Transform map onBefore script

[CCZMAX1]

Hi, I have a transform map with a source field of u_user_id that maps to target field u_user_id. The target u_user_id field is a reference field that references the sys_user table.

I am trying to do an onBefore script that checks the u_user_id in the source field is an active user in the sys_user table. If the user is inactive I don't want to import the record into my target table or update the record if it exists.

This is what I have so far and it appears to be working but not sure if this is the best way to go as I have a lot of rows.

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

var sourceUserId = source.u_user_id;
var user = new GlideRecord('sys_user');
user.addQuery('user_name', sourceUserId);
user.query();

if (!user.next()) {
    ignore = true;
}

if (!user.active) {
    status_message = "User inactive. Not added or updated.";
    ignore = true; // skip if user inactive
}


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

Many thanks

Max

[Ehab Pilloor]

Hi @CCZMAX1,

If user.next() is false due to incorrect name, it still runs if (!user.active) which is unnecessary because the user doesn't exist. You should nest the active check only if the user exists.

(function runTransformScript(source, map, log, target) {

    var sourceUserId = source.u_user_id;
    var user = new GlideRecord('sys_user');
    user.addQuery('user_name', sourceUserId);
    user.query();

    if (user.next()) {
        if (!user.active) {
            status_message = "User inactive. Not added or updated.";
            ignore = true;
        }
    } else {
        status_message = "User not found. Not added or updated.";
        ignore = true;
    }

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

Regards,

Ehab Pilloor

[CCZMAX1]

Thank you Ehab, this works.  So, for each record I have to do a GlideRecord lookup?  I have a quite a few thousand records to import.

Maxine

[Ehab Pilloor · Accepted solution]

Hi @CCZMAX1, 

You could try get a list of active users using onStart transform script. And in onBefore script you could check if User name is in that list.

onStart script:

this.activeUsers = {};  // shared with onBefore
var gr = new GlideRecord('sys_user');
gr.addQuery('active', true);
gr.query();
while (gr.next()) {
    this.activeUsers[gr.user_name + ''] = true;
}

onBefore script:

if (!this.activeUsers[source.u_user_id + '']) {
    status_message = "User not found or inactive. Skipping.";
    ignore = true;
}

Your original script is fine but for thousands of records, you could use this logic to avoid GlideRecord query for each.

Ref: https://www.servicenow.com/community/developer-forum/setting-and-reusing-variables-during-import-tra....

Regards,

Ehab Pilloor

[ReshmaNarale]

Hi @CCZMAX1 
Your script mostly works, but it has a subtle bug and a couple of performance and reliability concerns especially if you're dealing with a lot of rows, instead that use below script.

(function runTransformScript(source, map, log, target) {

var sourceUserId = source.u_user_id;

if (!sourceUserId) {
ignore = true;
status_message = "Missing user ID.";
return;
}

var user = new GlideRecord('sys_user');
user.addQuery('user_name', sourceUserId); // or change to 'sys_id' or 'email' if more reliable
user.query();

if (user.next()) {
if (!user.active) {
ignore = true;
status_message = "User is inactive. Skipping import.";
}
} else {
ignore = true;
status_message = "User not found. Skipping import.";
}

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