GlideAggregate SUM not working...

[Robert_Cartwrig]

Hi,

I'm using a fairly simple Business Rule to try to get the sum of a field called u_total_cost on RITM and then update its parent sc_request.  It all works...EXCEPT it is taking the last value - NOT the SUM.

I'm running this as an After Business Rule that runs on Insert and Update.  It is also updating the Total Cost field on the RITM.

Here is the script:

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

	//Update Request Item Total Cost
	var thisRecord = current.sys_id;
	
	var gr = new GlideRecord ('sc_req_item');
	gr.addQuery('sys_id',"=",thisRecord);
	gr.query();
	
	if (gr.next()) {
	var a = current.price;
	var b = current.quantity;
	current.u_total_cost=  a*b;
	current.update();
	}
	

	
	//Update parent Request
	var reqID = current.request.sys_id;
	
    var agg = new GlideAggregate('sc_req_item');
    agg.addQuery('request', reqID);
    agg.addAggregate('SUM', 'u_total_cost');
    agg.query();
	
        var cost = 0;
	
    while (agg.next()) {
        cost = agg.getAggregate('SUM', 'u_total_cost');
		
		var req = new GlideRecord ('sc_request');
		req.addQuery('sys_id',reqID);
		req.query();
		if (req.next()); {
			req.u_purchase_cost = cost;
			req.update();
		}	
    }


})(current, previous);

Can anyone tell me why this isn't summing?

Thank you,

Robert

[Mike Patel]

Below is what SN has in docs so try to do something similar.

Docs - https://docs.servicenow.com/bundle/jakarta-application-development/page/script/glide-server-apis/con...

var totalCostSum = new GlideAggregate('fixed_asset');
totalCostSum.addAggregate('SUM', 'total_cost');
totalCostSum.query();
 
while (totalCostSum.next()) {
  var allTotalCost = 0;
  allTotalCost = totalCostSum.getAggregate('SUM', 'total_cost');
  aTotalCost = totalCostSum.getValue('total_cost');
  gs.print('Unique field value: ' + aTotalCost + ', SUM = ' + allTotalCost + ', ' + allTotalCost/aTotalCost + ' records');
}

[Mike Patel]

Also try

(function executeRule(current, previous /*null when async*/) {
	
	var a = current.price;
	var b = current.quantity;
	current.u_total_cost=  a*b;
	current.update();
	
	//Update parent Request
	var reqID = current.request.sys_id;
	var agg = new GlideAggregate('sc_req_item');
	agg.addQuery('request', reqID);
	agg.addAggregate('SUM', 'u_total_cost');
	agg.groupBy('u_total_cost');
	agg.query();
	var cost = 0;
	while (agg.next()) {
		cost = agg.getAggregate('SUM', 'u_total_cost');
		var req = new GlideRecord ('sc_request');
		req.addQuery('sys_id', reqID);
		req.query();
		if (req.next()){
			req.u_purchase_cost = cost;
			req.update();
		}	
	}
})(current, previous);

[rajneeshbaranwa]

I think you do not need to GlideRecord  sc_req_item table , You can just use below codes to update total_cost on ritm table.

var a = current.price;

var b = current.quantity;

current.u_total_cost= a*b;

current.update();

I believe issue is happening because you are using current.update() in between the business rule which could again be trigerring same business rule.

I would advice to create a separate before business rule to update u_total_cost on RITM table. Please do not use current.update() on before business rule , It is not required and cause issues.

You can leave the remaining code in this business rule and I think it should work.

[Robert_Cartwrig]

Hi Mike, this is actually what I did with the input from Tony above.  Had I not already been in that thread, I would have done this and marked this correct.

Thank you!

Robert

[Mike Patel]

No problem Robert, as long as it answers your question marking any is fine.