Transform map onBefore script

CCZMAX1
Giga Sage

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

 

1 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

 

View solution in original post

13 REPLIES 13

Thank you Abhijit.  However, this does not appear to work.  My target table is a custom table with a reference field that references sys_user.  Field u_user_id is set to Coalesce but the onbefore script does not work.

@CCZMAX1 ideally it should work, as your coalesce field is already populated with required user. Can you try putting logs and see if target.u_user_id prints required sys_id. 

 

Hi Abhijit, it does not work because of TypeError: Cannot read property "active" from undefined (sys_transform_script......

 

I'm currently testing the onStart and onBefore suggested by Ehab Pilloor in this chat.

Hi @CCZMAX1 

 

I have done similar implementation before so this approach should work. If you are still interested in this approach, please share your Coalesce field configuration screenshot.