Populate a reference field (transform map)

Maria DeLaCruz · ‎12-10-2015

Hello,

How can I populate a reference field via import or transform map? The reference field is caller_id, but the source data contains the caller_id.user_name. Currently, we are able to type in the user_name in that field, and it is able to find the name.

Thanks,
Maria

James_Neale · ‎12-21-2015

Great!

Okay, now you've hit the main problem with the field being converted to integer - integers don't have prefixed zero's so they are lost.

You can try changing the type of the employee ID field to String with a length of 40, but chances are this won't work. (You could also try deleting the column and re-adding it but this will likely also have issues.)
If the employee ID's are a predictive length, then you could also pad the employee ID with the missing zeroes if it's not the right length, but this won't work for you because of the ID's that start with a letter:
```
var id = '' + gr.u_employee_id;

if (id.length < 6) id = ('000000' + id).substr(-6);

answer = id;
```
The only other option is to start your import table from scratch and prevent ServiceNow from interpreting the field type to anything other than a string (Consider if you might also need to do this for other fields as well). To do this, simply take your spreadsheet and delete all the rows except for the header and the first data row and change any fields that look like numbers and make them a string. E.g. Employee ID might have 005834 so just make it 'abc' to force it to be a String. Now run through a new import setup with this file but don't bother actually doing the transform, and once you've set everything up again you should find everything works ok. Doing this also means you won't need any conversion scripts for the employee id to user_name field because it's a string to string match.

View solution in original post

twright5 · ‎12-10-2015

do a GlideRecord on the sys_user table, query the name field with the name the name you are given. Get the sys_id and put that into the caller_id field.

Maria DeLaCruz · ‎12-10-2015

Hi Tom,

Thanks for the response. Would I do this in the transform scripts?

Maria

twright5 · ‎12-10-2015

yes...for the field map click the use source script checkbox and put your script in there.

var user_name = source.username;

var gr = new GlideRecord(sys_user);

gr.addQuery ('name',user_name); //this will be the value that is coming from the data source field

gr.query();

if ( gr.next() )

{

// Did we find the user?

answer = gr.sys_id;

{

Something like this. hope this helps.

Maria DeLaCruz · ‎12-17-2015

Hi Tom,

I modified the script you provided with the script below. So my data source contains an Employee ID (see attached file), and the GlideRecord query needs to look for a record that contains a matching User ID (user_name). When I run the transform, the Caller ID comes up empty. Could you please check if there's something I missed?

Thanks,
Maria

HV1 · ‎12-17-2015

Try converting the source value to string.

var user = source.u_employee_id.toString();

Regards,

Hardik Vora