Coalesce options

There are several possible configurations you can use to coalesce data in import sets.

No coalesce

If no coalesce is defined, all imported rows are treated as new records. No existing records are updated.

Single-field coalesce

You can coalesce on a single field to update an existing record.

If a target table record exists with the same value in the coalesce field as the staging table record, the target table record is updated using the import set record values.

Multiple-field coalesce

You can coalesce on multiple fields to update an existing record.

If a target table record exists with the same values in all coalesce fields as the staging table record, the target table record is updated using the staging table record values. All coalesce field values between the target and staging tables must match to coalesce with multiple fields.

Conditional coalesce

You can use a script to determine if a staging table row should coalesce to a target record.

Most conditional coalesce scripts are defined in the Source script field of a field map for the sys_id field. To update a target record using the staging table record values, the script must return the sys_id of the target table record.

Example conditional coalesce scripts

Review examples of conditional coalesce scripts.

Updates only

To only update records where a match is found, and skip records where a match is not found, specify a coalesce field and add the following script as an onBefore script to the transform map.

if (action == 'insert') 
  ignore = true;

Conditional coalesce using dot-walking

You can use dot-walked fields in a conditional coalesce script, such as to match the email address of a user when importing incident data.

In this example, this script is defined in the Source script of a field map for the Incident target table sys_id field.

var gr = new GlideRecord('incident');
gr.addQuery('caller_id.email', source.u_email); //check if the incident caller's email matches the import row email value
gr.query();

if(gr.next())
{
    answer = gr.sys_id; //if a match exists, return the sys_id of the matching Incident record
}
else
{
    answer= -1;
}

Conditional coalesce with an OR condition

You can use a conditional coalesce script to match source and target records based on multiple field values. Unlike multiple-field coalesce where all coalesce fields must match, you can specify a script to only require one of the fields to match.

In this example, this script is defined in the Source script of a field map for the User target table sys_id field.

var gr = new GlideRecord('sys_user');
var qc = gr.addQuery('email', source.u_email); //first check if the user's email matches
qc.addOrCondition('user_name', source.u_name); //alternatively, check if the username matches
gr.query();

if(gr.next()) 
{
    answer = gr.sys_id; //if a match exists, return the sys_id of the matching User record
}
else 
{
    answer = -1;
}

Case-sensitive coalesce field values

You can control if an import set will coalesce on matching values with different cases.

The field map Coalesce case sensitive field enables you to coalesce field values by case sensitivity.

By default, fields marked as Coalesce in the field map are used in a case insensitive lookup for existing records. If the Coalesce case sensitive check box is selected, the instance attempts to match coalesce field values by case. The target table record is updated only if the value in the target record and the import set staging record have the same value and case. Otherwise, a new record is created.

Coalesce on empty fields

You can control if an import set will coalesce on fields with no value.

The field map Coalesce empty fields field allows you to coalesce on fields with no value.

By default, fields marked as Coalesce in the field map cause the import set to check for a target record with a field value that matches the value from the import set staging record. When Coalesce empty fields is selected for that field map, an empty value in the target record and import set staging record counts as a match for the purpose of coalescing.

For example, the User transform map coalesces on the email field. With the Coalesce empty fields option selected, a source record containing an empty email address coalesces to a target record containing an empty email address.