What is Coalesce in ServiceNow?

[alex_04]

What is Coalesce in ServiceNow?

[Dr Atul G- LNG]

Hi @alex_04 

Coalesce is a feature in ServiceNow that determines if a row in a staging table matches a record in a target table. It can be used to prevent duplicate data and update existing records instead of creating new ones

Coalesce is like unique key, if match found then update else create a new record, 

[Uncle Rob]

You define coalesce on a field.  When coalesce is true on a field mapping in a transform, its essentially saying "If the record being imported has the same value in this field, then I want to UPDATE the record, not insert a new one"

[Sandeep Rajput]

@alex_04 Here is the link which describes Coalesce in detail https://developer.servicenow.com/dev.do#!/learn/courses/washingtondc/app_store_learnv2_importingdata...

[rambo1]

HI @alex_04 

In ServiceNow, the term "coalesce" is primarily used in the context of data import and transformation. When importing data into ServiceNow, coalescing allows you to specify which field or fields should be used as a unique key to find existing records in the target table. This helps to avoid creating duplicate records by updating existing records instead.
Here’s a detailed explanation of what coalesce does and how it works:
Coalesce in Data Import:

Field Coalescing: During the import process, you can designate one or more fields as coalesce fields. When you do this, ServiceNow uses these fields to determine whether a record already exists in the target table.

If a match is found: The existing record is updated with the imported data.
If no match is found: A new record is created.

Single-field Coalescing: You can coalesce on a single field. For example, if you're importing user data and coalesce on the email field, ServiceNow will look for existing records with the same email address.

If a record with the same email address exists, it will update that record.
If no record with that email exists, it will create a new record.

Multi-field Coalescing: You can also coalesce on multiple fields. In this case, ServiceNow uses a combination of field values to find matches.

For example, if you coalesce on first_name and last_name, ServiceNow will look for records where both the first name and last name match.
If a record with both matching fields is found, it will update that record.
If no matching combination is found, it will create a new record.

Use Case Examples:

User Import: When importing a list of users, coalesce on the user_id or email field to update existing user records without creating duplicates.
Inventory Management: When importing asset data, coalesce on the serial_number field to update existing assets rather than creating new records with the same serial number.

Steps to Use Coalescing in Import Sets:

Create Transform Map:

Navigate to System Import Sets > Create Transform Map.
Define the source and target table.

Mapping Fields:

Map the fields from your import set to the target table fields.

Set Coalesce:

For each field you want to coalesce on, select the Coalesce checkbox.
Multiple fields can be set to coalesce, implementing multi-field coalescing.

Example:
Suppose you have an import set of user data with fields like user_id, first_name, last_name, email, and you want to avoid creating duplicate user records.

Create a transform map for the User [sys_user] table.
Map the user_id field from your import set to the user_id field in the User table.
Check the Coalesce checkbox for the user_id field mapping.
When the data is imported, ServiceNow will use the user_id to check for existing records. If a match is found, it updates the record; otherwise, it creates a new record.

Key Benefits:

Data Integrity: Helps maintain clean and consistent data by avoiding duplicate records.
Efficiency: Reduces manual effort needed to check for existing records and update them accordingly.
Automated Updates: Automatically updates existing records in bulk import operations without user intervention.

By properly using the coalesce feature in ServiceNow, you can streamline data imports and ensure that your database remains accurate and up-to-date.