Auto update reference field value depends on another reference field.

[Yamja]

Hi,

I have two reference fields(Program Manager, company) from two different tables(sys_users, core_company ) in program record. I want the company field needs to auto populate depends on program manager. I know we can achieve this by writing a business rule but If someone changes the company value in sys_user record that automatically needs to update the company value in program. Ex; Manager X has company dell, he changes from dell to intel, the company field needs to change to intel automatically so we don't need to go and change manually.

Please someone help me how can I achieve this.

[Tushar · Accepted solution]

Hi @Yamja 

BR can be something like this -

// Check if the company field is modified
if (current.company.changes()) {
  // Get the updated company value
  var newCompany = current.company;

  // Query the program table for records with the old company value
  var programGR = new GlideRecord('program');
  programGR.addQuery('program_manager', current.sys_id); // Assuming the reference field is named 'program_manager'
  programGR.addQuery('company', current.company.getPrevious()); // Get the previous value of the company field
  programGR.query();

  // Update the company field in the program records with the new company value
  while (programGR.next()) {
    programGR.company = newCompany;
    programGR.update();
  }
}

 Reference qualifier script -

javascript:current.program_manager ? 'active=true^sys_id=' + current.program_manager.company : 'active=true';

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar

[Yamja]

Hi Tushar,

I wrote this script "

current.company = current.program_manager.company;" in program record before insert and update business rule. with BR the company value is auto populating based on program manager, if I change the company manager that new manager company value populating. But the issue is if that company manager changes the company, that new comapany value is not populating. I am new to Servicenow scripting. Please provide the instructions how can I use the script that U provided. is it like BR in program record with when to run(before insert and update and what condition). and I try to paste the "javascript:current.program_manager ? 'active=true^sys_id=' + current.program_manager.company : 'active=true';"  java script condition in program_manager reference qualifier. that condition is not giving the records in that reference qualifier. Please help me.

[Yamja]

Hi Tushar, Thanks for the script. It's working, I wrote a BR in user table with after insert and update condition.

I removed

"programGR.addQuery('company', current.company.getPrevious());"

from the script it worked. Thanks