glideAggregate Sum not populating field

ssturde · ‎07-06-2016

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 · ‎07-11-2016

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

View solution in original post

Chuck Tomasi · ‎07-06-2016

Hi Stephen,

This one has me confused:

var usam = count.addAggregate('SUM','u_total_users_impacted');

I don't think you want to add another aggregate after you retrieve your count.

First question, what is the value of count at this point?

I'm going to assume that 'u_so_incident_impacts' is your parent table. If that's the case, you want to count the records on the child table that have the same parent sys_id, not the number.

ssturde · ‎07-06-2016

Hi Chuck -

Chalk it up to copy and paste from the original thread I found it on.

The value is a sum, rather than a count. The parent table is the 'incident' table, and the child records are in the 'u_so_incident_impacts' table. I want the sum of all the records with the same parent incident number.

I removed the 'var usam' line, and added a message line (gs.addInfoMessage(gs.getMessage(count));) to see what the count variable was producing, if anything. The message displayed all of the values from the child records individually that I want to sum up, 19 times.

Chuck Tomasi · ‎07-06-2016

So what does your script look like right now?

ssturde · ‎07-07-2016

	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');

gs.addInfoMessage(gs.getMessage(count));

	// find and store the count value in the related record.
	}