Issue capturing import set errors to email to a user.

[stevewilson]

I have an import set/data source that is created when a users sends a file into ServiceNow with a specific subject and then transformed. It will then trigger 5 transform maps to load all the data into the correct tables.

After the transform is complete and the import set has finished being processed, I would like to gather all the errors to be sent to a user by email. I've done this before with an onComplete script that creates an event which then triggers an email. I am trying to use a Business Rule so I can gather all the errors for each of the 5 transform maps instead of writing 5 different onComplete scripts.

My Business Rule isnt running correctly and the GlideRecord queries are not returning anything. Current.sys_id (the import set sys id) isnt working for the GlideRecord lookup but if I log it I can see the value in the script log. It will work if I hardcode a sys_id and it will also work if I reprocess the import set manually but it fails if I send the data via an email to process.

(function executeRule(current, previous /*null when async*/) {
gs.log('Import set email error BR running, sys_id is: ' + current.sys_id);
//Query the import set row table to get with the sys_id of the import set and remove rows where the error is null
var setRow = new GlideRecord('sys_import_set_row');
setRow.addQuery('sys_import_set', current.sys_id);
setRow.addNotNullQuery('sys_row_error');
setRow.query();
while(setRow.next()){
//Add one to the row number so it will correspond to the row in the excel sheet
var rowNum = setRow.sys_import_row++;
//Get the error message using the sys_id of the error message on the import set row table
var error = getErrorMessage(setRow.sys_row_error);
gs.log('Row: ' + rowNum + ' Error Found: ' + error);
}
function getErrorMessage(sys_id) {
   var errorMessage = new GlideRecord('sys_import_set_row_error');
   errorMessage.get(sys_id);
   errorMessage.query();
   return errorMessage.error_message;
}
})(current, previous);

[venkatiyer1 · Accepted solution]

Hi Steve,

I don't consider that to be an issue as you can access import set sys id using import_set as that would remain the same irrespective of transform maps.

1. var setRow = new GlideRecord('sys_import_set_row');  
2. setRow.addQuery('sys_import_set', import_set.sys_id);   // alternatively you can use import_set
3. setRow.addNotNullQuery('sys_row_error');  
4. setRow.query();
5. while(setRow.next()){  
6. //Add one to the row number so it will correspond to the row in the excel sheet  
7. var rowNum = setRow.sys_import_row++;  
8. //Get the error message using the sys_id of the error message on the import set row table  
9. var error = getErrorMessage(setRow.sys_row_error);  
10. gs.log('Row: ' + rowNum + ' Error Found: ' + error);  
11. }  
12.  
13.  
14. function getErrorMessage(sys_id) {  
15.   var errorMessage = new GlideRecord('sys_import_set_row_error');  
16.   errorMessage.get(sys_id);  
17.   errorMessage.query();  
18.   return errorMessage.error_message;  
19. }
20.  

More detail on oncomplete script

http://wiki.servicenow.com/?title=Transform_Map_Scripts#gsc.tab=0

[stevewilson]

Awesome I think I glanced over the import_set variable when I was looking.

Let me do some more testing but it looks like everything is working now.

[Bharat Tagad]

Hi @venkatiyer1 ,

How we create an email notification and add link of the actual ignored records.

Or can we create a hyperlink such that when user clicks on it in email notification then the excel of the ignored records will get exported. 

thanks in advance