question Re: Import Set Transform Script to look up reference field in Developer forum

Import Set Transform Script to look up reference field

kevinray — Wed, 13 Jun 2018 19:26:44 GMT

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()){

Re: Import Set Transform Script to look up reference field

Michael Ritchie — Wed, 13 Jun 2018 19:33:35 GMT

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?

Re: Import Set Transform Script to look up reference field

kevinray — Wed, 13 Jun 2018 19:44:45 GMT

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

Re: Import Set Transform Script to look up reference field

Shashikant Yada — Wed, 13 Jun 2018 19:49:39 GMT

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; }

Re: Import Set Transform Script to look up reference field

kevinray — Wed, 13 Jun 2018 20:05:15 GMT

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.

Re: Import Set Transform Script to look up reference field

Shashikant Yada — Wed, 13 Jun 2018 20:12:12 GMT

Could you please share the mapping of the fields. And which is the string field in temp table? And it is mapping to which field? Thanks

Re: Import Set Transform Script to look up reference field

Michael Ritchie — Wed, 13 Jun 2018 20:12:13 GMT

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:

Re: Import Set Transform Script to look up reference field

kevinray — Wed, 13 Jun 2018 20:19:47 GMT

That is EXACTLY what I have. Clearly there is something else going on. I guess I can delete my tables and try again and see if that helps.

Thanks for your effort!