Report: How to display Survey Question/Responses in columns.

Community Alums · ‎12-14-2016

I am trying to build a report having survey questions/responses in columns. I need the columns to show Question 1, Question 2, Question 3, Question 4, Question 5 and all the answers in a single row. How can I do this?

Community Alums · ‎11-12-2020

current.state means the business rule is running on the "asmt_metric_result" table, that has the state field. Customize your form layout to find the state field.

Sorry, I don't have gr.state in my script. It was in yours. This been a while so I was lost. Here is my working script. Please cleanup your script.

Business Rule:

Condition: current.metric_type.getDisplayValue()=='Satisfactory Survey' && current.state == 'complete'

(function executeRule(current, previous /*null when async*/) {

	var gr = new GlideRecord('u_satisfactory_survey_response');
	gr.addQuery('u_survey_instance', current.sys_id);
	gr.query();
	if (!gr.next()){
		gr.initialize();
	}
		
	gr.u_survey_instance = current.sys_id;
	gr.u_metric_type = current.metric_type;
	gr.u_assigned_to =  current.user;
	gr.u_incident_number = current.trigger_id;
	gr.u_incident_assigned_to = current.related_id_1;
	gr.u_incident_assignment_group = current.related_id_2;
	
	var result = new GlideRecord('asmt_metric_result');
	result.addQuery('instance', current.sys_id);
	result.query();
	while(result.next()){
		if(result.metric == 'edfeec4f373ea20009dc65e2b3990ed4'){ //1
			gr.u_question_1 = result.metric;
			gr.u_string_value_1 = result.string_value;
			gr.u_normalized_value_1 = result.normalized_value;
			gr.u_nps_value_1 = result.nps_value;
		}
		if(result.metric == 'a1feec4f373ea20009dc65e2b3990ed5'){ //2
			gr.u_question_2 = result.metric;
			gr.u_string_value_2 = result.string_value;
			gr.u_normalized_value_2 = result.normalized_value;
			gr.u_nps_value_2 = result.nps_value;
		}
		if(result.metric == 'e1feec4f373ea20009dc65e2b3990ed5'){ //3
			gr.u_question_3 = result.metric;
			gr.u_string_value_3 = result.string_value;
			gr.u_normalized_value_3 = result.normalized_value;
			gr.u_nps_value_3 = result.nps_value;
		}
		if(result.metric == '25feec4f373ea20009dc65e2b3990ed5'){ //4
			gr.u_question_4 = result.metric;
			gr.u_string_value_4 = result.string_value;
			gr.u_normalized_value_4 = result.normalized_value;
			gr.u_nps_value_4 = result.nps_value;
		}			
		if(result.metric == '65feec4f373ea20009dc65e2b3990ed5'){ //5
			gr.u_question_5 = result.metric;
			gr.u_string_value_5 = result.string_value;
			gr.u_normalized_value_5 = result.normalized_value;
			gr.u_nps_value_5 = result.nps_value;
		}
	}	
	
	if (gr.isNewRecord()){
		gr.insert();
	}
	else {
		gr.update();
	}

})(current, previous);

View solution in original post

Community Alums · ‎11-12-2018

The sys_id's I used in my conditions will not apply to yours. You will be having a different sys_id. Find that and apply it in your script.

Mahidhar Thumma · ‎11-08-2020

Hi Rajinis,

I tried creating the same in my instance but its not working for me. I have changed metric sysids and tried creating but its not copying string values and questions to the custom table.

Can you please help me with this?

Regards,

Mahidhar

Community Alums · ‎11-09-2020

Can you post your business rule here?

Mahidhar Thumma · ‎11-10-2020

(function executeRule(current, previous /*null when async*/) {

var gr = new GlideRecord('u_puzzleresult');

gr.addQuery('u_survey_instance', current.sys_id);

gr.query();

if (!gr.next()){

gs.log('-----------test-----' + gr.u_survey_instance.getDisplayValue());

gr.initialize();

}

gr.u_survey_instance = current.sys_id;

gr.u_metric_type = current.metric_type;

gr.u_survey_assigned_to = current.user;

// gr.u_incident_number = current.trigger_id;

// gr.u_incident_assigned_to = current.related_id_1;

// gr.u_incident_assignment_group = current.related_id_2;

if (gr.state == 'complete'){

gs.log('---------------complete');

var result = new GlideRecord('asmt_metric_result');

result.addQuery('instance', gr.u_survey_instance);

result.query();

while(result.next()){

if(result.metric == '955de43adb102010870f9444ca961975'){

gr.u_question_1 = result.metric;

gr.u_string_value_1 = result.string_value;

gr.u_normalized_value_1 = result.normalized_value;

}

if(response.metric == '155de43adb102010870f9444ca96197c'){

gr.u_question_2 = response.metric;

gr.u_string_value_2 = response.string_value;

gr.u_normalized_value_2 = response.normalized_value;

}
}

}

if (gr.isNewRecord()){

gr.insert();

}

else {

gr.update();

}

})(current, previous);

Here puzzleresult is my custom table and i have changed the metric sysids as well. through the survey i am trying to get only 2 values.

Thanks,

Mahi

Community Alums · ‎11-11-2020

Your if condition had "response" some places. Can you update your script with this?

(function executeRule(current, previous /*null when async*/) {
var gr = new GlideRecord('u_puzzleresult');
gr.addQuery('u_survey_instance', current.sys_id);
gr.query();
if (!gr.next()){
gs.log('-----------test-----' + gr.u_survey_instance.getDisplayValue());
gr.initialize();
}

gr.u_survey_instance = current.sys_id;
gr.u_metric_type = current.metric_type;
gr.u_survey_assigned_to = current.user;
// gr.u_incident_number = current.trigger_id;
// gr.u_incident_assigned_to = current.related_id_1;
// gr.u_incident_assignment_group = current.related_id_2;

if (gr.state == 'complete'){
gs.log('---------------complete');
var result = new GlideRecord('asmt_metric_result');
result.addQuery('instance', gr.u_survey_instance);
result.query();
while(result.next()){
if(result.metric == '955de43adb102010870f9444ca961975'){
gr.u_question_1 = result.metric;
gr.u_string_value_1 = result.string_value;
gr.u_normalized_value_1 = result.normalized_value;
}

if(result.metric == '155de43adb102010870f9444ca96197c'){
gr.u_question_2 = result.metric;
gr.u_string_value_2 = result.string_value;
gr.u_normalized_value_2 = result.normalized_value;
}
}
}

if (gr.isNewRecord()){
gr.insert();
}
else {
gr.update();
}

})(current, previous);