Updating records using coalesce

Release version: Yokohama

Updated January 30, 2025

4 minutes to read

Summarize

Summarized using AI

Summary of Updating records using coalesce

The coalesce option in ServiceNow's import set transforms enables you to update existing records in a target table by matching on specified fields rather than creating new records for every import. This functionality is essential for maintaining data integrity and avoiding duplicates during data imports.

Show full answer Show less

How Coalesce Works

When you select the Coalesce checkbox on a field map, ServiceNow checks for existing records in the target table that have the same value in the specified target field as the import set source field. If a match is found, the existing record is updated; if not, a new record is created. It is important to choose unique fields for coalescing to ensure accurate updates. If multiple matches occur, only the first matching record is updated.

Coalesce Configurations

No coalesce: All imported rows create new records; no updates occur.
Single-field coalesce: Updates existing records when a single target field matches the import value.
Multiple-field coalesce: Updates occur only when all specified coalesce fields match exactly between target and import records.
Conditional coalesce: Uses scripts to determine if a record should be updated by returning the sysid of the matching target record. This provides flexibility for complex matching logic.

Advanced Coalesce Options

Updates only: With a script on the transform map’s onBefore event, you can configure the import to update records only when matches exist, skipping inserts.
Conditional coalesce using dot-walking: Scripts can match on related fields, such as matching an incident’s caller email to the import data.
Conditional coalesce with OR logic: Scripts can match if any one of multiple fields matches, unlike multiple-field coalesce which requires all fields to match.
Case-sensitive coalesce: By default, coalesce matching is case insensitive. Selecting the Coalesce case sensitive option enforces exact case matching, creating new records if case does not match.
Coalesce on empty fields: By default, empty field values do not match for coalesce. Enabling Coalesce empty fields treats empty values in both source and target as a match, allowing updates on records with empty values.

Practical Benefits for ServiceNow Customers

Using coalesce during data imports helps you maintain clean and accurate data by updating existing records instead of creating duplicates. It offers flexibility through single or multiple field matches, scripting for complex scenarios, and controls for case sensitivity and empty values. This ensures efficient, reliable data integration tailored to your unique business rules.

The coalesce option allows you to update existing target table records when transforming import data.

The coalesce option on a field map allows you to specify if the selected Target field should be used to coalesce on when import set records are transformed. If the field map Coalesce checkbox is selected, when the import set row is transformed the instance checks for an existing record in the target table that has the same value in the Target field as the import set row Source field. For step-by-step instructions, see Create a field map.

If an existing record with a matching value in the target table is found, that record is updated. If no matching record is found, then a new record is created in the target table.

Note:

Choose fields in the target table to coalesce on only if those fields will have unique values. If more than one record in the target table matches the specified coalesce options, only the first matching record in the target table is updated.

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.