Dynamically Sending Multi-Row Variable Set RITM Data via Email

Mallidi Suma · ‎08-28-2023

Hello Community,

Have any of you ever encountered a situation where you need to send a collection of information organized in a multi-row variable via email? I recently tackled this challenge and wanted to share my approach.

Typically, when we discuss sending variable information, we often mention using the GlideappVariablePoolQuestionSet() API. However, this approach falls short when it comes to extracting multi-row variable set information in a clear and understandable tabular format.

As a solution, I turned to the conventional GlideRecord API to retrieve the values. Below, you can find a snippet of the email script code I used for this purpose. If you've faced a similar situation or have any insights to share, I'd greatly appreciate it.

Mail Script Code:-

(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,

/* Optional EmailOutbound */

email, /* Optional GlideRecord */ email_action,

/* Optional GlideRecord */

event) {

// Add your code here

// This mail script is used to automatically pull Multi Row variable set values in a tabular format..

var mrvsSysId;

var appro_rec = new GlideRecord('sysapproval_approver');

appro_rec.addQuery('sys_id', current.sys_id);

appro_rec.query();

if (appro_rec.next()) {

//var Question_values = ['Access required?', 'Provide details of access required'];

//var Name_values = ['access_required', 'provide_details_of_access_required'];

var ritmGR = new GlideRecord('sc_req_item');

ritmGR.addQuery('sys_id', appro_rec.sysapproval);

ritmGR.query();

if (ritmGR.next()) {

var cat_var_m2m_rec = new GlideRecord('io_set_item'); // Catalog Variable set Relationship Table Glide Record

cat_var_m2m_rec.addQuery('sc_cat_item', ritmGR.cat_item);

cat_var_m2m_rec.orderBy('order');

cat_var_m2m_rec.query();

while (cat_var_m2m_rec.next()) { // May return multiple Multi - Row variable sets and this loop will be used to print those many tables.

mrvsSysId = cat_var_m2m_rec.variable_set; // Variable set

//gs.print(mrvsSysIDes);

var question_arr = [];

var keys_rows = [];

var catVarSet = new GlideRecord("item_option_new_set"); // Variable Set Table GlideRecord

catVarSet.addQuery('sys_id', mrvsSysId);

catVarSet.addQuery('type', 'one_to_many');// Checking whether the variable set is a multi - Row variable set or not. If no then breaks the loop if yes, values will be printed in a tabular format.

catVarSet.query();

if (catVarSet.next()) {

// var mrvsSysId = grVarSet.sys_id;

var mrvsTitle = catVarSet.title;

var mrvsInternalName = catVarSet.internal_name.toString();

var catVarSetVaribles = new GlideRecord('item_option_new'); // Glide Record to fetch the variables of the Variable Set

catVarSetVaribles.addQuery('variable_set', mrvsSysId);

catVarSetVaribles.orderBy('order');

catVarSetVaribles.query();

while (catVarSetVaribles.next()) {

question_arr.push(catVarSetVaribles.question_text.toString());

keys_rows.push(catVarSetVaribles.name.toString());

}

//ns_power_bi_sales

var mrvs = ritmGR.variables[mrvsInternalName]; // This will give the multi row variable set values

var mrvs_parse = JSON.parse(mrvs);

var j = 0;

var rowCount = mrvs.getRowCount();

if (rowCount >= 1) {

template.print('<p>' + mrvsTitle + '</p>');

template.print("<table border =1>");

template.print("<tr>");

for (j = 0; j < question_arr.length; j++) {

template.print('<th style="color: #000000;">' + question_arr[j] + '</th>'); // This will give us the table Header, Multi Row Variable set Variable (Questions)

}

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

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

template.print("<tr>");

for (var m = 0; m < keys_rows.length; m++) {

var array_values = mrvs_parse[i][keys_rows[m]] || ' ';

// template.print("<td>" + mrvs_parse[i][keys_rows[0]] + "</td>");

template.print('<td style="color: #008fd0;">' + array_values + '</td>'); // This loop will give us the values entered by the user in MRVS.

}

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

}

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

}

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

Output Format:-

Variable Set Display Name

Variable 1 Display name	Variable 2 Display Name	……..	Variable N Display Name
Value 1	Value 1	——	Value N
Value 2	Value 2	——	Value N
Value 3	Value 3	——	Value N

Mark this article as helpful, if it helps!!

Vinay70 · ‎02-29-2024

// Fetching flat variables
var item = new GlideRecord('sc_req_item');
item.addQuery('sys_id', current.document_id);
item.query();
if (item.next()) {
    var set = new GlideappVariablePoolQuestionSet(); // fetching variables from catalog items;
    set.setRequestID(item.sys_id);
    set.load(); // temporary storing catalog item Variables;
    var flatVariables = set.getFlatQuestions(); // fetching variables Labels;

    // Sort flat variables by order
    var sortedFlatVariables = sortVariables(flatVariables);

    var varLabel, varValue;
    template.print("Request Details:    ");
    template.print("<br>");
    for (var i = 0; i < sortedFlatVariables.length; i++) {
        varLabel = sortedFlatVariables[i].getLabel();
        varValue = sortedFlatVariables[i].getDisplayValue();
        template.print(varLabel + " : " + varValue + "<br>");
    }
}

// Function to sort variables by order
function sortVariables(variables) {
    var sortedVariables = [];
    for (var i = 0; i < variables.size(); i++) {
        sortedVariables[variables.get(i).getOrder()] = variables.get(i);
    }
    return sortedVariables.filter(function (el) {
        return el != null;
    });
}

// Fetching and printing multi-row variable sets
var ritmGR = new GlideRecord('sc_req_item');
ritmGR.addQuery('sys_id', current.document_id);
ritmGR.query();
if (ritmGR.next()) {
    var cat_var_m2m_rec = new GlideRecord('io_set_item'); // Catalog Variable set Relationship Table Glide Record
    cat_var_m2m_rec.addQuery('sc_cat_item', ritmGR.cat_item);
    cat_var_m2m_rec.orderBy('order');
    cat_var_m2m_rec.query();
    while (cat_var_m2m_rec.next()) { // May return multiple Multi - Row variable sets and this loop will be used to print those many tables.
        var mrvsSysId = cat_var_m2m_rec.variable_set; // Variable set
        var question_arr = [];
        var keys_rows = [];
        var catVarSet = new GlideRecord("item_option_new_set"); // Variable Set Table GlideRecord
        catVarSet.addQuery('sys_id', mrvsSysId);
        catVarSet.addQuery('type', 'one_to_many'); // Checking whether the variable set is a multi - Row variable set or not. If no then breaks the loop if yes, values will be printed in a tabular format.
        catVarSet.query();
        if (catVarSet.next()) {
            var mrvsTitle = catVarSet.title;
            var mrvsInternalName = catVarSet.internal_name.toString();
            var catVarSetVaribles = new GlideRecord('item_option_new'); // Glide Record to fetch the variables of the Variable Set
            catVarSetVaribles.addQuery('variable_set', mrvsSysId);
            catVarSetVaribles.orderBy('order');
            catVarSetVaribles.query();
            while (catVarSetVaribles.next()) {
                question_arr.push(catVarSetVaribles.question_text.toString());
                keys_rows.push(catVarSetVaribles.name.toString());
            }
            var mrvs = ritmGR.variables[mrvsInternalName]; // This will give the multi row variable set values
            var mrvs_parse = JSON.parse(mrvs);
            var j = 0;
            var rowCount = mrvs.getRowCount();

            if (rowCount >= 1) {
                template.print('<p>' + mrvsTitle + '</p>');
                template.print("<table border =1>");
                template.print("<tr>");
                for (j = 0; j < question_arr.length; j++) {
                    template.print('<th style="color: #000000;">' + question_arr[j] + '</th>'); // This will give us the table Header, Multi Row Variable set Variable (Questions) 
                }
                template.print("</tr>");
                for (var i = 0; i < mrvs_parse.length; i++) {
                    template.print("<tr>");
                    for (var m = 0; m < keys_rows.length; m++) {
                        var array_values = mrvs_parse[i][keys_rows[m]] || ' ';
                        template.print('<td style="color: #008fd0;">' + array_values + '</td>'); // This loop will give us the values entered by the user in MRVS.
                    }
                    template.print("</tr>");
                }
                template.print("</table>");
            }
        }
    }
}

Email Script