Updating another table field value using Business Rule

[Mohammed Kemal]

Hi everyone,

I have a table 2 called (sn_compliance_control) where I have a custom string field(u_control_id). I would like this field to be updated from another table 1 (sn_compliance_policy_statement)'s field called "Reference"(reference).

N.B. the field reference is a string field found within the table

I wrote after BR in table 1 with

• Condition set to (Reference is not empty),
• When set to async, and
• Advanced checked (insert, update, query)

Here is my script:

(function executeRule(current, previous /*null when async*/) {

	var referenceValue = new GlideRecord('sn_compliance_control');
	referenceValue.addQuery('u_control_id', current.reference);
	referenceValue.query();
	
	while (referenceValue.next()){
		
		referenceValue.setValue('u_control_id', current.reference);
		referenceValue.update();
	}

})(current, previous);

My goal is to update all control id in my table 2 with table one value. I need a second eye to my BR/code since it's not doing the way it supposed to do?

Thanks,

Mokemal!

[Mohammed Kemal]

Hey Mark, just saw it. The BR works fine to update the record in the child table as it suppose. The scheduled script was used in this case for the following objective reasons: 

• The ServiceNow Continuous Authorization and Monitoring application (CAM for short) applies a standardized approach to automating NIST's Risk Management by bringing the Control Objectives and Citations from NIST.
• Control Objectives are added into the system before the select phase of CAM by the control overlay (Policy)

Controls are generated once the select phase completed and approved by the AO. During this time there is no update to the parent control objective to fire the BR and make an update into the control record. Where as the Scheduled script can be executed right after controls are generated from the Baseline controls (Control objective). Hope this explain the why behind the scheduled job. Thank you for being my second eye right from the gate posting my question. You are a candid asset to the SN Community and greatly appreciate that! Wish you a great day and will chat soon. 

Thanks,

Mokemal!

[Mark Roethof]

Any luck with the Business Rule I shared?

Just thinking again, the custom "Control ID" field you added, should that always represent the value of the parent record? Or should you be able to override this value?

If this for example should always represent the value of the Parent record, the custom field might not be necessary. You could simply add the dotwalked Reference field from the Parent record to your form lay-out.

Here an example. I just added the dotwalked field to the Control form lay-out:

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020, 2021 ServiceNow Community MVP
2020, 2021 ServiceNow Developer MVP

---

LinkedIn
Community article, blog, video list

[Mohammed Kemal]

Hi Mark,

I concur adding the field as a dotwalked Reference field from the Parent record to the form lay-out. I have that field just like that before even creating custom field. You may see this before that any dot walked field will not exported as import set template since that field is not created within that table. Adding that field was the only option for work to be done outside the system and re-import the template. 

The BR works when we update the Control objective/parent table. The issue with that is that the parent record will not change after it is inserted or once created. Created scheduled job with  @Upender Kumar to update the Control ID field of the child tables periodically. 

Thanks!

[Mark Roethof]

Ah oke, so this is due to reporting/exporting that you need to field to actually be on the form. That's fine, good comment.

So the Control objective is created first, and immediately with the Reference? And after creation of the Control objective, the child Controls are created (or linked)? Why not at that moment generate the data for your custom field? Why with a Scheduled Job?

It could already be something like on insert of the sn_compliance_control, applying something like:

current.u_control_id = current.content.reference;

If my answer helped you in any way, please then mark it as helpful.

Kind regards,
Mark
2020, 2021 ServiceNow Community MVP
2020, 2021 ServiceNow Developer MVP

---

LinkedIn
Community article, blog, video list