Transform script to update records

[Akki1]

Hi,

There are  list of users imported in the u_custom_user table using excel file.

Fields are:

user_id

name

email

op_manager_id

hr_manager_id

hr_manager-reference to itself i.e u_custom_user table

op_manager-reference to itself i.e u_custom_user table

except manager all other details are coming from the excel sheet now I want once import is completed post import script should run to set the managers using their manager_id which is a string and it can be compared to user_id from the same table.

Also we'll check if the manager_ids are not empty and then we'll iterate through the records by comapring manager_id to user_id [As manager is also a user] in same table and get the manager values set

If the id is empty we won't run maybe?

Can anyone help with script and when onAfter or onComplete?

[anubhavritolia]

Hi @Akki1 

You need to use onAfter Transform Script as you have to run it for every record:

Please find the code below:

if(target.hr_manager_id!='')
{
vew hrMngr = new GlideRecord('u_custom_user');
hrMngr.addQuery('user_id',target.hr_manager_id);
hrMngr.query();
if(hrMngr.next())
{
target.hr_manager = hrMngr.sys_id;
}
if(target.op_manager_id!='')
{
vew opMngr = new GlideRecord('u_custom_user');
opMngr.addQuery('user_id',target.op_manager_id);
opMngr.query();
if(opMngr.next())
{
target.op_manager = opMngr.sys_id;
}
target.update();

[Akki1]

Thanks @anubhavritolia 

But

what if the manager record is inserted after user record.

What I meant to say is suppose in row 1 it is user and then in row 2 it is manager so this fails right because after inserting row1 it will search for manager but it will be not there 

[anubhavritolia]

Hi @Akki1 

Ok It got it. In that case you can use onComplete Transform Script as below:

var cUser = new GlideRecord('u_customer_user');
cUser.addEncodedQuery("sys_updated_onONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()^hr_manager_idISNOTEMPTY^ORop_manager_idISNOTEMPTY");
cUser.query();

while(cUser.next())
{
 if(cUser.hr_manager_id!='')
 {
 var hrUser = GlideRecord('u_customer_user');
 hrUser.addQuery('user_id',cUser.hr_manager_id);
 hrUser.query();
  if (hrUser.next())
  {
  cUser.hr_manager = hrUser.sys_id;
  }
 }
 if(cUser.op_manager_id!='')
 {
 var opUser = GlideRecord('u_customer_user');
 opUser.addQuery('user_id',cUser.op_manager_id);
 opUser.query();
  if (opUser.next())
  {
  cUser.op_manager = opUser.sys_id;
  }
 }
cUser.update();
}

NOTE: As this is custom Table, fields must also be custom so field names may vary based on what is Field name on your instance. I have user what you mentioned. Do check Field name spellings based on your instance.

[SanjivMeher]

Why not map the manager_id field to the manager field->user id?

That way you wont need a script or query to do the mapping. 

The first sync wont map manager, if running forr the first time. But the next one will do and will be more efficient.