Populate Field with Average Value of Related Survey Metric Values

gfancher · ‎05-29-2020

I need to populate a field on the Project record with the average value of one metric from all of the survey's submitted for the particular project. I have created a single score report that generates the value, but I don't believe that value is stored anywhere for me to pull into a field.

I'm attempting now to use an onLoad Client Script to grab all of the associated metric values, calculate the average and set the field value. I'm not super proficient with javascript and I suspect I'm not properly collecting the values before attempting the calculation. It's also very likely that this is the wrong path entirely. Any help would be very much appreciated.

Here is what I have:

function onLoad() {

var sys_id = g_form.getValue('sys_id');

var gr=new GlideRecord('asmt_assessment_instance_question');

gr.addQuery('instance.task', sys_id);
gr.addQuery('metric','My overall Satisfaction Rating for the project:');

gr.query();

while(gr.next()){

var scores = gr.value ;
var totalSum = 0;
for(var i in scores) {
totalSum += scores[i];
}

var scoresCnt = scores.length;
var average = totalSum / scoresCnt;

g_form.setValue('u_project_score_decimal',average);

}
}

Michael Ritchie · ‎06-04-2020

Yahoo! Glad that worked. My apologies for the back and forth and I appreciate your patience on this. That isNaN is supposed to detect null values so surprised it didn't work. Just in case here is an udpate script where I removed the debugging statement and also the scoreList since that isn't needed.

(function executeRule(current, previous /*null when async*/) {
	var taskID = current.instance.task_id.toString();
	// Only run if linked to a task
	if (!gs.nil(taskID)) {
		var taskRec = new GlideRecord(current.instance.task_id.sys_class_name.toString());
		// As a safeguard check for the u_project_score_decimal field and ensure the task is found
		if (taskRec.isValidField("u_project_score_decimal") && taskRec.get(taskID)) {
			var instanceID = current.getValue("sys_id");
			var metricID = current.getValue("metric");
			
			var scoreSum = 0;
			var scoreCount = 0;
			var value = current.getValue("value");
			if (!gs.nil(value) && !isNaN(value)) {
				scoreSum = parseInt(value, 10);
				scoreCount = 1; // Setting to 1 for current record;
			}
			var instanceQuestion = new GlideRecord("asmt_assessment_instance_question");
			instanceQuestion.addQuery("metric", metricID); // get records with same metric
			instanceQuestion.addQuery("sys_id", "!=", instanceID); // exclude current record
			instanceQuestion.addQuery("instance.task_id", taskID); // only get records tied to the same task
			instanceQuestion.query();
			while (instanceQuestion.next()) {
				value = instanceQuestion.getValue("value");
				if (gs.nil(value) || isNaN(value)) {
					continue;
				}
				scoreSum += parseInt(value, 10);
				scoreCount += 1;
			}
			
			//Only update project if we have actual scores averaged
			if (scoreCount > 0) {
				var scoreAverage = scoreSum / scoreCount;
				taskRec.u_project_score_decimal = scoreAverage;
				taskRec.update();
			}
		}
	}
})(current, previous);

View solution in original post

Michael Ritchie · ‎05-29-2020

The use of GlideRecord in client scripts is a bad practice. It is best do to this server side. So question is when do you need this average calculated? Is it just for display purposes or do you want to store it? You could move your code to a business rule of type display to set the value so you can view it on the form. If you need this updated on a regular basis for all projects then a scheduled job of some sort could run, loop through the projects and update the value.

gfancher · ‎05-29-2020

Thank you Michael - I would like to store the value. This would need to be updated regularly so I'm not opposed to the idea of setting it up as a scheduled job. Accepting that using GlideRecord in client scripts is bad practice, can you advise on what my code is missing that the values are not being collected and calculated?

Michael Ritchie · ‎05-29-2020

OK got it. Another option is to update the average in real time as surveys are submitted. This would involve a business rule on the asmt_assessment_instance_question table so as records are inserted, it would update the linked project with the new average. Thoughts?

gfancher · ‎05-29-2020

I really like that idea! It hadn't occurred to me to run it on the asmt_assessment_instance_question table. That makes a lot of sense.