Coalesce by sys_id!

Back to transform maps in #servicenow! Ever thought what would happen if you coalesce by sys_id? Well, it is not so interesting for matched records as it would simply update it, but what would happen if you supply non-existing sys_id?

Well, the new record will be created for non-matched value supplied for sys_id but if you look at the created record then you will see that system assigned a new unique value to it's sys_id.

How can you benefit from it?

More and more I face the need to perform "smart" coalesce in #servicenow Transform Maps.

For example I want to first attempt find a cmdb record with matching "correlaton_id", then if I fail I would like to search by "name" and finally (if I did not find anything at two previous steps) I will create a new record. Its not easy to accomplish with standart field maps, right?

Here is the way:

Use [script] and coalesce to target record's sys_id with the script looking like this:

var gr = new GlideRecord('cmdb_ci');

// 1. Lookup by correlation_id

if (gr.get('correlation_id', source.u_correlation_id)){

answer = gr.sys_id;

} else

// 2. Lookup by name

if (gr.get('name', source.u_name)){

answer = gr.sys_id;

} else {

answer = 'abrakadabra'; // Here is the trick! I did not find anything so I return the value that for sure will not be present in target table as sys_id!

}

Of course to make it working for repeated uploads you need to populate "name" and "correlation_id" in your transformations.

Have a great day!