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

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

russellprice — Mon, 24 Sep 2018 14:56:16 GMT

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.

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

Pradeep Sharma — Mon, 24 Sep 2018 15:32:49 GMT

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

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

David Ramirez — Mon, 24 Sep 2018 15:35:45 GMT

Here is my suggested solution:

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

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

russellprice — Tue, 25 Sep 2018 07:40:04 GMT

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?

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

Community Alums — Tue, 25 Sep 2018 08:01:53 GMT

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.

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

russellprice — Tue, 25 Sep 2018 08:18:32 GMT

Sorry, can you expand on this please? This sounds like exactly what I need but Im very new to scripting so any examples you can provide would be appreciated