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”?

[JOJO10]

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.

[Ajay_Chavan]

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);

[JOJO10]

Thankyou @Ajay_Chavan 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

[Ajay_Chavan]

//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);

[JOJO10]

Hi @Ajay_Chavan 
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.