Problems populating reference field from transform map

[robhaas]

I am importing approver data into a custom table "appr". The data being imported only contains the employee number for each approver but no other information. I am mapping the source employee number to the target appr table field "u_approver". This field is a reference field to the sys_user table. I searched here and found a post that says   I should change the Referenced value field name to "employee_number". I have done this and attempted to re-import, but the reference field stays blank.

I added a gs.log to the import and validated that my employee number is coming in just fine for each entry. It is just not populating the approver field. The referenced value field name I am using is employee_number.

The post I was reviewing is Re: populate reference field using transform map scri

Anyone have any thoughts on what I may be doing incorrect?

b-rad Tagging you as you were able to assist in the other post.

Thanks for any assistance.

[Prasun]

In the transform script use glidequery to map that filed. use it on before.

like:

var gr = new GlideRecord('sys_user');

gr.addQuery('employee_id',source.emp_id);

gr.query();

if(gr.next())

{

target.field = gr;

}

[robhaas]

Would this script be in conjunction with the referenced value field name being set to employee_number? Or should I use this by itself? I am not having luck either way. Another thing that may be the issue, is that when the data is imported, the approver field is populating with the number.0 For instance, if the employee number is 123, the approver reference field shows 123.0     I tried using .split for the source employee number and then replacing source.emp_id in the script with appr[0] (being the first value in the array after the split), but again, no luck.

[Brad Tilton · Accepted solution]

Hi Robert,

If excel has added decimals and the like, then using the oob functionality to match the employee number field on the user table may not work as I believe it would have to match exactly. One thing to try would be to change the field type of the employee number field on your import table (not the user table) to an integer to prevent the .0 . The other option would be to use a before transform script to manipulate the value as prasun suggested.

[robhaas]

I just changed the field type of the employee number field on the import table to integer. When I import now, the employee number will populate the "Approver" field, but only as the employee number without the .0 So the reference is not working. I have also attempted the following code in an On Before transform script, but Approver is stil blank:

var gr = new GlideRecord('sys_user');

gr.addQuery('employee_number',source.u_employee_number);

gr.query();

if(gr.next())

{

target.u_approver = gr;

}

I have also tried target.u_approver = gr.name; No dice.