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]

Ugh sorry, typo as I had copied the isNaN function from above and left the ! negating that so the previous code was skipping through the ones with a value on line 25.  The purpose of that check is to skip past any question that doesn't have a valid number answer.  The below code should now work.

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

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

[gfancher]

Thanks Michael - This time the field remained unchanged again after the submission of the survey. Here is the log. Besides the NaN entries, I think the scoreCount should be 3 for this project as I've submitted 3 surveys during testing.

[Michael Ritchie]

Darn ok lets go back to logging the values so we can analyze whats happening:

(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");
			scoreList.push(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");
				scoreList.push(value);
				if (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;
				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]

Ok, sounds good - Here is the log:

[Michael Ritchie]

Thanks so there is a null value there which could be causing the NaN but my previous code was capturing the raw data.  I moved the capture code to only get values that are captured for average, please try this and let me know results:

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