Best Practice for Monitoring Inserts/Updates When Using ignore = true in Transform Map

[beycos]

Hi everyone,

I regularly import data into ServiceNow from JSON file. 

I have a Transform Map scenario where the name field is set  coalesce=true.

However If a record with the same name has been deleted, I want to allow inserting a new record with the same name rather  than updating the deleted one. 

Because of this logic, I am not using "Field map". Instead using only OnBefore transform script and The script handles update or insert operations manually, and at the end I set ignore=true to prevent default insert/update behavior.

The script works as expected, but in the Transform Map execution logs, all records are shown as ignored due to ignore=true . This is expected behavior , but it means I cannot easily see how many records were updated or inserted during the import.

Is there a best practice or recommended approach to monitor import results ( inserts/updates) without losing the manual control that my script provides?

Please find the script below:

(function runTransformScript(source, map, log, target) {
var Code = source.u_code;
var sourceDateDeleted = source.u_datedeleted;
var updated = false;

// 1. Try to update record with same name and same date_deleted
var existing = new GlideRecord('u_cmdb_ci_xyz');
existing.addQuery('name', Code);
existing.addQuery('u_date_deleted', sourceDateDeleted);
existing.query();

if (existing.next()) {
log.info('Record found with same name and date_deleted: ' + existing.sys_id);

existing.u_xstatus = source.u_xstatus;
existing.u_company = source.u_company;
existing.short_description = source.u_short_description;
existing.u_expiry_date = source.u_expirydate;
existing.u_comments = source.u_comments;

existing.update();
updated = true;
}

// 2. Else, update active record (no date_deleted)
if (!updated) {
var active = new GlideRecord('u_cmdb_ci_xyz');
active.addQuery('name', Code);
active.addQuery('u_date_deleted', ''); // No deletion date = active
active.query();

if (active.next()) {
active.u_xstatus = source.u_xstatus;
active.u_company = source.u_company;
active.short_description = source.u_short_description;
active.u_expiry_date = source.u_expirydate;
active.u_date_deleted = source.u_datedeleted;
active.u_comments = source.u_comments;

active.update();
updated = true;
}
}

// 3. If still not updated, insert new record
if (!updated) {
var newRec = new GlideRecord('u_cmdb_ci_xyz');
newRec.initialize();

newRec.name = Code;
newRec.u_xstatus = source.u_xstatus;
newRec.u_company = source.u_company;
newRec.short_description = source.u_short_description;
newRec.u_expiry_date = source.u_expirydate;
newRec.u_date_deleted = source.u_datedeleted;
newRec.u_comments = source.u_comments;

var newSysId = newRec.insert();
if (newSysId) {
log.info('New record inserted with Sys ID: ' + newSysId);
}
}

// Prevent default transform map behavior
ignore = true;
})(source, map, log, target);

Thanks in advance for your help 

Beyza

[Kieran Anson · Accepted solution]

Hi Beyza,

This sounds like 'conditional coalesce' which is a documented way to coalesce given a script. Updating records using coalesce 

1. Create a new field map
2. Set the field mapping to 'use source script' , coalesce, and specify the target field as 'sys id'
3. Set the script to identify the target record

answer = (function transformEntry(source) {

    var Code = source.u_code;
    var sourceDateDeleted = source.u_datedeleted;

    var existing = new GlideRecord('u_cmdb_ci_xyz');
    existing.addQuery('name', Code);
    existing.addQuery('u_date_deleted', sourceDateDeleted);
	existing.setLimit(1);
    existing.query();
	if(existing.next()){
		return existing.getUniqueValue(); //Return the sys_id of the record to update
	}

	//return -1 which indicates we want to create a new record
	return '-1';

})(source);

[beycos]

Thank you Kieran. it worked.

Regards 

Beyza