Transform Map Coalesce

[beycos]

Hello Everyone, 

I'm importing data from a JSON file using a Data Source and Transform Map. I have two fields set as coalesce: x and y. These two fields always have values in the incoming data, and are mapped correctly. what I need to achieve is If a record already exists with matching x and y , but the existing record has a value in the date deleted field 

So basically:

• If Date deleted field is not empty, I want to bypass coalesce behavior and force insert.

• If  date deleted is empty, normal coalesce behavior (update) is fine.

• Any guidance or best practices are appreciated!

  Thanks in advance!

[Rafael Batistot]

Hi @beycos 

May you try via onBefore

1. Open your Transform Map. Navigate to the Transform Map you are using for the import.
2. Go to the Transform Scripts. Click on the "Transform Scripts" related list at the bottom of the form.
3. Create a new onBefore script. Click "New" to create a new script.
4. Set the When field. Choose "onBefore".
5. Write the script. Paste the following code into the script field. 

Transform script

This script will check if the record is attempting an update (action == 'update'). If so, it performs an additional check on the existing record's date deleted field. 

Code

if (action == 'update') {

    if (target.u_date_deleted.toString() != "") { // Replace `u_date_deleted` with your actual field name
 
        target.sys_id = "";
        gs.info('Coalesce overridden: Old record is deleted, forcing new insert for ' + source.x + ' / ' + source.y);
    }
}

[kaushal_snow]

Hi @beycos ,

Remove coalesce settings from the field mappings for x and y. Instead, handle coalescing manually in the script.... Use an onBefore transform script to check existing records before deciding to insert or update...

(function runTransformScript(source, map, log, target /*undefined onStart*/) {
var gr = new GlideRecord("your_target_table"); // Replace with your actual target table name
gr.addQuery("x", source.x);
gr.addQuery("y", source.y);
gr.query();

if (gr.next()) {
if (gr.u_date_deleted) { // If Date Deleted is not empty
target.sys_id = ""; // Force a new record insert
gs.info('Date deleted present – forcing insert for ' + source.x + ', ' + source.y);
} else {
// Allow updates: map required fields
target.fieldA = source.fieldA;
target.fieldB = source.fieldB;
// ... continue mapping as needed
gs.info('Updating existing record for ' + source.x + ', ' + source.y);
}
}
})(source, map, log, target);

This logic ensures: 

Forced Insert: If the existing record has a Date Deleted value, .sys_id is cleared, turning the action into an insert.

Normal Update: If Date Deleted is empty, relevant data is updated on the existing record...

If you found my response helpful, please mark it as ‘Accept as Solution’ and ‘Helpful’. This helps other community members find the right answer more easily and supports the community...

[Its_Azar]

Hi there @beycos 

You can’t do this with out-of-the-box coalesce alone — it will always try to update if a match is found. try this

Add an onBefore Transform Script to check the matched record.

If the existing record has date_deleted populated, clear the coalesce fields (ignore = true or set target sys_id = ‘’) so the insert is forced.

Otherwise, let it update as normal.

Hope this helps.

[beycos]

Even when the 'date_deleted' field is empty, it continues to insert when I schedule it.

[Ankur Bawiskar]

@beycos 

Did you check the script I shared above?

what debugging did you do?

If my response helped please mark it correct and close the thread so that it benefits future readers.