Transform Maps

[JOJO10]

Hi all,
I’m running into unexpected coalesce behavior on a Transform Map. I have two coalesce fields: Field X (direct source-to-target) and Field Y (mapped from a column in the uploaded Excel through script). The Transform Map also has “Create new record on empty coalesce fields” enabled.
When I import a row with Field X = 123 and Field Y blank, ServiceNow updates the existing record with Field X = 123 instead of inserting a new record.
Could someone explain why this is happening and how to enforce “update only when both keys are present; otherwise insert”?

[SD_Chandan]

Hi @JOJO10 ,

Create a new field, map it using a transform script like: answer = source.field_x + '_' + source.field_y;

Mark only this field as coalesce.This way, a match happens only when both Field X and Field Y match

[Rafael Batistot]

Hi @JOJO10 

Why it happens

1. Multiple coalesce fields work as an OR condition, not AND
   
• When you mark two fields as Coalesce, ServiceNow tries to find a match where any of them matches a record.
• In your case:
  
• Row has Field X = 123 and Field Y = blank.
• Since Field X = 123 matches an existing record, SN updates it, even though Field Y is blank.
2. “Create new record on empty coalesce fields” is misunderstood a lot
   
• That flag applies only when all coalesce fields are blank in the source row.
• It doesn’t enforce “both keys must exist to update”.

How to enforce “update only when both keys are present”

You’ll need to override the default coalesce logic. A couple of approaches:

Use onBefore Transform Script

(function transformRow(source, target, map, log, isUpdate) {
// Ensure both fields are present before allowing update
if (!source.u_field_x || !source.u_field_y) {
// Force insert by clearing target sys_id
target.sys_id = '';
}
})(source, target, map, log, action);

• This way, if either Field X or Field Y is empty, ServiceNow won’t try to update.

[JOJO10]

Thankyou @Rafael Batistot for clarification

But I’m facing an issue for one specific value of Field Y. In that case, the requirement is to match both Field X and Field Y and update the existing record. However, when I try to handle this in the OnBefore Transform Script (to coalesce and update the record), the Business Rule is blocking the update.

The BR was originally written to prevent duplicate case creation based on the combination of Field 1, Field 2, and Field Y, but it’s now restricting the update scenario I need for this specific Field Y value
Only for one specific value of Field Y , it should update the record 
How to handle it in OnBefore TS

[Rafael Batistot]

@JOJO10 

I see where you’re running into trouble now 

You need to make the OnBefore Transform Script smart enough to:

• Update only if Field Y == "that specific value" and an existing record matches.
• Otherwise, force insert.
• Also, bypass the BR logic in this scenario.

OnBefore Transform Script Example

(function transformRow(source, target, map, log, action) {
var specialValue = "SOME_VALUE"; // replace with your special Field Y value

setWorkflow(false)
if (source.u_field_y == specialValue) {
// Look for an existing record by both Field X and Field Y
var rec = new GlideRecord('your_table');
rec.addQuery('u_field_x', source.u_field_x);
rec.addQuery('u_field_y', source.u_field_y);
rec.query();

if (rec.next()) {
// Found existing record → force update
target.sys_id = rec.sys_id + '';
} else {
// No match → insert
target.sys_id = '';
}
} else {
// For all other values of Field Y → always insert
target.sys_id = '';
}
})(source, target, map, log, action);

In your script add “setWorkflow(false)“, any

This not trigger a business rule