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]

ok let me do a little investigation and will give you some examples

[gfancher]

I really appreciate your help Michael, thank you!

[Michael Ritchie]

Here is some untested code which should work.  Here is what you need to do:

• Navigate to System Definition \ Business Rules and click New
• Set name to something like Project Management - Set Average Score Value
• Set the table to Assessment Instance Question [asmt_assessment_instance_question]
• Check Advanced
• Check Insert
• Leave When as Before
• Set Filter conditions to metric is 'My overall Satisfaction Rating for the project:'

Then click the Advanced tab and paste in the following script:

(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 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()) {
				scoreSum += parseInt(instanceQuestion.getValue("value"), 10);
				scoreCount += 1;
			}
			
			var scoreAverage = scoreSum / scoreCount;
			taskRec.u_project_score_decimal = scoreAverage;
			taskRec.update();
		}
	}
})(current, previous);

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

[gfancher]

Thank you! I've created the Business Rule as you specified, but the field on the Project form remains empty when I submit a survey associated to it. 

Here is my method of testing in case you see a flaw. I close a project, which assigns surveys to the PM, sponsor and resources. I then submit the survey. Returning to the closed project the Project Score field remains blank. 

Here is my business rule as well in case I missed something.

[Michael Ritchie]

Doh sorry, you also need to check the update checkbox on the business rule!  The record is already inserted so the user is prompted with the question.  Leave both insert and update checked and try it out.  If it works, then uncheck insert and test again since we don't want the business rule running too often.