How to get multi-row variable values to print to email?

[Izzy B1]

Hi all. I'm a relatively new ServiceNow Admin and unfortunately my JavaScript skills are limited. I would be grateful for any help.

I'm trying to print multi-row variable (MRV) values from a record producer to an email that goes out to end users with limited access in our instance, but I can't seem to access the values on the Multi Row Question Answer table [sc_multi_row_question_answer]. I've tried a few things (like trying to have two GlideRecords at the same time) and they haven't worked. I thought about starting over with a completely new script so that I can try to incorporate some code I found on past answers to community questions that seemed similar to mine, but before I did that I wanted to see if more experienced folks knew if it could be saved.

I haven't worked on trying to format the MRVs yet because I can't even retrieve the values. I assume it's possible to do this and I just can't figure it out. Am I on the right track or way off base? How can I get the values from the Multi Row Question Answer table?

(function runMailScript(current, template, email, email_action, event) {
	template.print('<span style="font-size: 12pt; font-family: arial, geneva;">');
	
var my_record = current; //captures current record
var qa = new GlideRecord('question_answer');
    qa.addQuery('table_name', current); //captures table of request
    qa.addQuery('table_sys_id', my_record.sys_id);
	qa.addQuery('question.type', '!=', 11); //removes labels
	qa.addQuery('question.type', '!=', 19); //removes Container Start
	qa.addQuery('question.type', '!=', 20); //removes Container End
	qa.addQuery('question.type', '!=', 12); //removes breaks
	qa.addQuery('sc_multi_row_question_answer.parent_id', my_record.sys_id);
    qa.query();
while(qa.next()) {
   if (qa.question.question_text != '') {  //removing empty question_text to prevent  "undefined"
   var name = qa.question.name;
   var label = qa.question.question_text; //This row replace v.getGlideObject().getQuestion().getLabel()
   var value = my_record.variables[name].getDisplayValue(); //This row Replace  v.getDisplayValue()
   //template.space(6);
   if(value == undefined){  //MRV values
		/* ????? Not working
		name = qa.sc_multi_row_question_answer.variable_set;
		value = my_record.variables[name].getDisplayValue();
		
       */
	   } 
   template.print('<span style="font-size: 12pt; font-family: arial, geneva;">');
   template.print("<B>" + label + ": " + "</B>"  + value + "\n" + "</p>");
   
  }
}}
)(current, template, email, email_action, event);



/* Also did not work
	var gr = new GlideRecord('sc_multi_row_question_answer');
	gr.addQuery('parent_table_name', 'x_tebc2_fac_manage_fac_management_table');
	gr.addQuery('parent_id', my_record.sys_id);
	gr.query();
	   
	while (gr.next()) {
		var mrvLabel = gr.item_option_new.question_text;
		var index = gr.getValue('row_index');
		var mrvValue = gr.getValue('value');
		gs.info(index + ' '+ mrvName + ' ' + mrvValue);
}
*/

[Michael Jones -]

I have a multi-row variable on a record producer for Incident (let's call it multi_row) with 3 fields (variable1,variable2,variable3) and I can access it via a server-side script ( a business rule in my case) using a process like this: 

//You can run this from scripts - background to see how it works

var current = new GlideRecord('incident'); //update with your table
var sys_id = 'cedcace607de9050c5cff1e08c1ed001'; //update with a sys_id of a record in your table
current.get(sys_id);

//Note, the value is a JSON string, so we parse it back into an obect to make it easier to work with

var obj = JSON.parse(current.variables.multi_row); //update with your variable name


for(var i=0; i<obj.length; i++) {

    for(var index in obj[i]) {
    //your code here
    gs.info( index + " : " + obj[i][index] ); //prints the variable names and values for each line
    }

}

 The output looks like this: variable1 is a string, variable2 is reference field and variable3 is a checkbox. 

*** Script: variable1 : value1
*** Script: variable2 : 7781fc6907fd9010c5cff1e08c1ed084
*** Script: variable3 : true
*** Script: variable1 : value2
*** Script: variable2 : 0093af9607e11810c5cff1e08c1ed006
*** Script: variable3 : false
*** Script: variable1 : value3
*** Script: variable2 : 82832f9607e11810c5cff1e08c1ed04f
*** Script: variable3 : true

Clearly if you have reference fields or lookup select boxes you would need to account for that and do additional lookups to get the display values, but this should get you headed in the right direction as far as accessing the values in the multi-row variable set. 

Post back if you need additional pointers! 

Hope this helps!

 If this was helpful or correct, please be kind and click appropriately!

Michael Jones - Proud member of the CloudPires Team!

[Edxavier Robert]

 @Izzy B 

Hi, can you try with something like this in your email script? 

//You can run this from scripts - background to see how it works
(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
          /* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
          /* Optional GlideRecord */ event) {
	var record = new GlideRecord('sc_req_item');
    var sysCurrentRecordSysId = current.sys_id;
    record.addQuery('sys_id', sysCurrentRecordSysId);
    record.query();
	while (record.next()) {
//Note, the value is a JSON string, so we parse it back into an obect to make it easier to work with
var obj = JSON.parse(record.variables.quote_request); //update with your variable name
	for(var i=0; i<obj.length; i++) {
	template.print('<br/>' + 'Hardware: ' + obj[i].hardware_category); //These are my variables and you can copy and paste this line to add more
	template.print('<br/>' + 'Product: ' + obj[i].products);
		
		}
	}
}

)(current, template, email, email_action, event);