Loading string value from field to a reference field

[lrossy31]

Hello everyone,

I have to load the value of a string field that contains data migrated (name loaded last name then first name) to invert the name and load it into a reference field that references the user table. I have tried split method but when trying to Display it to reference field I cannot get the value.

I will greatly appreciate if anyone can help me with this.

[Brad Bowman]

This is hard to visualize without an example, but keep in mind that you need a sys_id of a record on the referenced table (sys_user) when populating a reference field.  If you're working with a string value and want to match it to the Name value, then you'll need to do a GlideRecord query on the sys_user table using that value to return the sys_id.

[Ankur Bawiskar]

@lrossy31 

you can use field map script or transform onBefore script

please share your script

[lrossy31]

this is the scrip:

var grDiscoveryName = new GlideRecord("discovery");
grDiscoveryName.setLimit(1);
//grDiscoveryName.addquery(name_string);
grDiscoveryName.query();

while(grDiscoveryName.next()){
var name = getValue(variable_name);
var name2
var first_name = name.split(",")[1].trim();
var last_name = name.split(",")[0].trim();
gs.log(first_name);
gs.log(last_name);

name2 = first_name + + last_name;

gr_user.update();

var gr_user = new GlideRecord("discovery");
gr_user.addEncodedQuery("first_name="+first_name+"^last_name="+last_name);
gr_user.query();
if(gr_user.next()){
gr_user.setValue('disc_issue_name', name2.sys_id );
//gs.log(gr_user.name);
gr_user.update();
}

[Sandeep Rajput]

@lrossy31 Try with the following script and see if it works.

var grDiscoveryName = new GlideRecord("discovery");
grDiscoveryName.setLimit(1);
//grDiscoveryName.addquery(name_string);
grDiscoveryName.query();
while(grDiscoveryName.next()){
var name = grDiscoveryName.getValue('variable_name');
var name2='';
var first_name = name.split(",")[1].trim();
var last_name = name.split(",")[0].trim();
gs.log(first_name);
gs.log(last_name);

name2 = first_name +' '+ last_name;

var gr_user = new GlideRecord("sys_user");
gr_user.addEncodedQuery("first_name="+first_name+"^last_name="+last_name);
gr_user.query();
if(gr_user.next()){
grDiscoveryName.setValue('disc_issue_name',gr_user.getValue('sys_id') );
//gs.log(gr_user.name);
grDiscoveryName.update();
}