Conversion of User ID to User Name in Assigned in Transform Map

[Okey_O]

I have a data source connection to a sql database. the field on the sql database for 'assigned to' uses the user id's (u_assignedto) of the users as opposed to the user names for the 'assigned to' field. I would like to convert the assigned to (user id) to assigned to (user name) when a value is in the field and leave it as blank when no value. I created a script for the assigned to field in the transform map but it seems to set the 'assigned to' to undefined. Can i please get some extra set of eyes on the script to see if i'm not doing it right. My script is below:

answer = (function transformEntry(source) {

// Add your code here

if(source.u_assignedto != "") {
var _user = new GlideRecord("sys_user");
_user.addQuery("user_name", source.getValue('u_assignedto'));
_user.query();

if (_user.next()) {
return _user.name; // return the value to be put into the target field
}
      }

else if(source.u_assignedto == "") {
current.assinged_to == ""; // set to empty
}

})(source);

[Ankur Bawiskar · Accepted solution]

Hi,

Does this field 'u_assignedto' in the import set table store the user_name or the sys_id?

I believe this is what you want to achieve; you are getting user ids of the user from the database and convert them to user in sys_user table so you are querying the sys_user table with user_name; get the record sys_id and store that in assigned_to field of target

also since this is transform script you need to use target object to set the value

can you update code as below

answer = (function transformEntry(source) {

// Add your code here

if(source.u_assignedto != "") {
var _user = new GlideRecord("sys_user");
_user.addQuery("user_name", source.getValue('u_assignedto'));
_user.query();


if (_user.next()) {
target.assigned_to = _user.sys_id; // return the value to be put into the target field
}
      }

else if(source.u_assignedto == "") {
target.assinged_to = ""; // set to empty
}

})(source);

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[Okey_O]

Thanks Ankur..just to be sure in the below line of code; user_name refers to the user id field on the sys_user table right?

_user.addQuery("user_name", source.getValue('u_assignedto'));

[Okey_O]

Thanks Ankur..just to be sure in the below line of code; user_name refers to the user id field on the sys_user table right?

_user.addQuery("user_name", source.getValue('u_assignedto'));

[Ankur Bawiskar]

Hi,

yes user id field on sys_user table means user_name

that is the column name in dictionary

Regards

Ankur