Import Record Handling Issue

[Sean Burton]

Need help on an issue. So I have a data source that imports from csv attachment, transforms, and updates a table in my instance. The data source is updated with a new spreadsheet each week. 

My issue is that if a record in the ServiceNow table is no longer on the weekly csv file, I need the record in ServiceNow to change from active true to active false. 

My transform will not match to any records since the record is not on the CSV file. I can accomplish my goal by deleting all my ServiceNow records before each import, so my ServiceNow table reflects only data from the last CSV file import, but that does not seem clean. Does anyone know a way to change a record to active false, if the ServiceNow record does not match to a CSV import?  

[Sandeep Rajput]

@Sean Burton Is there any field on the table records which you can update during transform, you need to set/update this field for those records which are present in the CSV file, if such a field is present then you can make a GlideRecord query in your onComplete Transform script and inactivate all those records which were not updated during last few minutes.

Hope this helps.

[Community Alums · Accepted solution]

Hi @Sean Burton ,

Scheduled job which runs after the data import is completed.

In scheduled job script you can query and compare the staging table & Target table to identify the records and make them inactive.

I started answering community questions recently. It would be a great boost if you can mark my answer as helpful/correct if my response helped you.

[Kieran Anson]

An easy option is to use a database view to join the import table to your target table, and then use the missing record values to identify your none-updated records.

1. Create a database view with your target table (u_coalesce in the below example) and the import table (u_coalesce_imp). Join them on the sys_target_sys_id field which import set records have to identify the target they updated

2. Because of the left join (see above screenshot) we can include records from the target table that don't appear in the import table. In the below screenshot, the uimp_sys_id column has an empty value. That allows us to identify that the corresponding sys_id (ucoa_sys_id) is a record in the target table that wasn't updated in the import set

3. Back to the transform map, you can use an onComplete to do the deactivation process

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	var dbViewGR = new GlideRecord('u_coalesce_import'); //u_coalesce_import is the dbview name
	dbViewGR.addNullQuery('uimp_sys_id'); //"uimp" is the prefix for the import set table, and we want to check if sys_id is empty

	//If the transform runs daily, it would be worth adding in a date filter
	dbViewGR.query();
	while(dbViewGR.next()){
		var targetRecordGR = new GlideRecord('u_coalesce');
		if(targetRecordGR.get(dbViewGR.getValue('ucoa_sys_id'))){
			targetRecordGR.setValue('active' , 'false');
			targetRecordGR.update()
		}
	}

})(source, map, log, target);