Dynamically Update the Coalesce Fields in Transform Map

[Shrey hurana]

I am trying to implement the coalesce condition dynamically based on the data being sent via excel file for uploading the data in the Batch Job Table (cmdb_ci_batch_job).

I have below fields:

System - Drop down field with values as 'None', 'SAP', 'ServiceNow', 'JAVA', 'UI'
Node - String field
Session - String field
Name - String field
SAP Job/Script Name - String field

If the “System” field is equal to “SAP” being sent via excel for bulk upload, then coalesce is based on the “Node”, “Session”, “Name” and “SAP Job/Script Name” fields.

If the “System” field is different from “SAP” in the excel, then the coalesce is based on the “Node”, “Session” and “Name” fields (so without the “SAP Job/Script Name” field, whatever its values empty or not)

I tried implementing the logic using 'onBefore' transform script but in case of updating the record everytime the record is getting created in the target table (cmdb_ci_batch_job). Can someone please help me with the same.

Script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    var system = source.u_system.trim().toString();
    var node = source.u_node.trim().toString();
    var name = source.u_name.trim().toString();
    var session = source.u_session.trim().toString();
    var sap_job = source.u_sap_job_script_name.trim().toString();
    if (system == "SAP") //system value is SAP
    {
        var gr = new GlideRecord("cmdb_ci_batch_job");
        //gr.addQuery("name", "value");
        gr.addQuery("u_node", node);
        gr.addQuery("name", name);
        gr.addQuery("u_session", session);
        gr.addQuery("u_sap_job_script_name", sap_job);
        gr.query();
        if (gr.next()) {
            gs.log("It should update record - SAP Case" + gr.sys_id + " | " + gr.getDisplayValue("short_description"));
            //ignore = true;//this will not inset nor update the record in ServiceNow
            target.sys_id = gr.sys_id;

        } else {
            gs.log("It should create record - SAP Case");

        }

    } else if (system != "SAP" || system == "") //system value is not SAP OR Empty
    {
        var gr1 = new GlideRecord("cmdb_ci_batch_job");
        //gr1.addQuery("name", "value");
        gr1.addQuery("u_node", node);
        gr1.addQuery("name", name);
        gr1.addQuery("u_session", session);
        gr1.query();
        if (gr1.next()) {
            gs.log("It should update record - Non SAP Case" + gr1.sys_id + " | " + gr1.getDisplayValue("short_description"));
            //ignore = true;//this will not inset nor update the record in ServiceNow

        } else {
            gs.log("It should create record - Non SAP Case");

        }

    }

})(source, map, log, target);