Transform Maps
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10 hours ago
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”?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
5 hours ago
Hi @Rafael Batistot
Thankyou for the response
This is still not working
Special Value update is still getting restricted by BR
what if i use "Coalsce empty fields" checkbox.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
9 hours ago
ServiceNow Coalesce Logic:
ServiceNow uses "OR" logic by default for multiple coalesce fields, not "AND" logic:
- Your case: Field X = 123, Field Y = blank
- ServiceNow logic: "Find record where Field X = 123 OR Field Y = blank"
- Result: Finds existing record with Field X = 123 and updates it
"Create new record on empty coalesce fields" only triggers when ALL coalesce fields are empty, not when some are empty.
Solution- Field-Level Script Mapping
// Use this script in the Field Mapping for your coalesce fields
// Apply similar logic to BOTH coalesce field mappings
// For Field X mapping:
(function transformField(source, map, log, target) {
var fieldXValue = source.getValue('field_x_column');
var fieldYValue = source.getValue('field_y_column');
// Only set coalesce value if BOTH fields are present
if (fieldXValue && fieldYValue && fieldXValue !== '' && fieldYValue !== '') {
// Both present - use normal coalesce behavior
return fieldXValue;
} else {
// Missing partner field - don't set coalesce value (forces insert)
target.setValue('field_x', fieldXValue); // Set actual value
return null; // Don't use for coalesce
}
})(source, map, log, target);
// For Field Y mapping (similar logic):
(function transformField(source, map, log, target) {
var fieldXValue = source.getValue('field_x_column');
var fieldYValue = source.getValue('field_y_column');
// Only set coalesce value if BOTH fields are present
if (fieldXValue && fieldYValue && fieldXValue !== '' && fieldYValue !== '') {
// Both present - use normal coalesce behavior
return fieldYValue;
} else {
// Missing partner field - don't set coalesce value (forces insert)
target.setValue('field_y', fieldYValue); // Set actual value
return null; // Don't use for coalesce
}
})(source, map, log, target);
[ Architect | Certified Professional]
Was this response helpful? If so, please mark it as ✅ Helpful and ✅ Accept as Solution to help others find answers.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
8 hours ago
Thankyou @Chavan AP 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
7 hours ago - last edited 7 hours ago
//You can handle this specific Field Y value requirement in the OnBefore Transform Script by implementing conditional coalesce logic and bypassing the Business Rule restriction.
(function runTransformScript(source, map, log, target) {
var fieldX = source.getValue('field_x_column'); // Change column name
var fieldY = source.getValue('field_y_column'); // Change column name
var SPECIAL_VALUE = 'your_special_field_y_value'; // Change this value
// Special handling for specific Field Y value
if (fieldY === SPECIAL_VALUE && fieldX) {
// Find existing record with both Field X and Field Y
var gr = new GlideRecord(map.getTargetTable());
gr.addQuery('field_x', fieldX);
gr.addQuery('field_y', fieldY);
gr.query();
if (gr.next()) {
// Update existing record and bypass BR
gr.setValue('u_skip_duplicate_check', 'true'); // Add this field to your table
target = gr;
log.info('Updating existing record for special Field Y value');
} else {
// Create new record with bypass flag
target = new GlideRecord(map.getTargetTable());
target.initialize();
target.setValue('u_skip_duplicate_check', 'true');
log.info('Creating new record for special Field Y value');
}
}
})(source, map, log, target);
Update your existing Business Rule to check the bypass flag:
// Modify your existing Business Rule - add this at the top
(function executeRule(current, previous) {
// Check bypass flag first
if (current.u_skip_duplicate_check == 'true') {
// Clear the flag and allow the operation
current.u_skip_duplicate_check = '';
return; // Exit BR - allow the update/insert
}
// Your existing duplicate check logic here...
// Original BR code for Field 1, Field 2, and Field Y duplicate prevention
})(current, previous);
[ Architect | Certified Professional]
Was this response helpful? If so, please mark it as ✅ Helpful and ✅ Accept as Solution to help others find answers.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
5 hours ago - last edited 5 hours ago
Hi @Chavan AP
Thankyou for the response
This is still not working
Special Value update is still getting restricted by BR
what if i use "Coalsce empty fields" checkbox.