Transform Script.. i need to update user name based on it's country and currency.. how should i add multiple conditions?

ServiceNow SA · ‎09-16-2016

During importing I need to update user name based on it's country and currency.. how should i add multiple conditions?

Chuck Tomasi · ‎09-16-2016

Thank you for the clarification. I wasn't aware the data was being imported in to another table. I'm going to assume you have fields on your sys_user table called u_country and u_currency. You can adjust the field names as necessary. I'm also going to assume the import set field name is 'u_name' - again, adjust the script below as needed.

Put something like this in the script field of your transform map.

var user = new GlideRecord('sys_user');

user.addQuery('name', source.u_name);

user.addQuery('u_currency', source.u_currency);

user.addQuery('u_country', source.u_country);

user.query();

if (user.next()) {
var target.u_user = user.getValue('sys_id');

// User was found with name, currency, and country

} else {

// user was not found with name, currency, and country

}

View solution in original post

bala_sn · ‎09-16-2016

Select "Run Script" check box in the Transform map. You should do something like this in the script field.

//First separate the username, country, currency and store in variables, like Chuck said

var user = source.u_user.split(' '); // save the record/field input in to an array split on the spaces

var userName = user[0];

var country = user[1];

var currency = user[2];

//Now look for the user based on these 3 values

var u = new GlideRecord('sys_user');

u.addQuery('user_name', userName);

u.addQuery('country', country);

u.addQuery('currency', currency); //Currency is not a field on user table. I hope you get my point anyway

u.query();

if(u.next()) {

target.[user_reference_field] = u.sys_id; // This is where you're setting the user you found to the target table

}

Let me know if this is what you're looking for

ServiceNow SA · ‎09-16-2016

I tried this script but it is not returning any values..

bala_sn · ‎09-16-2016

Hi Sri,

The script is just a skeleton. I mean you need to adjust few things in the script to make it work.

Also, change this line in my script

target.[user_reference_field] = u.sys_id;

to map user name or name based on what you *really* need to map. for eg.

target.[user_reference_field] = u.user_name;

target.[user_reference_field] = u.name;

Also, to see what values are stored in which variable, add gs.log throughout the transform map script and check the logs to verify where it's going wrong.

For e.g gs.log(userName + '..' + country + '..' + currency);

gs.log(u.name) etc

Let us know how it goes.

Regards,

Bala

Abhinay Erra · ‎09-16-2016

First to start with what are the field types of u_country and u_currency on sys_user table?