glideAggregate Sum not populating field

[ssturde]

I have parent records with child records. The child records each record unique values of impacted users. When I attempt to use glideAggregate to sum the individual child values into a single field in the parent record, I get no value returned.

Any help is appreciated:

var rec = new GlideAggregate('u_so_incident_impacts');

	rec.groupBy('number');
	rec.addAggregate('SUM','u_users_in_game_impacted');

	rec.query();
	while(rec.next()){
	var count = rec.getAggregate('SUM','u_users_in_game_impacted');

	var usam = count.addAggregate('SUM','u_total_users_impacted');
	current.u_total_users_impacted = usam;
	// find and store the count value in the related record.
	}

I found the original script on another thread found here:

Add values from field on a related list

[Chuck Tomasi]

Ah, that makes more sense then. Just out of curiousity... what do you anticipate for the number of child records a parent record could have? I'm curious if GlideAggregate is just confusing the issue at this time and we'd be better off with a straight GlideRecord query and adding the numbers ourselves on each parent record from all the children it has.

Are we talking single digits typically? hundreds? thousands?

[ssturde]

Typically, these will be single digits, but could be up to 50 or so.

[Chuck Tomasi]

Alright. Since we cannot get in sync on the GlideAggregate thing, and that solution is best when updating ALL parent incident records, let's keep it simple for now.

I'm rebuilding the tables now per your script definition you sent earlier. Just one last question to verify our data models are in sync.

Is the u_parent field on u_so_incident_impacts the reference to incident?

[ssturde]

Yes, u_parent is a reference field to the Parent incident Sys_ID

[Chuck Tomasi · Accepted solution]

OK, here's the script I used in the calculated field on the dictionary entry for incident.u_total_users_impacted...

(function calculatedFieldValue(current) {

   var childTable = 'u_so_incident_impacts';
   var numberField = 'u_users_in_game_impacted'; // what do we want to sum up?

   var count = 0;
   var gr = new GlideRecord(childTable);
   gr.addQuery('u_parent', current.sys_id);
   gr.query();

   while (gr.next()) {
   var n = parseInt(gr.getValue(numberField), 10);
   count += n;
   }
   return count;   // return the calculated value

})(current);

Something to keep in mind... the calculated field isn't STORED in the database until you update the record. So you MAY incur performance issues if you present a list and it has to do all these gliderecord lookups to show you the list (assuming you put that field in the list layout.)

To improve performance, do a one-time forced-update to the incident records so it retally's the numbers and stores them. Going forward, they'll maintain themselves.

var inc = new GlideRecord('incident');
inc.query();
while (inc.next()) {
   inc.setWorkflow(false);
   inc.autoSysFields(false);
   inc.setForceUpdate(true);
   inc.update();
}

You can run this from scripts background or create a Fix script if you like (makes it easier to move from dev to prod in an update set.)

Fix Scripts - ServiceNow Wiki