Populate a reference field (transform map)

[Maria DeLaCruz]

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

[Maria DeLaCruz]

Hi Hardik,

Thanks for the response.   I tried that but it still didn't work.   Anything else I can try?

Maria

[James_Neale]

Hi Maria,

I have a sneaking suspicion that the root of your problem is in the way that ServiceNow has chosen your employee id field type. It has probably interpreted it as a number and when it does this it adds a comma as a thousandth separator so you're actually looking up '100,679' instead of '100679'.

The easy way to fix this is to just modify your field mapping script to remove any commas:

answer = source.u_employee_id.toString().split(",").join("");

Re the previous methods:

• Don't do a lookup in a field map to the same table as the reference you are mapping to. You're essentially doing two lookups with this and will double the time it takes to import. This is what the Referenced value field name takes care of for you.
• You had the answer being the sys_id of the user but the referenced value field name was still user_name. You'd want that to be sys_id, but as I've explained you don't need to do this double lookup.
• On your first response to Brad (10-Dec-2015 20:36) you show the caller being populated, but it's showing the number which tells me Brad's suggestion worked, it just need the tailoring above. Depending on your requirements, I would suggest changing Choice action to 'ignore' so that new user records aren't created when they aren't found. Alternatively, you can choose 'reject' because it's likely you wouldn't want to create a target record and have the caller field empty.

Cheers

[Maria DeLaCruz]

Hi James,

Thanks for the response.   This worked great!   The only thing is I have some employee IDs that start with zero's and others start with a letter, and the incidents that got created for those have empty "Caller" fields. I tried to create another transform map, and modified the field map for the caller with: answer = source.u_employee_id.toString();.   Is this correct?

Thanks,

Maria

[James_Neale · Accepted solution]

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.

1. 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.)
2. 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;
   

3. 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.

[Maria DeLaCruz]

Thanks James!   This works great for the employee IDs   that are all numeric.

For the IDs containing a letter, I just created another transform map and used the source script answer = source.u_employee_id.toString() for the caller field map.