Email Script - Getting Variable Set Row Values from RITM into Approval Notification

[Chris Dea1]

Hi there,

I hope someone can help advise and provide example script.  I used the Email Script from Ankur on this post to create a new Email Script to get variables from a RITM into an Approval Notification.  It works great except there is one case where it's not working, the script does not pick up the values from a variable set from the RITM....

On the catalog item, there is a variable named "is_this_request_for_multiple_users".  If the user selects "Yes" to set the variable as true, then the user is able to add one or more users to specify details for each user such as the affected_user, request, type, security_group, email, etc.   Screenshot below showing the catalog item in our the service portal:

And here's a screenshot of the multiple_user_info variable set which is of type Multi Row  This variable set is what the user completes if is_this_request_for_multiple_users == true...

Basically, I need to include any row values added into the multiple_user_info variable set and print them into the approval notification using the Email Script. 

Below is the current script I am using.  Can anyone help me out to provide a script to get the row values from the variable set?  I would greatly appreciate any help =).

(function runMailScript(current, template, email, email_action, event) {

	// Add your code here

	template.print('Summary of requested item: <br/>');

	var ritm = new GlideRecord('sc_req_item');
	ritm.addQuery('sys_id', current.sysapproval);
	ritm.query();
	if(ritm.next()){
		var variables = ritm.variables.getElements(); 
		for (var i=0;i<variables.length;i++) { 
			var question = variables[i].getQuestion();
			var label = question.getLabel();
			var value = question.getDisplayValue();
			if(label != ''){
				template.space(4);
				template.print('  ' + label + " --> " + value + "<br/>");
			}
		} 
	}

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

Thanks,
Chris

[Mark Manders]

Check the 'create a summary' part of this post from Mark Roethof.

[Deepak Shaerma · Accepted solution]

Hi @Chris Dea1 
try using 

 template.print('Summary of requested item: <br>');

 var ritm = new GlideRecord('sc_req_item');
 ritm.addQuery('sys_id', current.sysapproval);
 ritm.query();
 if (ritm.next()) {
     var variables = ritm.variables.getElements();
     for (var i = 0; i < variables.length; i++) {
         var question = variables[i].getQuestion();
         var label = question.getLabel();
         var value;

         // Handle special cases, assuming multiple_user_info is your target complex variable
         if (question.getName() === 'multiple_user_info') {
             // Assuming this variable contains a JSON string for simplicity
             try {
                 var parsedData = JSON.parse(question.getValue());
                 parsedData.forEach(function(userDetail) {
                     template.space(4);
                     // Assuming userDetail contains properties like name, request, etc.
                     template.print('User: ' + userDetail.name + " | Request: " + userDetail.request + "<br>");
                     // Add more fields from userDetail as necessary
                 });
             } catch (e) {
                 template.print('Error parsing multiple user info: ' + e.message + "<br>");
             }
         } else {
             value = question.getDisplayValue();
             if (label != '') {
                 template.space(4);
                 template.print('  ' + label + " --> " + value + "<br>");
             }
         }
     }
 }

Note ; ServiceNow does not directly support extracting “row” information from a variable set for ‘Repeat Row’ type variables in an out-of-the-box manner for Email Scripts
Please Mark this Helpful and Accepted Solution. If this Helps you to understand. This will help both the community and me..
- Keep Learning ‌‌
Thanks & Regards 
Deepak Sharma 

[Chris Dea1]

Deepak, thanks for the reply! Your solution got me 90% of the way to what I needed.  For some reason, the if condition below did not work out for me.  (the "question.getName" seemed to return variable value instead of label (i.e. "multiple_user.info").

if (question.getName() === 'multiple_user_info') {

Also, I updated the va parsedData  line below:

var parsedData = JSON.parse(question.getValue());

I am sharing my updated script below, in case it's helpful to others out there.  You'll see the main changes mentioned that I needed to make it work on lines 21 and 31.  I feel pretty good about being able to fetch values from Multi Row Variable Sets now :)....

(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
          /* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
          /* Optional GlideRecord */ event) {

          // Add your code here

    template.print('Summary of requested item: <br>');

    var ritm = new GlideRecord('sc_req_item');
    ritm.addQuery('sys_id', current.sysapproval);
    ritm.query();
    if (ritm.next()) {
        var variables = ritm.variables.getElements();

        for (var i = 0; i < variables.length; i++) {
            var question = variables[i].getQuestion();
            var label = question.getLabel();
            var value;

            // Handle special cases, assuming multiple_user_info is your target complex variable
            if (label == 'Is this request for multiple users?' && question.getDisplayValue() == 'Yes') {
                value = question.getDisplayValue();
                template.space(4);
                template.print('  ' + label + " --> " + value + "<br>");

                // Assuming this variable contains a JSON string for simplicity
                // Get multiple user info
                try {
                    
                    //Note, the value is a JSON string, so we parse it back into an object to make it easier to work with
                    var obj = JSON.parse(ritm.variables.multiple_user_info); //update with your variable name
                    for(var j=0; j<obj.length; j++) {
                            template.print('<br/><br/>');
                            template.print('<br/>' + 'Affected User: ' + obj[j].affected_user);
                            template.print('<br/>' + 'Request Type: ' + obj[j].request_type);
                            template.print('<br/>' + 'Email: ' + obj[j].email);
                            template.print('<br/>' + 'Phone: ' + obj[j].phone);
                    }
                } catch (e) {
                    //template.print('Error parsing multiple user info: ' + e.message + "<br>");
                    template.print('=============================================' + "<br>");
                }
            } else {
                value = question.getDisplayValue();
                if (label != '') {
                    template.space(4);
                    template.print('  ' + label + " --> " + value + "<br>");
                }
            }
        }
    }
})(current, template, email, email_action, event);

-Chris