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

Pradeep Sharma · ‎07-07-2016

Hi Stephen,

I haven't gone through whole code. But here is little explanation.

To store value in field "u_total_users_impacted" you have to use

current.u_total_users_impacted = sum.u_parent.getDisplayValue(); //Here replace u_parent with the referncig variable.

Chuck Tomasi · ‎07-07-2016

Hi Pradeep,

The line for sum.u_parent.getDisplayValue() was not to affect his total users, it was to display the field in the gs.print() message. It's right from the wiki (only they used the category field.) That way you don't get a list of numbers, you know what records they go with.

Chuck Tomasi · ‎07-07-2016

Hi Stephen,

Yes, use the field from the child record on line 15. What's being summed up from the child records has a label. We're just trying to get the diagnostic message to display. If you don't need that, then just remove it.

To make the code display nicely, you need to look for the "Use advanced editor" link in the upper right of the comments box. It doesn't normally show when you reply directly from the inbox. Once in the advanced editor, you will see an icon >> that allows for script formatting. Highlight your text and select Javascript. Done.