In our current solution we have our customers (end users) interact with Requests through the service portal, and our fulfillers work off of Tasks in the system. We had an issue where fulfillers were adding attachments to a Task and asking our customers view the attachment, however because the attachment was on the Task and not the Request they were unable to view the attachment(s).
To solve this we ended up creating a Business Rule on the Attachment (sys_attachment) table that runs on insert and after the database has completed the transaction only for records that have a table of "sc_task" or "sc_req_item". The Business rule will copy all attachments from the Task or RITM to the parent Request, then delete attachments that are duplicates on the Request. We do this by using a GlideRecord query that sorts by hash and file name, and then sorts by descending on created on. If we find a match on both hash and file name, we can say with confidence that the attachment is a duplicate and that file should be deleted. Because it's sorted by descending on created on, it will keep the newest attachment and only delete the previously attached ones.
Below is the script and setting for the Business Rule, hope it helps someone else out there!
Create Business Rule on the sys_attachment table with the following settings:
- Advanced - True
- When - After
- Insert - True
- Filter Condition - "Table name > is > sc_task" OR "Table name > is > sc_req_item"
- Copy the code below into the "Script" section:
(function executeRule(current, previous /*null when async*/) {
var copyFrom = new GlideRecord(current.table_name);
copyFrom.get(current.table_sys_id);
var copyTo = new GlideRecord('sc_request');
copyTo.get(copyFrom.request);
/* copy all attachments, then delete any duplicates */
copyAttachments(copyTo.getTableName(), copyTo.getUniqueValue());
deleteDuplicateAttachments(copyTo.getUniqueValue());
gs.addInfoMessage('Attachments successfully copied to ' + copyTo.getDisplayValue());
/************************** functions *****************************/
function copyAttachments(table, sys_id){
var attachmentUtil = new GlideSysAttachment();
attachmentUtil.copy(current.table_name, current.table_sys_id, table, sys_id);
}
function deleteDuplicateAttachments(sys_id){
var gr = new GlideRecord('sys_attachment');
gr.addQuery('table_sys_id', sys_id);
gr.orderBy('hash');
gr.orderBy('file_name');
gr.orderByDesc('sys_created_on');
gr.query();
var lastHash = 'not_a_match';
var lastFileName = 'not_a_match';
while(gr.next()){
var isDuplicate = false;
if ((lastHash == gr.hash) && (lastFileName == gr.file_name)){
isDuplicate = true;
}
if(isDuplicate)
gr.deleteRecord();
lastHash = gr.hash.getValue();
lastFileName = gr.file_name.getValue();
}
}
})(current, previous);
