Handling variable coalesce in transform maps

General Add comments

Sometimes a functional request might look difficult, but after taking a closer look the solution can be easy….

When creating a transform map for importing records in a target table, you can set the coalesce on one or more field maps. This way you can find the corresponding target record.

The wiki of Service-now.com describes this on http://wiki.service-now.com/index.php?title=Creating_New_Transform_Maps#Creating_a_Field_Map

Our customer asked us to create a Transform Map where the unique key is not always on the same field maps. So for example if you are creating a transform map for importing users into the sys_user table, you must be able to define the correct user record on the AD account ór employee number.

The problem

If you do it the normal way, you would set the coalesce on 2 field maps (AD account and employee number) however, when the source record has an incorrect value for the employee number and a correct value for the AD account, the result will be an “insert action” for this user since the target record isn’t found.

We have found a simple and effective solution.

The solution

Create only one field map where the coalesce is checked, the target field is Sys ID, the source field is Script and the coalesce empty fields is checked.

In the source script you can do all the magic that is needed to find the corresponding target record. When you have found the correct record, you return the Sys ID in the answer so you have a perfect match and if you can’t find the target you don’t fill the answer variable.

Simple isn’t it!?

Below I’ve included a sample source script:

// Check if we can find a user
var gr = new GlideRecord(“sys_user”);
gr.addQuery(“company”, companyRef);
if(hasRealValue(source.u_hr_employeeid)) {
    gr.addQuery(“employee_number”, source.u_hr_employeeid);
} else {
    if(hasRealValue(source.u_hr_userid)) {
       gr.addQuery(“u_ad_account”, source.u_hr_userid);
    } else {
       gr.addQuery(“sys_id”, “NOT_FOUND”);
if(gr.next()) {
    answer = gr.sys_id;

function hasRealValue(hItem) {
    return (Boolean(hItem) && hItem != “NULL”);


If you have any questions, don’t hesitate to send me a mail on: menno.aret@2e2.nl

Leave a Reply