Accessing two tables from within a single business rule

[cai]

Does anyone know if it is possible to have a business rule that accesses two separate tables? Basically, I am parent and child tables. I want to query the child table for all records meeting a certain condition (and count the number of records found) based on the selected value from the parent record. Then, I want to write the value of the count into a field in the parent table. Can all this be done within a single business rule?

[Deepa Srivastav]

Yes can be done..write BR in parent table and access child table using GlideRecord and add the condition in addQuery..

GlideRecord - ServiceNow Wiki

http://wiki.servicenow.com/index.php?title=Scripting_in_Business_Rules#gsc.tab=0

Mark Correct if it solved your issue or hit Like and Helpful if you find my response worthy.

[Robert Beeman]

Adding to this, it should be a 'Before' Insert/Update since you are trying to set a value in the parent record.

[Abhinay Erra]

You can absolutely do this. Give more details on your requirement, I can provide you the code

[cai]

Thanks, Abhinay. I still cannot get it to work. My code is below:

var gr = new GlideAggregate('students');

gr.addQuery('school_name,'=',current.school_name);

gr.addQuery('score','>',70);

gr.addAggregate('COUNT');

gr.query();

while (gr.next()) {

  var total_no_high_score = gr.getAggregate('COUNT');

}

var gr2 = new GlideAggregate('schools');

current.score_count = total_high_score;

What I am trying to achieve is the following:

Let's assume that you have two tables. The parent table contains the names of the schools. The child table contains the names of students attending those schools along with their scores. The user is first shown a list of all students, the schools they are attending, and their scores. What I want to be able to accomplish is when the user clicks on the school from that list view, the app opens up a new page showing details of the school, which includes a count of the number of students at that school with a score greater than 80 and less than 90.

Not knowing of the best way to approach that, I decided to create a business rule and use the code as shown above. Yet, it does not work. Field called school_name is field in the Students table and score_count is the filed in the Schools table. Any suggestions or assistance you may provide would be greatly appreciated.

Thanks,

Cairo