ServiceNow allows a "coalease" field on Transform Maps to match data for insert/update on data imports. How do you make conditional coalease methods or special scripted coalease imports?

If you need this and read this article beforehand this can really save some frustration!

How Coalease Works

You can check the coalease field on a row or multiple rows in the Transform map. For a single field coalease, if there is a match on that field, ServiceNow will do an update, if there is no match ServiceNow will insert a new record.

If multiple fields are set to coalesce, all coalesce values are used to match an existing record. If two fields are set for coalescing and a matching value is found on one of the coalescing fields but not on the other, a new record is inserted.

That is pretty helpful, but what if you want more, for example you want to make conditional coalease methods? There is a way to do that, and it is pretty great trick to know.

How to use a Sys ID Source Script with Transform Maps

1. Turn off the existing coalease checkbox on the field map
2. Add a new field Map

Source Table: Table you are importing from
Target Table Table you are importing into
Target Field: Sys ID
Use Source Script: true
Source Script:
//In a source script, you can do any number of elaborate GlideRecord queries or if statements
//Important to note that the "answer" must be the sys_id of the match
//Below is an example script
var grUser = new GlideRecord('sys_user');
grUser.addQuery('active', true); 
grUser.addQuery('email',source.u_email);
grUser.query();
if (grUser.next()) {
answer=grUser.sys_id;
}
else {
answer= -1;
}

Hope this helps,
Mike