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()){
I just created a similar table in my personal dev instance:
I created a transform map similar to what you have:
I created a simple spreadsheet using out of the box demo data:
And it imported just fine:
