Copy data from a string field to a reference field

Wayne Richmond · ‎06-29-2016

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 · ‎07-04-2016

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.

View solution in original post

Abhinay Erra · ‎06-29-2016

wayne,

what table is the field "u_affiliate" referencing? Is there any relation between the referencing table and the mobile_phone field?

Thanks,

Abhinay

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

Wayne Richmond · ‎06-29-2016

Hi, thanks for the reply. The table is called u_affiliates

There is no relation between the fields

Pradeep Sharma · ‎07-01-2016

Hi Wayne,

I am assuming you want to create a record in u_affiliates table and then update the field on user table which reference to table "u_affiliates". IF my assumption is correct then here are the steps you have to follow.

Create a before business rule insert/update check to true. From the condition builder select mobilephone is not empty.

Screen Shot 2016-07-01 at 5.33.23 PM.png

Script : Please place below script under advanced tab between function templates.




var gr = new GlideRecord('u_affiliates'); //u_affiliates refers to table name

   gr.initialize();

   gr.u_fieldname = current.mobile_phone; //Replace u_fieldname with the exact column name where you wnt this data to be copied to table u_affiliates 

   var sysId = gr.insert();

   current.u_affiliates = sysId; //Here I am assuming a field u_affiliates refers to the table u_affiliates, and you want update this field on user record. Comment this line if not requried



})(current, previous);

Make sure you refer my comments in script section and adjust the changes before you start testing

I hope this answers your question.

Wayne Richmond · ‎07-04-2016

Hi Pradeep, thank you so much for taking the time to write this.

I don't want/need a new 'affiliate' record to be created. Entries in mobile_phone should match an existing entry in u_affilaite (on the u_affiliates table). I just need it to update the field on the sys_user table. I hope that makes sense 😕

Thanks again