Sum Value from Child Tasks into Field on Parent

[richelle_pivec]

I am trying to write a business rule that will run whenever a child task is update to sum all of the values in a specific field (u_estimated_capital_hours) on the child tasks and write that value to a field on the parent. It has to account for the possibility that someone might actually change the value in the field and not just add a new number. 

I've hunted around the community and done some trial and error...here's what I've got so far:

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


 var additional = new GlideAggregate('u_demand_task');
 additional.Query('parent',current.sys_id);
 additional.addAggregate('SUM','u_estimated_capital_hours');
  additional.query();

	if (additional.next()) {
   var dmn = new GlideRecord('dmn_demand');
   if (dmn.get(current.sys_id)) {
     dmn.u_capital_implementation_hours = additional.getAggregate('SUM');
     dmn.update();
   }
}

I've set the BR to run at Server, to run After and to run at Insert and Update.

Any thoughts on how to get this thing to work?

Oh, I have tried this with both decimal fields

(u_estimated_capital_cost) and now with integer fields (u_estimated_capital_hours). Not knowing any better, I have also tried it running at Client and at Server. Through trial and error, I've concluded there is something wrong with the script, I just can't figure out what it is.

Thanks,

Richelle

Thanks,

Richelle

[Daniele Songini]

Hi,

you wrote additional.Query
instead of additional.addQuery

Please mark this as "Correct Answer" if I have given you a sufficient answer to your question.

Best Regards,
Daniele

[richelle_pivec]

I made that addition, but it did not make the script work.

Thanks though,

Richelle

[Daniele Songini · Accepted solution]

Hi Richelle,

create a br on the table u_demand_task, that run on after the record is inserted or updated, and the field u_estimated_capital_hours changes.

enter this in the script

var dmnId = current.parent;
var sum = 0;

// Retrive all the brothers of my demand task
var dmnTask = new GlideRecord( 'u_demand_task' );
dmnTask.addQuery( 'parent', dmnId );
dmnTask.query();

// Sum the values
while( dmnTask.next() ){
	 sum += Number( dmnTask.u_estimated_capital_hours );
}

// Update parent
var dmn = new GlideRecord( 'dmn_demand' );
if( dmn.get( dmnId ) ){
	dmn.u_capital_implementation_hours = sum;
	dmn.update();
}

let me know if it is okay.

Daniele

[richelle_pivec]

It worked like a charm.

The only note I'd make for anyone else who uses it...the fields on the u_demand_task table needed to be decimal fields. It did not work if they were integer fields. (When they were that type, it just wrote the last number and didn't sum the numbers together.) It didn't matter if the fields were decimal or integer on the parent record, but on the child one they all needed to be decimals.

And it even did subtraction too if the numbers needed to go down on the child task! I was afraid it wouldn't do that, but I suppose that's just another form of SUM.

Thanks much,

Richelle