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]

Hi Upender,

Tried yours updated handy code but not updating the 2nd table value for Control id.

Any thoughts? 

[Mark Roethof]

Yeah it's like I mentioned before, quering on u_control_id = current.reference and then setting the same does not make sence. It just won't work. 

Kind regards,
Mark

[Upender Kumar · Accepted solution]

Thanks @mokemal 

 As parent record will not change after it is inserted. We created a scheduled job to update the Control ID field of the child tables.

If you need you can create a before BR on sn_compliance_control

var referenceValue = new GlideRecord('sn_compliance_policy_statement');
	if(referenceValue.get(curent.content)
	{
current.u_control_id=referenceValue.reference

	}

[Mohammed Kemal]

Thanks @Upender Kumar 

Here is the Scheduled script for anyone who is referring in the future this thread.  To update the field in the child table periodically from the parent table. Thanks my brother Upender for helping this out.

var parrentReference = new GlideRecord('sn_compliance_policy_statement');
parrentReference.query();
while (parrentReference.next()) {

    var childControl = new GlideRecord('sn_compliance_control');
    childControl.addQuery('content', parrentReference.getUniqueValue());

    childControl.query();

    while (childControl.next()) {
        if (childControl.u_control_id != parrentReference.reference) {

            childControl.u_control_id = parrentReference.reference;
            //when this script run we want the workflow and SysFields to be false
            childControl.autoSysFields(false);
            childControl.setWorkflow(false);
            childControl.update();
        }
    }
}

[Mark Roethof]

Have you seen my last post? Just wondering why this can't already be done on insert of the Control. Why a Scheduled Job?

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