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

Chuck Tomasi · ‎07-07-2016

The only thing I see missing from this script is the actual "update" part to the parent record. I'll patch that in now...

Chuck Tomasi · ‎07-07-2016

Here you go. An updated version of the script that updates the parent record. Update fields as necessary. I removed the line with the getDisplayValue(), but I have a new one to set to get the record. Unfortunately my test instance was wiped this morning and I cannot fully test this code.

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 on the child

var parentTotal = 'u_total';


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 gr = new GlideRecord(parentTable);

     gr.get(sum.u_parent); // change u_parent to the same as line 3 above

     gr.setValue(parentTotal, impacts);

     gr.update();

}

ssturde · ‎07-08-2016

I've updated my script as shown below.

I added line 10 to ensure only the child records related to that parent were summed up.

I commented out several lines for now, as it gave the error, "Unique Key violation detected by database (Duplicate entry 'org.mozilla.javascript.Undefined' for key 'PRIMARY')"

I'm guessing this is because the 'parentField' variable is the incident sysID.

If I comment out line 18 alone, a value is returned, but it's incorrect. Additionally, I get an error displayed below the field containing the total calculation saying, "unparseable number:" and it gives a sys_id. I found the record with the sys_id, and it was a blank incident record. I then noticed that new blank incident records were being created by system, or my user name every time the field calculates.

Did I use the wrong field somewhere?

var parentTable = 'incident';   

var childTable = 'u_so_incident_impacts';   

var parentField = current.sys_id; // ref field from child to parent   

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

var parentTotal = 'u_total_users_impacted';



var sum = new GlideAggregate(childTable);   

sum.groupBy(parentField);   

sum.addQuery('u_parent_incident', parentField);

sum.addAggregate('SUM', numberField);   

sum.query();   

   

var impacts = 0;   

while (sum.next()) {   

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

//     var gr = new GlideRecord(parentTable);   

//     gr.get(sum.parentField); // change u_parent to the same as line 3 above   

//     gr.setValue(parentTotal, impacts);   

//     gr.update();   

}

Chuck Tomasi · ‎07-08-2016

Hi Stephen,

You don't need line 10. That's where we went wrong before. It's the job of groupBy() to gather them all together.