How to sum up values with Survey Instance

mattgr · ‎09-12-2022

Hi Friendly crowd.

How can I sum up values from the survey instance and then place it in the 'Survey total score' (u_survey_total_score)

The end goal is to create a notification based on the score. Any help very much appreciated.

Matt

Claude DAmico · ‎09-15-2022

Here is what I've come up with.

The Business Rule runs on the asmt_assessment_instance_question table after insert/update and combines both GlideRecord to get the instance that needs to be updated and GlideAggregate to get to SUM you've requested to put into the u_survey_total_score field on the instance.

var ir = new GlideRecord('asmt_assessment_instance');
ir.get(current.instance);

var gr = new GlideAggregate('asmt_assessment_instance_question');
gr.addQuery('instance', current.instance);
gr.addAggregate('SUM', 'value');
gr.setGroup(false);
gr.query();
while(gr.next()){
	var sumVal = gr.getAggregate('SUM', 'value');
}

ir.u_survey_total_score = sumVal;
ir.update()

Claude E. D'Amico, III - CSA

View solution in original post

Claude DAmico · ‎09-18-2022

Sorry for the delayed response, weekends I check on things only if I find some spare time. Thanks for the kudos though!

Interesting that it missed something... I created the script using my OOB PDI which worked correctly using the demo data that exists. I do see that the total in the second one worked correctly so I'm not entirely sure why the first one didn't.

You can see the GlideAggregate (global) documentation here.

To explain the script though:

Get the instance record.
Create an aggregate query where we want just the instance questions for the instance we got in step 1 and SUM the value column.
- The setGroup(false) part makes it so things don't get grouped by different questions or other groupings.
I used a WHILE loop to get the SUM calculation, but you may be able to do an IF statement instead per the documentation example for addAggregate().
Set the u_survey_total_score field in the instance record to the SUM calculation.
Save (update) the instance with the new value.

Since the first one didn't work, I'm wondering if filtering the asmt_assessment_instance_question table gives you all the correct records for the AINST0012188 instance.

Claude E. D'Amico, III - CSA

mattgr · ‎09-18-2022

ok, I found what I did wrong, the BS was set to 'before' instead of 'after', hence wonky calculations, many thanks for the help

Claude DAmico · ‎09-19-2022

Fantastic! I'm glad you figured it out! You're very welcome!

Claude E. D'Amico, III - CSA