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-07-2016

Thanks for sharing. Not knowing the entire structure of your table, it would seem that you want to group by the parent reference field (e.g. u_parent?) not the number. You are summing up the records that have that in common, right?

ssturde · ‎07-07-2016

That's right. Thanx for helping me realize it was a reference field. Using that and help from the wiki, I went back and started from scratch using GlideAggregate - ServiceNow Wiki

I'm now getting a value in my field, but I get one of two answers, both of which are incorrect:

If I use "if (sum.next());" then I get a sum of like values of the smallest amount
If I use "while (sum.next();" then I get a sum of like values of the largest amount

var sum = new GlideAggregate('u_so_incident_impacts');

var parent = current.sys_id;

sum.groupBy(parent);

sum.addQuery('u_parent_incident', parent);

sum.addAggregate('SUM', 'u_users_in_game_impacted');

sum.query();

var impacts = 0;

while (sum.next())

impacts = sum.getAggregate('SUM', 'u_users_in_game_impacted');

I can't seem to aggregate all of the values into a total sum.

Chuck Tomasi · ‎07-07-2016

Let me see if I can make a similar version of what you are doing later today. I don't have exact tables and fields, but I'll try to make those easy to change variables. stay tuned...

Chuck Tomasi · ‎07-07-2016

Here we go Stephen... Seems to be working on my tables and data set. Replace the table/field names at the top with yours and adjust line 15 as needed.

var parentTable = 'u_parent_table';

var childTable = 'u_child_table';

var parentField = 'u_parent'; // ref field from child to parent

var numberField = 'u_number'; // what do we want to sum up?


var sum = new GlideAggregate(childTable);


sum.groupBy(parentField);

sum.addAggregate('SUM', numberField);

sum.query();


var impacts = 0;

while (sum.next()) {

     impacts = sum.getAggregate('SUM', numberField);

     var parent = sum.u_parent.getDisplayValue(); // Adjust this line for your parent field

     gs.print(parent + ' impacts total=' + impacts );

}

ssturde · ‎07-07-2016

I'm not sure what to put in line 15.

var parent = sum.u_parent.getDisplayValue(); // Adjust this line for your parent field

You have 'u_parent' listed in line, am I supposed to replace that with the field name of the field in the parent record where I want the value to display, or is this referencing the variable we created in line 3? I've tried the variable name and several field names there, but with no luck.

When I added an info message for the 'impacts' variable, and I can see all of the numbers it's supposed to sum up, but an info message on the 'parent' variable returns "undefined" for any of the field names I've entered.

Here's what I've got currently:

	var parentTable = 'incident';
	var childTable = 'u_so_incident_impacts';
	var parentField = 'incident.sys_id'; // ref field from child to parent
	var numberField = 'u_users_in_game_impacted'; // what do we want to sum up?

	var sum = new GlideAggregate(childTable);

	sum.groupBy(parentField);
	sum.addAggregate('SUM', numberField);
	sum.query();

	var impacts = 0;
	while (sum.next()) {
	impacts = sum.getAggregate('SUM', numberField);
	var parent = sum.u_total_users_impacted.getDisplayValue(); // Adjust this line for your parent field

	gs.print(parent + ' impacts total=' + impacts );
	}

'u_total_users_impacted' is the field on the incident record where I want to calculate and store this value.

p.s. how do you get your code to paste so nicely?