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

taylor21 · ‎08-21-2018

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 · ‎08-21-2018

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.

View solution in original post

taylor21 · ‎08-21-2018

Thank you so much! This worked beautifully, you deserve a trophy... or a cookie!

Michael Ritchie · ‎08-21-2018

You are very welcome, sorry for the incorrect suggestion earlier. Glad this worked for you.

kironk · ‎10-27-2023

Hello.. I have a similar situation.

I am trying transform map for cmdb_rel_ci . however, I would like to map Child ci from only "windows Server" or "Linux Server" class and NOT from Vmware instance or Nutanix virtual machine instance where you might find the same ci name.

How do I do that , I tried adding cmdb_ci_win_server under reference field value name but no luck