Setup Primary and Secondary Coalesce for Transform Map

[DebjitGhosh]

Hi All,
I’m trying to load user data into the ServiceNow sys_user table using a transform map, and I have a specific coalescing requirement.
From the incoming payload, I need to identify existing users using a priority-based (multi-level) coalesce logic as follows:

First priority: emailAddress

Check if a user already exists with the same email address.

Second priority: employeeID

Only if no match is found using the email address, check whether a user exists with the same employee ID.

The logic must strictly follow this order—email address should always take precedence over employee ID.

If a matching user is found at either level, the existing record should be updated.
If no match is found using either field, a new user record should be inserted.

I’m unsure how to implement this kind of conditional, multi-level coalescing using a transform map in ServiceNow. Standard coalesce behavior doesn’t seem to support prioritized matching out of the box.
Has anyone implemented a similar approach, or can you suggest the best way to achieve this using transform maps?
Thanks in advance for your help.
Regards,
Debjit

[Tanushree Maiti]

Hi @DebjitGhosh 

The recommended approach is to

1) Use a Transform Map Script with an onBefore script rather than standard field-level coalesce checkboxes for your requirement.

This method lets you explicitly control the sequence of actions, making sure that email is prioritized over the employee ID.

Sample code :

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

    var email = source.u_primary_user_email; // Update to your source column name

    if (email) {

        var grUser = new GlideRecord('sys_user');

        grUser.addQuery('email', email);

        grUser.query();

        if (grUser.next()) {

               target.assigned_to = grUser.sys_id;  //  Map the sys_id to the target reference field as per your requirement

        } else {

   var userGR = new GlideRecord('sys_user');

              userGR.addQuery('employee_number', source.u_employee_id); // Match field

              userGR.query();

               if (userGR.next()) {

              target.assigned_to = userGR.sys_id; //  Map the sys_id to the target reference field as per your requirement

       }

        }

    }

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

2) use a Field Map with a Source Script on the sys_id field.

Sample code:

1. Create a New Field Map: In your Transform Map, add a new entry.
2. Target Field: Set this to Sys ID 
3. Use Source Script: Check this box.
4. Coalesce: Check this box.
5. Source Script: Enter the logic below. 

answer = (function transformEntry(source) {
    var email = source.u_email_address; // Use your  source field name
    var empID = source.u_employee_id;  // Use your source field name
var flag = false;

    // Check by emailAddress
    if (email) {
        var grEmail = new GlideRecord('sys_user');
        grEmail.addQuery('email', email);
        grEmail.query();
        if (grEmail.next()) {
           flag= true;
            return grEmail.getUniqueValue();         }
    }

    //  Check by employeeID (only if no email match was found)
    if (!flag){
    if (empID) {
        var grEmp = new GlideRecord('sys_user');
        grEmp.addQuery('employee_number', empID);         grEmp.query();
        if (grEmp.next()) {
            return grEmp.getUniqueValue();         }
    }
}

  })(source);

[DebjitGhosh]

Hi @Tanushree Maiti ,

Thank you very much for your response.

Few doubts I have, firstly do I have to implement both steps in my transform map to make it work?

In addition to that, 

 target.assigned_to = userGR.sys_id; 

what is this assigned_to, do I have to replace this field with one of my fields, if yes which field given my requirement?

Regards,

Debjit

[Tanushree Maiti]

Hi @DebjitGhosh 

OnBefore transform scripts in ServiceNow are server-side scripts executed during an import, running before each source row is transformed and saved to the target table.

They are primarily used to manipulate, validate, or selectively ignore records before they enter the target table. 

Whereas Field Map Source Script is used during data imports when you need to perform complex logic or data transformation on a single field that a simple direct mapping cannot.

handle

For my current and previous client , the data load logic is complex, so we have used both.

Just a addition,  For onbefore script , you can update the record before transform whatever fields as per your requirement.

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

    var email = source.u_primary_user_email; // Update to your source column name

    if (email) {

        var grUser = new GlideRecord('sys_user');

        grUser.addQuery('email', email);

        grUser.query();

        if (grUser.next()) {

          grUser.phone=source.u_phone_no;  //you add field as per your requirement
        grUser.update()

        } else {

   var userGR = new GlideRecord('sys_user');

              userGR.addQuery('employee_number', source.u_employee_id); // Match field

              userGR.query();

               if (userGR.next()) {

              userGR.phone=source.u_phone_no; //  add field as per your requirement

          userGR.update();

       }

        }

    }

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

For my cu