Business Rule Scripting Help - Query other table and change current field

[JWayner]

I'm trying to set up a business rule to check if the data in a field called control_number in another table matches the current record number. If yes, then in the current record, I want to change the internal_validation_performed field to true. 

I've tried it as a before and after rule. I've selected update and insert. I can't seem to get it to work. I've added an info message throughout and no matter what, the info message is returned successfully (regardless if the query requirements are met), meaning the query is not working properly. Can anyone help?

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

var currentNumber = current.getValue('number'); 
var controlTests = new GlideRecord('x_hotm_orm_risk_registry');
controlTests.addQuery('control_number', currentNumber);
controlTests.query();

if (controlTests.next()) {
current.setValue('internal_validation_performed', true);
}

})(current, previous);

[Steven Parker]

Have you tried logging the currentNumber field to see if it contains the value you want it to?

Since this is a Business Rule and if it were against the 'x_hotm_orm_control_registry' table (as an example)...you should be able to just refer to that number as 'current.number' instead of the getValue.  When you run against a table, you can grab values from it just using 'current.whatever_field_name'.  Also, if either of these are a custom tables, make sure the field names aren't 'u_number' for example since the system adds u_ in front of custom table fields.

[JWayner]

Great idea!

Upon doing this, I saw that with the following script, it is accurately identifying the currentNumber so I'm assuming the query section is the issue. I've verified the table name and the field name so I'm not sure what the problem is.

(function executeRule(current, previous /*null when async*/) {
    var currentNumber = current.number;
    var controlTests = new GlideRecord('x_hotm_orm_risk_registry');
    controlTests.addQuery('control_number', currentNumber);
    controlTests.query();
    
    if (controlTests.next()) {
        current.setValue('internal_validation_performed', true);
        current.setWorkflow(false); // To prevent retriggering the business rule
        current.update(); // Save the changes to the current record
    }
})(current, previous);

[Steven Parker]

Did you add an alert inside of the If statement to see if you are making it into the IF?

Also you have the following:

current.setValue('internal_validation_performed', true);

Which would set the value on the table the business rule is running on.

Should it be setting that value on the table you are glideRecord to like below:

controlTests.setValue('internal_validation_performed', true);
controlTests.update();

or is current accurate?

[JWayner]

So I do want it to update on the current record, not the controlTest record.

The error message in put in the code stops working as soon as it's within the if statement.

[Steven Parker · Accepted solution]

One other thing to consider...

What type of field is Number and what type of field is Control Number?  Are they both reference, is one a string and one a reference, etc?

This may be the reason the query isn't working...maybe you are getting a sys_id from a reference, but one of the fields is a string field which is text.