Multi-Row Variable Sets: Composing approval and task descriptions

Duodecimal · ‎02-14-2019

ServiceNow provides a lightweight variable formatter in the out-of-the-box notifications for catalog approvals, but it does not appear to handle multi-row variables. Also, we weren't entirely happy with the formatting, so we scripted our own variable presentation used in approval emails (HTML) and for populating plaintext descriptions.

Multi-row variable sets (MRVS) are not cooperating with the custom script either. I was reluctant to spend a lot of time coding a solution since I'm confident ServiceNow will be updating the APIs to handle MRVS correctly, or maybe already have in Madrid? But we got a catalog item requirement that MRVS was perfect for, so those approvals need all that information now.

Brad Tilton provided an overview of the MRVS and its API in these posts:

The scripting API is mostly for editing the MRVS, not so much how it would need to be used to compose an email for any catalog item -- not just a specific one that you know all the names of the variables in advance. In short, only a couple methods available are actually useful for my requirements.

Non-MRVS

Variations of this code are in widespread use. It takes a requested item's variables, gets the question labels and answer display values, and sorts them in the order they appear on the form. I include type in my output object for specific things I do in emails or descriptions. For example, for type 7 questions (Checkbox), I just print the label if the value is "true", and otherwise skip it. For types 21 and 22 (list collector and lookup multiple choice), I print each selection on its own line.

The key api methods are getGlideObject on the variable, and then getQuestion on the returned glide object (which is essentially an item_option_new gliderecord):

function getRITMVariables (ritmGR) {
   var answers = []; 
   for (var v in ritmGR.variables) {
      var vA = ritmGR.variables[v].getGlideObject();
      if (!vA || !vA.getDisplayValue().trim()) {
         continue;
      }
      var vQ = vA.getQuestion();
      answers.push({
         "label" : vQ.label,
         "type"  : vQ.type,
         "order" : vQ.order || 0,
         "value" : vA.getDisplayValue()
      });
      }
   }
   answers.sort(function(a, b){return a.order - b.order});
   return answers;
}

The first "if" is for when the answer was blank, or getGlideObject returned an invalid value, the variable would be skipped (expected behavior until MRVS showed up).

This code returned an array of objects that would be parsed by either a notification email script or a business rule depending on usage - producing HTML output for an approval, or unformatted plaintext in catalog tasks or other descriptions.

MRVS

When the above code runs into a multi-row variable set, getGlideObject returns no value, and it is skipped. Graceful failure state in terms of runtime, but not so great for the content of our approval emails.

The safe assumption was that there would be some way, somehow, to get the data. Querying gliderecords is admin 101. The actual problem to solve is how to format the output in a way that makes it easy to parse. Fortunately, ServiceNow sets it up in a sensible way: the MRVS is an array, with each row being an element of the array. Within each row is a collection of objects that are the name:value pairs for the question variables and their answers.

Brad Tilton's scripting blog post about this works, if you know in advance the variable names you need to get the values of, along with the internal name of the variable set. It's possible to create a notification template tailored to each catalog item using MRVS, but not practical. The script puts out a JSON object with the internal variable names and the values (not display values) of the answers. List collectors and references are sys_ids, for example.

Fortunately ServiceNow provided a way to get at the display values with an update to the VariableUtil script include. Handy - except we need to pass in the sys_id of the variable set. There doesn't appear to be an api for getting that on the MRVS variable itself. The available methods on MRVS are apiHelp, help, getRows, addRow, getQuestionIds, getRowCount, deleteRow, getRow, and getCells, as user hrng found out in their own post.

Of those methods, two will be useful in our custom script: getRowCount, and getQuestionIds.

getRowCount will be a quick check to determine if we are working with an MRVS or with a standard variable. getQuestionIds is how we'll be able to work around us not being able to use getQuestion (since getGlideObject doesn't return anything we can work with).

The structure of our code will basically look something like this:

for (var itemVar in ritmGR.variables) { 
   if (itemVar.getRowCount() > 0) { 
   // do MRVS processing
   var qIDs = itemVar.getQuestionIds();
   // define the answer as an array to hold rows
   // get type, order, and label from item_options_new
   // Re-use that information for each answer found
   // Now loop each row
      // Create an array to hold answer objects on this row
      // Now loop through each column in the row
         // Push output info for the row's array
         // Sort the columns within the row
      // Push the row array into the variable's value array
      // {ush the whole answer into the return array
} else { 
   // This is the regular processing already in the previous section
}
// Sort all the answers by order
// return answer array

The answer array will contain an array for each MRVS, and each element of the MRVS answer array will itself be another array where each element is all the details of the answer in a column of a row in the MRVS. Simple.

Now we're back to the problem of converting sys_ids into display values. In our first draft that never actually made it into testing, we would look up the question in item_option_new using the sys_ids provided by getQuestionIds, find the reference table, or list table, or lookup from table (depending on the type of the variable), do another get, and voila.

But we won't be going through all that nonsense because, as mentioned earlier, ServiceNow updated VariableUtil to handle MRVS.

This use of VariableUtil is described in another thread. Instead of querying the question repeatedly for each row, we should just make a dictionary to hold the information. The getQuestionIds method on the MRVS is handy here. Unfortunately it returns a value I couldn't figure out how to parse correctly, so I had to do something that looks stupid:

var qIDs = itemVar.getQuestionIds().toString().slice(1, -1).split(", ");

The raw value that's returned doesn't work as an array, and even if I send the string value through JSON.parse, it fails because the elements of the array aren't enclosed in quotes. Maybe someone else who isn't confounded by inexperience can help me with the correct way to handle that return value. What I did instead was strip off the enclosing brackets and split it by comma-space to get a usable array.

Once you have that array, just query item_option_new for the variables, and save that info to an object you can refer to later on when you're parsing the return from VariableUtil.getDisplayValue(). Then push that combined information to the row, and then add that row to the array, and then push that array to the answer array, and you're done. With collecting the answers, at least - we still have to convert them to something that's pleasing to read.

Since we'll need to parse this answer array row by row, we'll need to give that parser a heads-up that it's about to be fed another array for the MRVS. We can just be professionals and use typeof, but instead I specify the MRVS type as 99. I'm checking for variable type anyway -- since I handle checkbox and multi-value answers differently (types 7, 21, and 22).

Here's the final version of the code that I polluted our production instance with:

CatalogUtils.getRITMVariables = function(ritmGR) {
   var answers = []; // return object
   // Question dictionary
   // more useful for MRV, so question info won't be queried repeatedly
   // Returns false if the question variable should be skipped
   var qDict = {};
   function setQuestion (qID, isMRV) {
      var ion;
      if (isMRV) {
         ion = new GlideRecord("item_option_new_set");
         if (!ion.get("internal_name", qID)) {
            return false;
         }
      } else {
         ion = new GlideRecord("item_option_new");
         if (!ion.get(qID)) {
            return false;
         }
      }
      var name = isMRV ? ion.internal_name : ion.name;
      qDict[name.toString()] = {
         "order"  : +ion.order || 0,
         "label"  : ion.getDisplayValue(),
         "type"   : isMRV ? 99 : +ion.type
      };
      return true;
   }

   // Step through each variable on the requested item and populate the

   // question dictionary and answer array
   VariableLoop: for (var v in ritmGR.variables) {
      var itemVar = ritmGR.variables[v];
      var rows = itemVar.getRowCount();
      if (rows) {
         // Multi-Row Variable parsing
         if (!setQuestion(v, true)) {
            continue VariableLoop;
         }
         var mrv = {
            "label"  : qDict[v].label,
            "type"   : qDict[v].type,
            "order"  : qDict[v].order,
            "value"  : []
         };
         // Define list of columns within the multi-row variable
         var qIDs = itemVar.getQuestionIds().toString().slice(1, -1).split(", ");
         MRVColumnIDLoop: for (var q = 0; q < qIDs.length; q++) {
            if (!setQuestion(qIDs[q], false)) {
               continue MRVColumnIDLoop;
            }
         }
         // Get display value for answers
         var ions = new GlideRecord("item_option_new_set");
         if (!ions.get("internal_name", v)) {
            continue VariableLoop;
         }
         var vu = new VariableUtil();
         var mrva = JSON.parse(vu.getDisplayValue(ions.sys_id, itemVar));
         RowLoop: for (var r = 0; r < rows; r++) {
            var mrvRow = [];
            ColumnLoop: for (var c in mrva[r]) {
               mrvRow.push({
                  "label"  : qDict[c].label,
                  "type"   : qDict[c].type,
                  "order"  : qDict[c].order,
                  "value"  : mrva[r][c]
               });
            }
            // Sort the columns within the multi-row variable
            mrvRow.sort(function(a, b){return a.order - b.order});
            mrv.value.push(mrvRow);
         }
         answers.push(mrv);
      } else {
         // Regular variable parsing
         var vA = itemVar.getGlideObject();
         if (!vA || !vA.getDisplayValue().trim()) {
            continue VariableLoop;
         }
         var vQ = vA.getQuestion();
         if (!setQuestion(vQ.id)) {
            continue VariableLoop;
         }
         answers.push({
            "label"  : vQ.label,
            "type"   : vQ.type,
            "order"  : vQ.order,
            "value"  : vA.getDisplayValue()
         });
      }
   }
   // Sort by variable order
   answers.sort(function(a, b){return a.order - b.order});
   return answers;
};

Displaying the Answer

Now you need a function or two to parse that answer array into HTML for email (using a notification email script) or another task's description (via a business rule or workflow). It's just an array of objects, so looping through that array and printing the label and value properties is all you need to do. That's what the old code did.

But then you hit the MRVS. How do you display that? My decision was to insert a table for HTML view, and to make an ordinal list to identify the row each answer pertains to for plain text description. Building an html table at runtime felt like 1996 again but if you have a better way I wish you had posted it a few weeks ago so I wouldn't have had to go through all this.

// Returns plaintext description of all RITM variables (for non-HTML description field)
CatalogUtils.getRITMDescription = function(ritmGR) { 
   var desc = req.number ? req.number + "." + ritmGR.number + "\n" : "";
   desc += ritmGR.cat_item.getDisplayValue().toUpperCase() + "\n\n";
   var answers = CatalogUtils.getRITMVariables(ritmGR);

   // Find questions for the given order value, 
   // blank line between each set
   AnsLoop: for (var i = 0; i < answers.length; i++) { 
      var ansType = +answers[i].type;
      // Don't print unchecked checkboxes
      if (ansType == 7 && answers[i].value != "true") continue AnsLoop;
      // Print Answer
      // Don't print "true" value of checked checkboxes (question is answer)
      if (ansType == 7) continue AnsLoop;
      if (ansType == 99) {
         // Multi-Row Variable
         if (answers[i].value.length <= 0) { 
            continue AnsLoop;
         }
         desc += answers[i].label.toUpperCase() + "\n";
         for (var r = 0; r < answers[i].value.length; r++) { 
            for (var c = 0; c < answers[i].value[r].length; c++) { 
               var o = answers[i].value[r][c];
               desc += r + "." + c + " " + o.label.toUpperCase() + ": " + (o.value || "<blank>") + "\n";
            }
         }
         continue AnsLoop;
      }
      // Print Question
      desc += answers[i].label.toUpperCase() + "\n";
      var valArr;
      if (ansType == 21 || ansType == 22) { 
         // Multi-item list; put each member on its own line
         valArr = answers[i].value.toString().split(",");
      } else {
         valArr = [answers[i].value.toString()];
      }
      for (var j = 0; j < valArr.length; j++) { 
         desc += valArr[j].trim() + "\n";
      }
   }
   return desc;
};

// Returns html string for email - variable summary for a given RITM
CatalogUtils.getPrintableRITM = function(ritmGR) {
   if (!ritmGR.hasOwnProperty("variables")) {
      var gr = new GlideRecord("sc_req_item");
      if (!gr.get(ritmGR)) {

         return "Unable to retrieve data";
      }
      ritmGR = gr;
   }
   var answers = CatalogUtils.getRITMVariables(ritmGR);
   var output = "<ul>";
   AnsLoop: for (var i = 0; i < answers.length; i++) {
      var ansType = +answers[i].type;
      if (ansType == 99) {
         // Multi-Row Variable; compose table
         if (answers[i].value.length <= 0) {
            // No rows
            continue AnsLoop;
         }
         output += "<li><i>" + answers[i].label + "</i>";
         var rs = "<tr>", hs = "<th>", cs = "<td>";
         var re = "</tr>", he = "</th>", ce = "</td>";
         output += "<ul><table border='1'>";
         // Headers
         output += rs;
         for (var h = 0; h < answers[i].value[0].length; h++) {
            output += hs + answers[i].value[0][h].label + he;
         }
         output += re;
         // Rows
         for (var r = 0; r < answers[i].value.length; r++) {
            output += rs;
            // Columns
            for (var c = 0; c < answers[i].value[r].length; c++) {
               output += cs;
               var o = answers[i].value[r][c];
               if (o.type == 7 && o.value == "true") {
                  // Checkboxes - only show a value for true; leave blank for false
                  output += "Yes";
               } else if (o.type == 21 || o.type == 22) {
                  // List collector / lookup multiple choice; put on each line
                  var oArr = o.value.split(",");
                  output += "<ul>";
                  for (var a = 0; a < oArr.length; a++) {
                     output += "<li>" + oArr[a];
                  }
                  output = "</ul>";
               } else {
                  output += o.value;
               }
               output += ce;
            }
            output += re;
         }
         output += "</table></ul>";
         continue AnsLoop;
      }
      // For checkboxes, print only the label and skip entirely if the answer is false
      if (ansType == 7 && answers[i].value != "true") {
         continue AnsLoop;
      }
      output += "<li><i>" + answers[i].label + "</i>";
      if (ansType == 7) {
         continue AnsLoop;
      }
      // Show answers with a leading list dot
      var valArr;
      output += "<ul>";
      if (ansType == 21 || ansType == 22) {
         // Multi-item list; put each member on its own line
         valArr = answers[i].value.toString().split(",");
      } else {
         valArr = [answers[i].value.toString()];
      }
      for (var j = 0; j < valArr.length; j++) {
         output += "<li>" + valArr[j].trim() + "</li>";
      }
      output += "</ul></li>";
   }
   output += "</ul>";
   return output;
};

It's a trivial exercise from here to use those functions in one of your custom utility script includes so you can call it from wherever you need to, given the following disclaimer:

Code is provided as-is and the responsibility of your own instance is yours
This code is not endorsed or supported by ServiceNow
Opinions and any other impressions gotten from this post are not meant to reflect on my employer
You may not list your emergency contact at Knowledge as Fred Luddy
ServiceNow may revise these APIs at any time, breaking the undocumented and/or documented functionality presented
Questions, complaints, and sarcastic comments directed at me are to be limited to this forum thread

User672379 · ‎07-23-2019

Hello,

Reading through this and I'm a little confused on where you put this code. I don't see a CatalogUtils OOTB so I'm assuming this is a custom script include, and the code displayed here is the script include code and not necessarily the code being used in mail scripts?

Thank you!

Duodecimal · ‎07-23-2019

Correct, you'd make your own custom script include 'library', which CatalogUtils is the one we made to house these types of functions. The email scripts do invoke a function from CatalogUtils, the one called "getPrintableRITM" in the code example above. The string returned by the function is just used in the mail script's template.print() function.

User672379 · ‎07-23-2019

Thank you so much for replying quickly!

Si Min · ‎08-04-2019

Hi Anthony, I'm working something similar to yours, could you point me out how to get this done?

I'm a newbie on this.

Thanks.

Duodecimal · ‎08-07-2019

Other than a minor error (I refer to a chunk of code I took out with the "req" variable that it looks like I left some of that in), the code above works as-is.

What you need to do is create an email notification script that calls these functions and prints them to your email. Out of the box, there's an email template called "itil.approve.role" on the sysapproval_approver table. You'll make a copy of that template so you can update it with your own custom content, and then have the related approval notification(s) use that new template instead.

Things get a little complex here and depends on your implementation. We have approvals that occur at multiple levels of a service catalog request -- on REQ, RITM, and SCTASK tickets. You may need to write code that feeds the appropriate ticket type to the above functions. This is probably intermediate level administration and I can't really run a javascript tutorial here. But basically --

For a REQ ticket, the mail script ought to call a function that looks up all RITM tickets for that REQ, and uses those RITM records as parameters for the function, concatenating the returned strings together for template.print()
An SCTASK, if configured correctly, will have a subset of the variables on the RITM (that is, only those variables relevant to the group being assigned the catalog task). This is configured in the workflow for the requested item in the Catalog Task activity. Or maybe there's no variables in the SCTask and you need to display everything that's in the request_item reference instead. Either way - the code ought to be able to take any gliderecord that has catalog variables, and print out those variables nicely.

Other than those cases, we'll just do the simplest one that uses a copy of the itil.approve.role Template, that is used by a notification with a condition that the Approval For.Task Type is Requested Item. Set the Weight value on a notification so that if other notifications may qualify for delivery, only the one with the highest weight is actually sent (note: weight 0 notifications are always sent).

You'll need to create a new Notification Email Script (in the System Notification navigator menu). You'll notice at the top there's a blue box that explains the global variables that are available - current, email, etc. The requested item on a sysapproval_approver record is in the "sysapproval" reference.

var ritm = current.sysapproval.getRefRecord();
template.print(CatalogUtils.getPrintableRITM(ritm));

Name the notification script record something you'll remember (getNiceVariables), and save it. Now you can call that script from your email template by name:

${mail_script:getNiceVariables}

That line goes in your email template body wherever you want the list of questions/answers to show up.

[edit] had getRITMDescription in the email script instead of getPrintableRITM

lister · ‎07-16-2020

Hello,

I was wondering if anyone else has started experiencing issues with this after upgrading to New York / Orlando?

Since we've upgraded from Madrid, I've noticed that labels for MRVs are returning as 'undefined'.

After doing some debugging, I've narrowed it down to the following:

var qIDs = itemVar.getQuestionIds().toString().slice(1, -1).split(", ");

returns different results in Madrid and New York / Orlando:

Madrid:
qIDs: df0c4519dbadd410d7aba2b4449619e7,d0ec8d19dbadd410d7aba2b44496198a

New York/Orlando:
qIDs: Ljava.lang.String;@1c95fb

This is because the function getQuestionIds() has changed in New York and it now returns an array.

Unfortunately, I haven't been able to resolve the issue, and wanted to see if others have experienced the same.

Thanks,

L.

Duodecimal · ‎07-16-2020

Excellent, that'll simplify using those return values. I haven't upgraded to New York yet, but when I do I'm pretty sure that line would just be:

var qIDs = itemVar.getQuestionIds();

... as long as it is returning a standard array.

Duodecimal · ‎07-16-2020

Excellent, that'll simplify using those return values. I haven't upgraded to New York yet, but when I do I'm pretty sure that line would just be:

var qIDs = itemVar.getQuestionIds();

... as long as it is returning a standard array.

lister · ‎08-05-2020

You may wanted to skip straight to Paris. The above mentioned change returns 'variable' for the column headers, which isn't ideal, however, better than 'undefined'.

Good news, however, after spinning up a Paris instance and testing the scripts, it looks like it's working as intended again :).

Raf1 · ‎10-15-2020

Hi @Anthony Bongiovanni , this code update works like a charm!

Thanks,

Raf

Roger Poore · ‎01-06-2021

This is a great article but I came up with another solution in case anyone cares. I created a script include named getItemMRVs and call it from an e-mail template. First parameter is the ritm and the second indicates whether or not to return the raw JSON or HTML. That way, it can be used for anything that needs to pull the variables from a request.

Email template:

template.print (getItemMRVs(current.sysapproval, true));

Script include:

function getItemMRVs(ritm, forTemplate) {

	function getMRVs(ritm) {
		var arrayUtil = new ArrayUtil();
		var results = [], mrv_list = [];
		var gr = new GlideRecord('sc_multi_row_question_answer');
		gr.addEncodedQuery('parent_id=' + ritm);
		gr.query();

		while (gr.next()) {
			if (!arrayUtil.contains(mrv_list, gr.variable_set.sys_id)) {
				mrv_list.push(gr.variable_set.sys_id.toString());

				var o = {};
				o.mrv = gr.variable_set.title.toString();
				o.variables = getMRVVariables(ritm, gr.variable_set.sys_id.toString());

				results.push(o);
			}
		}

		return results;
	}

	function getMRVVariables(ritm, mrv) {
		var results = [];
		var gr = new GlideRecord('sc_multi_row_question_answer');
		gr.addEncodedQuery('parent_id=' + ritm + '^variable_set=' + mrv);
		gr.orderBy('row_index');
		gr.query();

		while (gr.next()) {
			// only add the variables that have values
			if (gr.value) {
				var o = {};
				o.question = gr.item_option_new.getDisplayValue();
				o.value = gr.value.getValue();
				o.row_index = gr.row_index.toString();

				results.push(o);        
			}
		}

		return results;
	}
	
	// used for building an e-mail template
	function buildTemplate(json) {
		var HTML = '';

		json.forEach(function(obj) {
			var rowIndex = null;
			HTML += '<div style="padding: 5px; background-color: #004466; color: #fff;">';
			HTML += '<b>' + obj.mrv + '</b>';

			obj.variables.forEach(function(q) {
				if (!rowIndex || rowIndex == q.row_index) {
					HTML += '<div style="padding: 2px; background-color: #fff; color: #666; border-bottom: 1px solid #ddd;">';
					HTML +=  '<b>' + q.question + ':</b> ' + q.value;
					HTML += '</div>';
				}
				else {
					HTML += '<div style="background-color: #fff; border-bottom: 1px solid #ddd;"> </div>';
				}

				rowIndex = q.row_index;
			});

			HTML += '</div><br />';
		});

		return HTML;
	}	
    
	// if this for e-mail, forTemplate should be true
	if (forTemplate) {
		return buildTemplate(getMRVs(ritm));
	}
	else {
		return getMRVs(ritm);		
	}	
}

satyendrakgupta · ‎04-27-2021

Multi-row variable set in Notifications via Notifications Email Script

Smashley · ‎09-20-2021

I need to filter out variables that have Visible in Summary set to false. Where in the script would I add that?

Duodecimal · ‎09-22-2021

I do exactly the same thing but didn't include it in the code since it was a custom field in our case. I'm going to guess that you're using a checkbox in the item_option_new table, in which case you'll need to add a quick test to the getRITMVariables function:

CatalogUtils.getRITMVariables = function(ritmGR) {
   var answers = []; // return object
   // Question dictionary
   // more useful for MRV, so question info won't be queried repeatedly
   // Returns false if the question variable should be skipped
   var qDict = {};
   function setQuestion (qID, isMRV) {
      var ion;
      if (isMRV) {
         ion = new GlideRecord("item_option_new_set");
         if (!ion.get("internal_name", qID)) {
            return false;
         }
      } else {
         ion = new GlideRecord("item_option_new");
         if (!ion.get(qID)) {
            return false;
         }
      }

In that Else where you do the gliderecord lookup in item_option_new, you'll add another if statement. Unless SN has added a built-in field for this, you'll refer to your checkbox here. In our instance, it was a custom checkbox "u_suppress_print":

        } else {
            ion = new GlideRecord("item_option_new");
            if (!ion.get(qID)) {
                return false;
            }
            if (ion.u_suppress_print) {
                return false;
            }

Smashley · ‎09-22-2021

Thank you so much!! I just couldn't seem to stick the if statement in the right spot!