How can I map a value to a reference field where the mapped value is not the display value?

[taylor21]

Hi all,

I am trying to move data from one table to another using a background script. I am trying to map the fields as follows

*Note: The two fields on u_table_two are reference fields and reference as follows:

• u_section_name :: u_table_three
• u_user :: sys_user

The mapping for u_section_name → u_section_name is working just fine. I am using setDisplayValue for this map.

My issue is occurring when I am attempting to map u_user_id_number → u_user.

The u_user_id_number is the (dot walked) u_user.u_user_id_number value on the sys_user table. (Example: 123456) The display value for this field is a name (Example: John Smith). This User field is a reference to the sys_user table.

How can I map the User's ID number to the u_user field and have it populate the correct associated user?

This is my script so far:

var gr = new GlideRecord('u_table_one');
gr.addQuery('u_section_name', '700Z1000'); //Grabbing a single record for testing
gr.query();

while (gr.next()) {
   gs.log("Current record from u_table_one: " + gr.u_section_name);
   var gt = new GlideRecord('u_table_two');
   gt.query();

   if (!gt.next()) {
   	 var gh = new GlideRecord('u_table_two');
   	 gh.initialize(); 
   	 gh.setDisplayValue('u_section_name',gr.u_section_name);
   	 gh.setValue('u_user',gr.u_user_id);
   	 gh.setValue('u_relationship', 'Staff');
   	 gh.insert();
   	 gs.log("New record: " + 
                "Section name: " + gh.u_section_name + " " + 
   	        "User: " + gh.u_user + " " + 
   	 	"Relationship: " + gh.u_relationship + " ");
   }
}

[Michael Ritchie]

With reference fields an extra field appears within the field map that you can use to specify the column to match on called Referenced value field name and that is where you would enter: u_user_id_number

[Michael Ritchie]

Here is a quick example of one of my demo field maps where I entered in your column name:

[taylor21]

Hi Michael, 

Thank you for your reply! I am not using a transform map for this, I am using only a background script.

Taylor

[Michael Ritchie · Accepted solution]

Ah sorry.  You can get this via a function call at the end of your script:

function getUserByID(userID) {
	var answer = "";
	var userRec = new GlideRecord("sys_user");
	userRec.addQuery("u_user_id_number", userID);
	userRec.query();
	if (userRec.next()) {
		answer = userRec.sys_id;
	}
	
	return answer;
}

Then in your script you would use:

gh.setValue('u_user', getUserByID(gr.u_user_id));

Please mark this post or any as helpful or the correct answer to your question if applicable so others viewing can benefit.