Copy data from a string field to a reference field

[Wayne Richmond]

We are currently populating a field on our User table from Okta called 'mobile_phone'. I need to copy this data to a reference field called 'u_affiliate'. I don't want the update to happen if the mobile_phone field is empty. I tried to do this as a Business Rule when the record is updated/inserted but couldn't get it to work. Does anyone know how I might achieve this? Alternatively I could schedule a script to run nightly instead of a BR but I don't know how to do that either. Any help is much appreciated.

[Pradeep Sharma · Accepted solution]

Thanks Wayne for the update. Please create a before business rule with below script.

var gr = new GlideRecord('u_affiliates');
gr.addQuery('field column name', current.mobile_phone);
gr.query();
while(gr.next())
   {
   current.u_affiliates = gr.sys_id;
}

NOTE : Replace field column name in line 2 with the exact field column name and make sure all column names and table name are adjusted properly.

Please let me know if you are blocked.

[Wayne Richmond]

Excellent Pradeep, spot on! Thank you so much, that's helps us out of a hole.

All the best

[Pradeep Sharma]

Thanks Wayne for the update.

Glad to know your issue is resolved

[nitin_kumar]

Hi,

You cannot copy a string field into a reference field as they are of different types. The only string you can copy into a reference field is a 'sys_id'.

Reference Fields - ServiceNow Wiki  

Thanks,

Nitin.

PS: Hit like, Helpful or Correct depending on the impact of the response

[Wayne Richmond]

Hi Nitin, thanks for your reply. It's interesting you say it can't be done because the way we populate this field for non-Okta integrated Users is via a 'Source script' entry on an LDAP transform, which is in plain text:

There is no sys_id here, just "Oxfam GB" being passed to the Target field 'Affiliate' which is the reference field on the User table I'm talking about. This works fine.

I'm not doubting your expertise but what I'm showing above seems to contradict what you're suggesting.