Compute the average of the values entered in a field on multiple records under related list

[ss123]

Hi everyone!

I would like to ask / be guided on how to get the average of the entered value in a field in multiple records under related list. I have created a field "Objective fulfillment" in different table so that will become the container field of the average. See below:

Table: BUS

Field: Objective fulfillment (Average of the "Actual achievement till date"; this should be automatically computed based on the number of rows in related list below)

Under related list, here is the field that will be computed (average) based on the number of rows in related list

Table: Goal

Field: "Actual achievement till date"

Thank you in advance 🙂

[Shruti · Accepted solution]

Hi,

You can write 'After' update Business rule on 'Goal' table.

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

var total = 0,
count = 0,
actual_achievement, objective_fulfillment;

var gr = new GlideRecord("GOAL_table");  //Replace GOAL table name
gr.addQuery('Business_Unit_Strategy',current.Business_Unit_Strategy); //Replace query condition based on the requirement
gr.query();
while (gr.next()) {
count++;
total += Number(gr.Actual_achievement_till_date); //Replace Actual achievement till date field name
}

if (total)
actual_achievement = Math.round(total / count);

if (actual_achievement) {

var gr1 = new GlideRecord('BUS Table'); //Replace BUS table name
gr1.addQuery('sys_id', current.Business_Unit_Strategy); //Replace query condition based on the requirement
gr1.query();
if (gr1.next()) {

gr1.objective_fulfillment = actual_achievement; //Replace Objective fulfillment and actual achievement field name
gr1.update();
}

}

})(current, previous);

[ss123]

Hi Stark,

I will try to add your script on Business Rule as advised. Will keep you posted on this 🙂

Thank you so much!!

[ss123]

Thank you Stark for the help! Very much appreciated. It is working as intended 🙂