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

[Ankur Bawiskar]

@CCZMAX1 

you want to ignore the entire row or only skip the field from getting mapped?

If you use onBefore transform script and set ignore=true then entire row will be ignored.

If my response helped please mark it correct and close the thread so that it benefits future readers.

[CCZMAX1]

I want to ignore the entire row if the user in that row is inactive in the sys_user table.  I just don't want the row to be added if user is inactive or updated if inactive even if the record exists in my target table which may have been added when they were active.

Max

[Ankur Bawiskar]

@CCZMAX1 

let the field map match against the user and you handle the ignore part in onBefore transform script

if (target.userField.active.toString() == 'false' && (action == 'insert' || action == 'update')) {
    ignore = true;
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[CCZMAX1]

Hi Ankur, thank you for your help.  However, I tried this and it does not work.  I think because u_user is just a sys_id, not a full user record, so target.u_user.active is undefined.

 if (target.u_user.active.toString() == 'false' && (action == 'insert' || action == 'update')) {
        ignore = true;
    }

What does work however is the solution that @Ehab Pilloor provided.  This I have working.

onStart

   this.activeUsers = {}; 
    var gr = new GlideRecord('sys_user');
    gr.addQuery('active', true);
    gr.query();
    while (gr.next()) 
	{
		this.activeUsers[gr.user_name.toString().toLocaleLowerCase().trim()] = true;
    }

onBefore

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

Thanks

Max

[Abhijit4]

Hi @CCZMAX1 

Using GlideRecord for each transform entry is not efficient approach.

Instead have Coalesce field on reference field on target table and in  Referenced value field name" enter field name which holds actual user id which is usually "user_name". ( Example shown below )

Let Coalesce mapping do its job efficiently. Then create onBefore script as below:

if(target.active=="false")
ignore=true;

or may be in your case, it should be as below:

if(target.u_user_id.active=="false")
ignore=true;