Import Set Transform Script to look up reference field

[kevinray]

Found tons of articles on this topic, but nothing is helping.

Trying to import data to a custom table where the custom table is 2 reference fields. (it's called Approver table)
field 1 references a ROLES table, Field 2 references the sys_user table.

My import spreadsheet has two columns. First column is the name of the role, the second column is the employee number. Every time i import i get an error that it can't find a matching reference value.

On our sys_user table, the column that holds the employee number is "employee_number"

I've tried to set the "Referenced value field name" on the transform map to "employee_number" and that didn't work. same error.

I've tried to do a script on the field to look up the record and return the sys_id, but also...same error

answer = (function transformEntry(source) {
	
	var eNumb = source.u_approver;
	var u = new GlideRecord('sys_user');
	u.addQuery('employee_number',eNumb);
	u.query();
	
	if(sgr.next()){
		var eName = u.sys_id; // return the value to be put into the target field
		return eName;
	}
	
})(source);

Any thoughts?

BTW the if(sgr.next()) is a typo. In my real code it's if(u.next()){

[Michael Ritchie]

In your import set table, what is the field type for u_approver?  The employee_number field on the sys_user table is of type string.  Is the data in your import set table clean, meaning no additional spaces?  Have you taken one of the values in your import set table and queried the sys_user table to make sure there is a match?

[kevinray]

in the temp table it's a string, in the target table it's a ref. Yes, the employee numbers are valid and confirmed the query works in background script

[Shashikant Yada]

You can write the OnAfter transform script var eNumb = source.u_approver; var u = new GlideRecord('sys_user'); u.addQuery('employee_number',eNumb); u.query(); if(sgr.next()){ var eName = u.sys_id; target.employee_number = eName; }

[kevinray]

Nope. Getting same message on all 313 records i'm trying to import. 

No matching reference value found for Approver. Please use an existing reference value.