Business Rule - Query another table, set value of current reference field 'dot walk'

[Josh80]

Hi

Doesn't seem like this would be too hard but can't seem to quite get it.

I created a record producer that creates a new user and creates a new record in a custom 'user account relationship' table.     Works great, etc..

However, upon insert of the new 'user account relationship' record which has only 2 reference fields (u_user and u_customer_account), I want a business rule to fire and backfill the 'company' field on the User record, as the same 'company' that is in the table of the 'u_customer_account' company field.

Can I dotwalk such as 'current.u_customer_account.u_master_company_name' below?

It is not working.

var gr = new GlideRecord('sys_user');

  gr.addQuery('sys_id',current.u_user);

  gr.query();

  gs.log("User value: " + current.getValue('u_user'));

  while(gr.next()){

  gr.company = u_customer_account.u_master_company_name;

  gr.update();

  }

[Josh80]

I placed the u_master_company_name field on the same form...

Business rule still not working.

var gr = new GlideRecord('sys_user');

gr.addQuery('sys_id',current.u_user);

gr.query();

gs.log("User value: " + current.getValue('u_user'));

if(gr.next()){

  gr.company = current.u_master_company_name;

  gr.update();

}

[BALAJI40]

If the code in Business rule replace the above line with below code,

gr.company = current.u_customer_account.u_master_company_name;

[Sharique Azim]

Hi Josh,

Two very important points here:

It is strictly advised not to use update()   in after/before Business Rule.

And if my understanding is correct, you want to populate the value on the user table from the 'user account relationship' table which itself creates record from the record producer. So,basically if you are not using reference fields in the record producer it would tend to create several records in both the parent table of the reference field on 'user account relationship' table. Or if the correct sys_id of the record is not passed.

OK, now coming to a possible approach.

Approach One:

The business rule which is in the   'user account relationship' table. Here you already have the value of the 'u_user ' parent table(i assume its sys_user) and the 'u_customer_account' field.

after insert BR

  var gr = new GlideRecord('sys_user');

  gr.addQuery('sys_id',current.u_user);

  gr.query();

  if(gr.next()){   // while for many users related to the record on the user account relationship table.

  gr.company = current.u_master_company_name.company;   // if the company   reference field on the u_customer_account table is called.

// If the record on the u_customer_account parent table itself is the name of the company then you can simply use

// gr.company = current.u_master_company_name;

  gr.update();

  }

Approach Two:

Since the company u_master_company_name field is already updated from the record producer during creation, pass this to the user's company field right from there.

in the record producer script:

say you have code   to create a record on 'user account relationship' table.

var usr= new GlideRecord('sys_user');

....

...

var userID=usr.insert(); //storing the sys_id of the user

......

abc.initialize();

abc.u_user=producer.variable_name; // the variable that captures the user.

abc.u_master_company_name =producer.variable_name2; // the variable that captures the company from the record producer. It would be used later again

abc.insert();

//query the user table again as the record for the user is created by now

  var gr = new GlideRecord('sys_user');

  gr.addQuery('sys_id', userID);

  gr.query();

  if(gr.next()){ .

  gr.company = producer.variable_name2;

  gr.update();

}

I believe this should work, well it took me 30 mins to type . Any correction is always welcomed.

Hit like and mark correct if it helps.

Regards,

Shariq

[Josh80]

Hello Sharique

Thank you! I will try either/both of them.

Josh

On Wed, Apr 19, 2017 at 1:06 PM, sharique <community-no-reply@servicenow.com