Calculating the average rating from survey responses

[jakehford]

I want to calculate the average of each 5 survey responses, pertaining to their associated incident ticket. We are using legacy surveys, and the only table that actually shows the incident number for the survey response is Task Survey. So, I added a custom column called u_average_total_rating onto Task Survey, so I can write the value after the survey is submitted. The script works, the problem is when the business rule gets executed.. Survey Responses get completed, and Task Surveys get updated all at the same time, so Task Survey doesn't actually know the answer_integer value.. My script below is running on the Task Survey table, before an insert or update. I tried doing the script as an after, but it doesn't run at all then. 

The script actually works if I manually update the task survey record again, calculates and sets the custom average value like it's supposed to. Basically, if I run the business rule twice, it works... Is there a way I can trigger this business rule to run twice automatically? 

function onBefore(current, previous) {
	
	calculateAverage();
	
	function calculateAverage(){
		
		var total = 0;
		var NUM_OF_RESPONSES = 4;
		var avg = 0;
                //Completed Date of survey, this matches the created date of responses.
                //This is how I tie the repsonses together with the task survey.
		var taskSurveyInstance = current.completed_date;
		var response = new GlideRecord('survey_response');
		response.addQuery('sys_created_on', taskSurveyInstance);
		response.query();
		while ( response.next() ) {
			total += response.answer_integer;
		}
		avg = total / NUM_OF_RESPONSES;
		current.u_average_total_rating = avg;

	}
}

[Shane J · Accepted solution]

Would it make more sense to run this as a Scheduled Job instead?

[jakehford]

Thanks, giving that a try now

[jakehford]

Thanks Shane! I had to modify my code a little, but got it working! Learn something new everyday, was my first scheduled job lol. Here is the code if anyone ever needs to do something similar.

(function(){
	
	var taskSurvey = new GlideRecord('task_survey');
	//Get all task survey records that are "Completed"
	taskSurvey.addQuery('state', 'completed');
	//Only get task survey records that have an empty average total rating
	taskSurvey.addNullQuery('u_average_total_rating');  
	taskSurvey.query();
	while( taskSurvey.next() ){
		//For every task survey, loop through the surevey response records
		var response = new GlideRecord('survey_response');
		//Only return records with a created date that matches the task survey completed date.
		//When a survey response is created, the task survey state changes to completed, so these two match.
		response.addQuery('sys_created_on', taskSurvey.completed_date);
		response.query();
		var total = 0;
		var NUM_OF_RESPONSES = 4;
		var avg = 0;
		while ( response.next() ) {
			//For every response, add the answer integer to the total.
			total += response.answer_integer;
		}
		avg = total / NUM_OF_RESPONSES;
		taskSurvey.u_average_total_rating = avg;
		taskSurvey.update();
	}
	
})();