How to loop through variables in a variable set for Email Script

[Matt Cordero1]

I am trying to read in values from a Service Catalog request and will email the general request information when the request is complete.  Part of the request is using a Variable Set with 5 fields (1 plain text, 4 reference).

Here is the code I have so far. I just can't figure out how to read in the values from the Variable Set (looping through the result set itself).  I can read in values from the Variable pool just fine.

var gr = new GlideRecord("sc_req_item");
gr.addQuery("sys_id", current.sys_id);
gr.query();
while(gr.next()) {
var requestedBy = gr.variable_pool.requested_by.getDisplayValue();
var whoIsThisFor = gr.variable_pool.who_is_this_for_ref.getDisplayValue();
var forSelect = gr.variable_pool.requesting_for_select.getDisplayValue();
var contactNumber = gr.variable_pool.contact_number.getDisplayValue();
var reqHTML = gr.variable_pool.image;
var blacklineReconciliation = gr.variable_pool.blackline_reconciliation_reassignment_request;

template.print("<b>Requested By:</b> " + requestedBy + "\n");
if (forSelect == 'Someone Else')
template.print("<b>Requested For:</b> " + whoIsThisFor + "\n");

template.print("<br /><b>Contact Number:</b> " + contactNumber + "\n");
template.print("<br /><br /><b>Blackline Reconciliation Reassignment:</b>\n");

if(reqHTML)
template.print("<br /><b>Additional Comments/Images:</b>" + reqHTML);

template.print("<hr />");
}

[Matt Cordero1 · Accepted solution]

Here is the code block that I used to be able to loop through a Variable Set:

blackline_reconciliation_reassignment_request is the internal name of the Variable Set.  For the acct/group name, I am able to extract the sting entered easily.  For the other 4 reference fields, I am given a sys_id, so I need to reference back to the sys_user table to get the user's name.

//Blackline Reconciliation Reassignment information
template.print("<br /><br /><b>Blackline Reconciliation Reassignment:</b>\n");
template.print("<table width='100%' border='1'>");
template.print("<tr style='background-color:lightgrey'>");
template.print("<th>");
template.print("Reconciliation<br/>(Acct Number/Group Name)");
template.print("</th>");
template.print("<th>");
template.print("Old Preparer");
template.print("</th>");
template.print("<th>");
template.print("Old Approver");
template.print("</th>");
template.print("<th>");
template.print("New Preparer");
template.print("</th>");
template.print("<th>");
template.print("New Approver");
template.print("</th>");
template.print("</tr>");

var brrr = gr.variable_pool.blackline_reconciliation_reassignment_request;
var rowCount = brrr.getRowCount();
for (var i = 0; i < rowCount; i++) {
var row = brrr.getRow(i);

//Acct Number/Group Name
var groupName = row.reconciliation_acct_number_group_name;

//Old Preparer (Reference field)
var oldPreparer = "";
var oldPreparerGR = new GlideRecord("sys_user");
oldPreparerGR.addQuery("sys_id", row.old_preparer);
oldPreparerGR.setLimit(1);
oldPreparerGR.query();
if(oldPreparerGR.next())
{
oldPreparer = oldPreparerGR.name;
}

//Old Approver (Reference field)
var oldApprover = "";
var oldApproverGR = new GlideRecord("sys_user");
oldApproverGR.addQuery("sys_id", row.old_approver);
oldApproverGR.setLimit(1);
oldApproverGR.query();
if(oldApproverGR.next())
{
oldApprover = oldApproverGR.name;
}

//New Preparer (Reference field)
var newPreparer = "";
var newPreparerGR = new GlideRecord("sys_user");
newPreparerGR.addQuery("sys_id", row.new_preparer);
newPreparerGR.setLimit(1);
newPreparerGR.query();
if(newPreparerGR.next())
{
newPreparer = newPreparerGR.name;
}

//New Approver (Reference field)
var newApprover = "";
var newApproverGR = new GlideRecord("sys_user");
newApproverGR.addQuery("sys_id", row.new_approver);
newApproverGR.setLimit(1);
newApproverGR.query();
if(newApproverGR.next())
{
newApprover = newApproverGR.name;
}

//Row/Column block for dynamic table rendering
template.print("<tr>");
template.print("<td>");
template.print(groupName);
template.print("</td>");
template.print("<td>");
template.print(oldPreparer);
template.print("</td>");
template.print("<td>");
template.print(oldApprover);
template.print("</td>");
template.print("<td>");
template.print(newPreparer);
template.print("</td>");
template.print("<td>");
template.print(newApprover);
template.print("</td>");
template.print("</tr>");
}

template.print("</table>");

Rendering comes up very quickly and previews well in an email template.  If there are any improvements that can be made, please reply here.  If you were able to use this code or find it useful, please mark the post helpful. 😃