Set Value of current record to those of the same assignment group

[TerryC03]

Hello,

I'm trying to take a calculated value from the sn_safe_sprint table. I currently have two queries: one for calculation and the other to set the value of the same field to that of similar groups. For example, I have created a field named "Average Completed Points" (u_average_completed_points; integer).

Within the business rule (before), my first query calculates the Average Completed Points value:

// Query the sn_safe_sprint table to get the latest 3 updated sprints
var gr = new GlideRecord('sn_safe_sprint');
gr.addQuery('state', 3);
gr.addQuery('assignment_group', current.assignment_group);
gr.orderByDesc('sys_updated_on');
gr.setLimit(3);
gr.query();

//variable to store the total completed points
var totalCompletedPoints = 0;
var count = 0;

// Loop through the records and calculate the total completed points
while (gr.next()) {
	gs.info("act "+gr.getValue('actual_points') +" " +gr.getValue('assignment_group'));

totalCompletedPoints += parseInt(gr.getValue('actual_points'));
count++;
}

current.u_average_completed_points = totalCompletedPoints/count;
gs.info("avg "+current.u_average_completed_points);

For this example, the average field calculates to 9 (shown in picture as well). Next, I have another query to take the value of 9 and set the value to all records with the same SAFe Team.

In this picture the average completed points is 9 for SAFe Team "Employee Portal Features Team", I would like to set that value of 9 for all records with the same team. However, my code is not working. Here is what I have:

var grTeam = new GlideRecord('sn_safe_sprint');
grTeam.addQuery('state', 3);
grTeam.addQuery('assignment_group', current.assignment_group);
grTeam.query();

while(grTeam.next()){
	gs.info("Team "+current.assignment_group);
	grTeam.u_average_completed_points = totalCompletedPoints/count;
}

Any advice would be helpful. I tried grTeam.setValue(), but that did not work. Full code:

(function executeRule(current, previous /*null when async*/) {
// Query the sn_safe_sprint table to get the latest 3 updated sprints
var gr = new GlideRecord('sn_safe_sprint');
gr.addQuery('state', 3);
gr.addQuery('assignment_group', current.assignment_group);
gr.orderByDesc('sys_updated_on');
gr.setLimit(3);
gr.query();

//variable to store the total completed points
var totalCompletedPoints = 0;
var count = 0;

// Loop through the records and calculate the total completed points
while (gr.next()) {
	gs.info("act "+gr.getValue('actual_points') +" " +gr.getValue('assignment_group'));

totalCompletedPoints += parseInt(gr.getValue('actual_points'));
count++;
}

current.u_average_completed_points = totalCompletedPoints/count;
gs.info("avg "+current.u_average_completed_points);

var grTeam = new GlideRecord('sn_safe_sprint');
grTeam.addQuery('state', 3);
grTeam.addQuery('assignment_group', current.assignment_group);
grTeam.query();

while(grTeam.next()){
	gs.info("Team "+current.assignment_group);
	grTeam.u_average_completed_points = totalCompletedPoints/count;
}
})(current, previous);

[Alok Das]

Oops, it's a silly one!

You are missing grTeam.update(); in the last line due to which the record on "sn_safe_sprint" table is not getting updated.

The script will be as below:

(function executeRule(current, previous /*null when async*/) {
// Query the sn_safe_sprint table to get the latest 3 updated sprints
var gr = new GlideRecord('sn_safe_sprint');
gr.addQuery('state', 3);
gr.addQuery('assignment_group', current.assignment_group);
gr.orderByDesc('sys_updated_on');
gr.setLimit(3);
gr.query();

//variable to store the total completed points
var totalCompletedPoints = 0;
var count = 0;

// Loop through the records and calculate the total completed points
while (gr.next()) {
	gs.info("act "+gr.getValue('actual_points') +" " +gr.getValue('assignment_group'));

totalCompletedPoints += parseInt(gr.getValue('actual_points'));
count++;
}

current.u_average_completed_points = totalCompletedPoints/count;
gs.info("avg "+current.u_average_completed_points);

var grTeam = new GlideRecord('sn_safe_sprint');
grTeam.addQuery('state', 3);
grTeam.addQuery('assignment_group', current.assignment_group);
grTeam.query();

while(grTeam.next()){
	gs.info("Team "+current.assignment_group);
	grTeam.u_average_completed_points = totalCompletedPoints/count;
grTeam.update();
}
})(current, previous);

Kindly mark my answer as Correct and helpful based on the Impact.

Regards,

Alok

[TerryC03]

I believe it's not recommended to use update() for a before business rule.

[Alok Das]

current.update() is not recommended on business rule but you are using glide record  to update record from other table, so it will need grTeam.update() to update the record. It's fine to use it.

For your reference regarding current.update():

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0715782