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);

[Gurpreet07]

I am not able to understand the implementing but there seems to be some errors in code.

1. While calling function , use   toString() to pass sys_id

var error = getErrorMessage(setRow.sys_row_error.toString());

2.   In function definition, you need to move Glide Object to next location.

1. function getErrorMessage(sys_id) {  
2.   var errorMessage = new GlideRecord('sys_import_set_row_error');  
3.   errorMessage.get(sys_id);  
4.   errorMessage.query();  
5. if(errorMessage.next())
6.   return errorMessage.error_message;  
7. }
8. }

[stevewilson]

The code is not getting going through the while loop because no GlideRecord is found by setRow.next();

Additionally, if I hardcode a sys_id to setRow.addQuery('sys_import_set', current.sys_id);   the script will work. Or if I reprocess the import set the script will also work.

[venkatiyer1]

Hi Steve,

One option would be to order your transform maps and add the script to check for error in the complete of last transform map. But if we were to go with the above option though i couldn't spot any particular issue I would say instead of using filtered condition try using the condition above the script and modify that to a code like one below:-

current.state.changes() && (current.state == "processed" && previous.state == "loaded")

[stevewilson]

The only issue with this approach is I have 5 transform maps total for this import. I would need to get all the previous errors from the other transforms.

How can I get the name of the current import set from an onComplete script?