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

}
}

[Michael Ritchie]

Hum not sure what's up with the NaN (not a number) so lets get the detail behind those by putting the values into an array.  Is insert still checked?  If so uncheck it and keep just update checked and then update the script to the following so we can see the values the code is supposed to average:

(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 = parseInt(current.getValue("value"), 10);
			var scoreList = [current.getValue("value")];
			var 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.query();
			while (instanceQuestion.next()) {
				scoreList.push(instanceQuestion.getValue("value"));
				scoreSum += parseInt(instanceQuestion.getValue("value"), 10);
				scoreCount += 1;
			}
			
			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]

I unchecked insert, adjusted the code. Here is the log entry generated by my test:

[Michael Ritchie]

OK got it, try this updated code instead that checks for values.  I also realized I forgot to include query to only grab values for the same project - noticed your counts were going up each time.  Hopefully this is it.

(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 (!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 (!isNaN(value)) {
					continue;
				}
				
				scoreSum += parseInt(instanceQuestion.getValue("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);
				taskRec.u_project_score_decimal = scoreAverage;
				taskRec.update();
			}
		}
	}
})(current, previous);

If the above works, just remove line 36 and you should be set!

Please mark any post as helpful or the correct answer to your question if applicable so others viewing may benefit.

[Michael Ritchie]

OK got it, try this updated code instead that checks for values.  I also realized I forgot to include query to only grab values for the same project - noticed your counts were going up each time.  Hopefully this is it.

(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 (!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 (!isNaN(value)) {
					continue;
				}
				
				scoreSum += parseInt(instanceQuestion.getValue("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);
				taskRec.u_project_score_decimal = scoreAverage;
				taskRec.update();
			}
		}
	}
})(current, previous);

If the above works, just remove line 36 and you should be set!

 

Please mark any post as helpful or the correct answer to your question if applicable so others viewing may benefit.

[gfancher]

This worked much better! It seems to be miscalculating the count though. I submitted 2 surveys for the same project. The log shows a score count of 1 for both, making the average score whatever score was last submitted.