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

gfancher · ‎06-04-2020

Thank you so much for sticking with me on this! I really appreciate your help.

Michael Ritchie · ‎06-04-2020

You are very welcome. It was a fun puzzle to solve.