Transform Map - reject all records in import if one recored is rejected

[russellprice]

Hi

Is there a way I can do a pre-check of data from source table before it transforms? then if it finds a record that it will reject due to a choice field being invalid it rejects the whole inport so that the correction can be made and the spreadsheet imported again with corrections?

Or is there a way to do on-the-fly corrections as and when it finds invalid choices?

any advise would be appreciated.

[Pradeep Sharma]

Hi,

I don't think there is a way to reject the complete records at one go but OOTB, you can set the choice action to reject which will reject the complete row if the invalid choice value is found. 

https://docs.servicenow.com/bundle/london-platform-administration/page/integrate/ldap/task/t_SetChoiceAction.html

Thanks,

Pradeep Sharma

[David Ramirez]

Here is my suggested solution:

1. On your transform map create an "onStart" transform script, in this script you should set a variable such as dataErrors = false, then iterate through the rows in the import set, and if any data issues are found then set the "dataErrors" variable to true, then at the end of the onStart script you can do:

if(dataError == true){
    ignore = true;
    //optionally you could add something to the log or even send a notification by triggering an event
}

This will abort the entire transform process, just take care to iterate only through the current SET, since the import set table may have several days/weeks worth of imports, your script may look similar to this (untested):

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
	
	var dataErrors = false;
	var rowGR = new GlideRecord('myImportSetTable');
	rowGR.addQuery('sys_import_set',source.sys_import_set);
	rowGR.query();
	while(rowGR.next()){
		//run validations on the row
		if(rowIssuesFound){
			dataErrors=true;
		}
	}
	if(dataErrors == true){
		ignore = true;
	}
	
})(source, map, log, target);

[russellprice]

This is awesome! Gives me somthing to work with. Thanks

Is there a way for it to report which errors it found? Maybe in an alert box or somthing?

[Community Alums]

Hi,

If you are importing data from spread sheet, then you can write DataValidations. So that user can select choices which you have defined in the data validation.