Coalesce conditional script in the transform map

Shantharao
Kilo Sage

‎07-21-2024 10:25 AM - edited ‎07-21-2024 10:45 AM

Hi All,

Please find the scenarios below
I want to import data into the "cmdb_ci_server" table using import set table transform maps
Scenarios1: If Company and Name exist => I want to ignore the record
Scenario2: If Company and Correlation ID exist => I want to ignore the record, but
                  Scenario3: If the "Name" does not match with the existing data but a "Correlation ID" and Company exists, 
                  I want to update the name of that CI record considering "Correlation ID" has a unique one

Is it possible?
How can we write the coalesce logic via script instead of the coalesce checkbox?
Can someone please post the sample code for reference, making three fields coalesce logic 
Thanks

0 Helpfuls
  • 887 Views
1 REPLY 1

Satishkumar B
Giga Sage
Giga Sage

‎07-21-2024 11:43 AM

Hi @Shantharao 

 

In the transform map, add the following script in the "onBefore" transform script section:

 

 

(function transformRow(source, target, map, log, isUpdate) {
    // Scenario 1: Check if Company and Name exist in the target table
    var gr = new GlideRecord('cmdb_ci_server');
    gr.addQuery('company', source.u_company); // Use the actual field name for company
    gr.addQuery('name', source.u_name); // Use the actual field name for name
    gr.query();
    
    if (gr.next()) {
        // Company and Name exist, ignore the record
        ignore = true;
        return;
    }
    
    // Scenario 2 & 3: Check if Company and Correlation ID exist
    gr = new GlideRecord('cmdb_ci_server');
    gr.addQuery('company', source.u_company); // Use the actual field name for company
    gr.addQuery('correlation_id', source.u_correlation_id); // Use the actual field name for correlation_id
    gr.query();
    
    if (gr.next()) {
        if (gr.name == source.u_name) {
            // Company and Correlation ID exist and Name matches, ignore the record
            ignore = true;
            return;
        } else {
            // Company and Correlation ID exist but Name does not match, update Name
            target.sys_id = gr.sys_id;
            target.name = source.u_name; // Set the new name
        }
    }
})(source, target, map, log, isUpdate);

 

 

……………………………………………………………………………………………………

Please Mark it helpful 👍and Accept Solution✔️!! If this helps you to understand. 

 

0 Helpfuls