ServiceNow provides the ability to have an unique key on data imports. This method of matching is called Coalesce. In this article, I'll discuss the different ways you can use coalesce and matching possibilities.
No Coalesce
If no coalesce is set in the Transform Map, all data is inserted and there are no updates. This method is often used for one-time imports of data.
Single Field Coalesce
Coalesce is set in the Transform Map. It is just a true/false value between an source Import Set field and a target table field. Coalesce on a single field, and ServiceNow will attempt to match source values to existing target values. If there is a match, ServiceNow will update that record. If there is not a match, a new record is inserted.
This is the most common method to coalesce and should be used often to update records and not create duplicates.
Multiple Field Coalesce
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.
The important detail about this type of coalesce is that all coalesce fields must match in order to update a record.
Using Multiple Transform Maps
Multiple transform maps can be applied to a single data source. One import set row is created per transform map. This behavior can cause a large number of temporary records to be generated.
However, the advantage of using multiple transform maps is that you can use different coalesce methods for one set of data. You can ignore certain records on one transform map (ignore = true;) and use certain records on another transform map. In a way, using multiple transform maps allows you to create complex coalesce methods with only minor scripting.
Conditional Coalesce
ServiceNow allows you to coalesce against multiple fields, and use multiple transform maps against a single data source. However what if you want to create a conditional coalesce with a single transform map?
In that case, you can use a source script against the sys_id field.
Example Source Script 1: Incident Data
Source Table: Incident Data Load
Map: Incident Data Load
Target table: Incident
Target field: Sys ID
Coalesce: true
Use Source Script: true
Script:
var grIncident = new GlideRecord('incident'); grIncident.addQuery('caller_id.email',source.u_caller_email);
grIncident.query();
if (grIncident.next()) {
answer=grIncident.sys_id;
}
else {
answer= GlideGuid.generate(null);
}
Example Source Script 2: User Data
Source Table: User Data Load
Map: User Data Load
Target table: User [sys_user]
Target field: Sys ID
Coalesce: true
Use Source Script: true
Script:
var grUser = new GlideRecord('sys_user');
var qc = grUser.addQuery('email',source.u_email); qc.addOrCondition('user_name',source.u_name);
grUser.query();
if (grUser.next()) {
answer=grUser.sys_id; }
else {
answer= GlideGuid.generate(null);
}