Coalesce Behaviors & Advanced Scripts

Section 1

Coalesce Deep Dive

We introduced coalesce in Topic 2 — it is the mechanism that prevents duplicate records by checking whether a matching record already exists before deciding to insert or update. Here we go deeper into exactly how the matching works and edge cases you need to know for the exam.

The Coalesce Query

When the Transform Map processes a row, it builds a GlideRecord query using all fields marked as "Coalesce." It queries the target table for a record where each coalesced field equals the corresponding staging value. For example, if email is the coalesce field and the staging row has email = jane@example.com, ServiceNow runs: SELECT * FROM sys_user WHERE email = 'jane@example.com'

If one record is found: update it. If zero records found: insert a new one. If multiple records found: the behavior depends on the Transform Map's "Enforce mandatory fields" and error handling settings — usually it updates the first match and logs a warning.

⚠

Exam Trap — Coalesce on Reference Fields If you coalesce on a Reference field (e.g., assigned_to which points to sys_user), the coalesce query tries to match the staging value against the display value of the reference (the user's name). If the staging data contains the sys_id instead of the name, coalesce will fail to match and always insert new records. Always verify what format the staging value is in for reference fields.

Coalesce Priority — Field Map Order

When multiple fields are marked as coalesce, ServiceNow AND's all the conditions together for the lookup. There is no priority ordering between coalesce fields — they all must match. If you want OR logic (match on email OR employee_id), you need to write a custom script in the onBefore hook to perform your own lookup and force the action.

Section 2

Insert vs. Update vs. Ignore — Controlling the Action

By default, coalesce determines whether to insert or update. But you can override this behavior using the action variable in onBefore scripts.

action value	Effect	When to Use
`INSERT`	Always create a new record — ignore coalesce result	When you know this row should always be new (e.g., event log entries)
`UPDATE`	Always update an existing record — fail if no match	When rows should only update and never create (e.g., syncing status back)
`IGNORE` (via `ignore=true`)	Skip this row entirely — no insert, no update	When row fails validation or should be filtered out

// In onBefore script — force update only; skip rows that don't already exist
var gr = new GlideRecord('cmdb_ci_server');
gr.addQuery('serial_number', source.u_serial_number);
gr.query();
if (!gr.next()) {
ignore = true; // skip this row — no matching CI found
} else {
action = 'UPDATE'; // force update, don't insert
}

Section 3

Advanced onAfter Scripts — Creating Related Records

The onAfter script fires after each row is processed (inserted or updated). It has access to target (the GlideRecord of the record just written) and source (the staging table row). This is where you create related records, fire workflows, or perform post-processing.

Creating Related Records in onAfter

Example: after importing a CI from a CSV, you want to create a CI Relationship record linking it to its parent rack. The CSV has a "rack_name" column. In onAfter:

// Find the rack CI by name
var rack = new GlideRecord('cmdb_ci_rack');
rack.addQuery('name', source.u_rack_name);
rack.query();
if (rack.next()) {
  // Create a "Hosted on" relationship
  var rel = new GlideRecord('cmdb_rel_ci');
  rel.parent = rack.sys_id;
  rel.child = target.sys_id;
  rel.type = '<Hosted on::Hosts>'; // relationship type sys_id or name
  rel.insert();
}

💡

Tip — Use target.isNewRecord() in onAfter In the onAfter script, target.operation() returns either 'insert' or 'update'. Use this to distinguish first-time inserts from updates. For example, you may only want to create a relationship on first insert, not every time the CI is updated.

Section 4

Copy Empty Fields Behavior

The "Copy empty fields" setting on the Transform Map controls what happens when the staging row has a blank/empty value for a mapped field and the target record already has a value for that field.

Setting	Behavior	Use Case
Copy empty fields = true (checked)	Blank staging value OVERWRITES existing target value — the target field becomes blank	When source data is authoritative and blanks mean "clear this field"
Copy empty fields = false (unchecked)	Blank staging value is ignored — existing target value is preserved	When source data is partial and you don't want to lose data previously entered in SN

⚠

Exam Trap — Unexpected Data Loss with Copy Empty Fields A common real-world and exam scenario: a team imports user records from HR. The HR export doesn't include phone numbers. With "Copy empty fields" checked, every import wipes out the phone numbers that help desk agents had entered manually. Always determine whether the source is the authoritative source for every field before enabling this setting.

Run Business Rules Setting

The Transform Map also has a "Run business rules" checkbox. When enabled, Business Rules on the target table fire as if the record were being saved normally via the UI. When disabled, Business Rules are bypassed — useful for performance in large loads but dangerous if Business Rules enforce data integrity or kick off workflows.

For CMDB imports, disable business rules is sometimes used to speed up large initial loads, but you must ensure data integrity is maintained another way. For user imports, leaving Business Rules enabled ensures notifications and automations fire correctly.

Exam Quick Reference

Common Exam Traps — Coalesce & Scripts

Multiple coalesce fields use AND logic — all must match for an update to occur
Coalescing on a Reference field matches against the display value, not sys_id
Set ignore = true in onBefore to skip a row; set action = 'INSERT' or 'UPDATE' to override coalesce decision
onAfter has access to target (the written record) and source (staging row) — use target.operation() to check insert vs. update
"Copy empty fields" = true will overwrite existing data with blanks from source — causes unexpected data loss
"Run business rules" = false skips Business Rules on target table — use cautiously
Transform hook order: onStart → per row (onBefore → fields → onAfter) → onComplete

Exam Checkpoint

Practice Questions

4 questions · Select an answer to see the explanation immediately.

Coalesce & Scripts Quiz 1 / 4

A Transform Map coalesces on a Reference field that points to a User record. What value does the coalesce match against?

💡 Explanation

B is correct. When coalescing on a Reference field, the Transform Map matches against the display value of the referenced record (e.g., the user's full name), not the sys_id. This is a key exam trap — the source data typically contains a human-readable name, not a sys_id. A is incorrect because source data rarely contains sys_ids. C and D are field values, not the reference display value.