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

[Tony Chatfield1]

H,

Your GlideAggregate query 'agg' is running against sc_req_item table,
yet you are querying on sys_id of the 'request record' from gr query. If I am not mistaken this is sc_request table and not sc_req_item?

[Robert_Cartwrig]

That is correct.  It's not the Request Item that is being updated, but the parent Request.  The event that triggers this is an update to the RITM.  This will kick off a SUM of the Total Cost on all RITMS with the same parent Request...then find the parent request and update a field called u_purchase_cost with this SUM.

Regards,

Robert

p.s. btw, this all works...the problem is that the "cost" is calculated as the Total Cost of the last RITM in the list...not the SUM.  I've used info messages to confirm...

[Tony Chatfield1 · Accepted solution]

Sorry, I didn't read your question properly.

Documentation and examples are a bit sparse but checking quickly in a dev instance
I found similar results to yours, until I added agg.setGroup(false); into my query.

[Robert_Cartwrig]

Boom! That was the trick.  And makes sense.  Thank you!