Populate Field with Average Value of Related Survey Metric Values

[gfancher]

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);

}
}

[blanca12]

I really like that idea! 

Tutuapp 9Apps Showbox

[gfancher]

My first test with this new code looked to work correctly. I was curious about the scoreCount though, as I had actually reached 6 surveys. I ran another test, submitting one survey for a different project. That log showed a scoreCount of 2 with NaN values for sum and average. I then submitted another survey for that second project and it worked correctly. 

[Michael Ritchie]

I don't understand why those null values are getting stored since the isNaN is supposed to trap that.  I adjusted the code to also check for null so crossing my fingers this works:

(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 scoreList = [];
			var value = current.getValue("value");
			if (!gs.nil(value) && !isNaN(value)) {
				scoreList.push(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;
				}
				scoreList.push(value);
				scoreSum += parseInt(value, 10);
				scoreCount += 1;
			}
			
			//Only update project if we have actual scores averaged
			if (scoreCount > 0) {
				var scoreAverage = scoreSum / scoreCount;
				gs.info("gfancher taskID: " + taskID + " scoreSum: " + scoreSum + " scoreCount: " + scoreCount + " scoreAverage: " + scoreAverage + " scoreList: " + scoreList.toString());
				taskRec.u_project_score_decimal = scoreAverage;
				taskRec.update();
			}
		}
	}
})(current, previous);

[gfancher]

Success! I submitted 2 surveys on a project and the average score was generated successfully each time. 

I really appreciate all of your work on this, thank you!

[Michael Ritchie · Accepted solution]

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);