As developers, we get many requests by the business that often challenges how ServiceNow is conceptualized.
One of my recent tasks was to adapt a massive import process from an external platform to the instance. For context, the imported data were invoices with around 3 attachments each. The invoices were inserted into a custom table with an easy system import through an excel file, but then the attachments were a challenge. There are many ways to upload attachments to the instance, but it required me as an admin to handle almost the entire process, have access to the PDF folder and upload them myself. I needed a solution were the end user could just provide all the data and then only care about the import process of the excel, which is inescapably an admin task.
The solution I came up with is an easy implementation that relies on two things:
- How attachments are handled in the sys_attachment table.
- The ability to mass upload attachments on a Catalog Item attachment widget.
We will upload all the attachments on the RITM and then redirect them using a UI Action and based on a correlation ID that connects the attachment and the row of the import excel sheet it belongs to.
Let's see how it is done.
The Catalog Item
The Catalog Item is an easy and straight forward thing to do. There is only one detail to have in mind for comfortability: there needs to be an attachment variable to separate the excel sheet with the import data from the PDF attachments. The rest can be designed as needed. Of course, the Catalog Item must allow attachments.
The Format
For this solution to work, we need to relate the PDFs with the row of the import excel sheet it belong to. Let's set an example so we can clearly see the requirement:
I have record I want to upload, and this record has three attachments. If I want to import this record and have the PDFs redirected to it, I need to create a keyword that will relate these two entities so my script can query the record and make the relationship. I need a code or ID unique to these entities so the system knows how to identify that they belong together.
The way we create such ID can be custom to your solution, but here's how I did mine.
One of the requirements for my import process is to set a unique identifier to each row, each record. This identifier must be written at the beginning of each PDF file name so that they are identified by the record they should be related to. If the PDF file name needs to contain more information, separate the Correlation ID with a "_", then we can separate the string later.
PDF Name Example: <correlation ID>_<PDF file name>.pdf
Have in mind, ServiceNow will crop long attachment file names. This could break the relation process.
Once we come up with a system to create this identifier (this is totally up to each situation, just needs to be unique), we write it at the beginning of each file and each row on a designated column.
The Import Process
The Import process is carried out as usual. We take the excel sheet and run it through a transform map that maps the fields to the desired table. Remember to have a Correlation ID field were we can write the unique identifier that will connect the inserted record with its attachments.
The UI Action
After to System Import process, we find ourselves with a bunch of newly created records on a table and a RITM with a bunch of attachments. Here's how we connect the dots for the culmination of our process.
The UI action will serve as a quick way to redirect all the attachments to where they belong. This solution is founded on the fact that the attachments exist on the sys_attachment table and can be handled just as any other record.
The sys_attachment table stores the location of each attachment in the system using the fields Table Name [table_name] and Table sys ID [table_sys_id]. These two fields can be overwritten, changing the location of that attachment to our liking (you should already know where this is heading).
Design
The UI Action must be created in the Global scope in order to access the sys_attachment table, or else have cross-scope accesses created.
Name: (any)
Table: Requested Item [sc_req_item]
Action name: (any)
Show update: true
Form button: true
Condition: current.cat_item=="<Enter yout Catalog Item Sys ID>" // This will ensure that the UI Action only shows for this requests.
Script:
gs.addInfoMessage("Redirecting the files to the correspondent records. This may take a while, don't do anything else on the instance until it's finished.");
// Variables
var destinationTable = ""; // Insert the Table Name where the inserted records where created
var correlationField = ""; // Insert the Field Name where the Correlation ID is stored in the table
// Query the attachments from the RITM record
var reqId = current.getUniqueValue();
var att = new GlideRecord('sys_attachment');
att.addEncodedQuery('table_sys_id=' + reqId);
att.query();
while(att.next()){
// Disect the file name to get the Correlation ID
var fileName = att.getDisplayValue('file_name');
var fileNameArray = fileName.split('_');
var correlationId = fileNameArray[0];
// Find the related record using the Correlation ID
var relatedRecord = new GlideRecord(destinationTable);
relatedRecord.addQuery(correlationField, correlationId);
relatedRecord.query();
// Change the attachment location
if(relatedRecord.next()) {
att.setValue('table_name', destinationTable);
att.setValue('table_sys_id', relatedRecord.getUniqueValue());
att.update();
}
}
// Refresh the page
current.update();
action.setRedirectURL(current);
gs.addInfoMessage("Process completed");If everything was done right, the files will be redirected to the correspondent record.
Error handling
If any attachment was not redirected, you might find yourself in a situation where you don't know where it belongs to.
You can use a background script similar to the one below to monitor if any record is missing attachments:
var record = new GlideRecord('<your_table>');
record.addEncodedQuery("<your query>"); // I recommend to query using the 'sys_created' field
record.query();
while (record.next()) {
var log = '';
var index = 0;
var attachment = new GlideRecord('sys_attachment');
attachment.addQuery('table_sys_id', record.getUniqueValue());
attachment.query();
while (attachment.next()) {
index++;
}
// Here you can set any conditions you need to monitor how many attachments are expected to have. Set an amount, an average, a range...
if (index != 3) {
gs.info(record.number + ' has ' + index + ' attachments.');
}
}Hope this is useful. If I skipped anything or any part is not explained properly, let me know.
Happy coding!
