My requirement was very specific to write a reusable/generic Notification Email Script that can print/return the MRVS in Tabular/Grid format so that when a Request Submitter or Approver receives an Email Notification, he/she can see what they submitted or what to approve. Also, this needs to be easily managed by my Production Support team for any existing or future Catalog Item where there is an MRVS, with the help of a Configuration Change.
So let start with, What is the Multi-Row Variable Set?
Multi-Row Variable Set (MRVS) is a Variable Set of Type Multi-Row. It captures the variable data in a grid/tabular layout.
The article by Brad Tilton describes MRVS pretty well [Multi-row variable set]. Also, you can check ServiceNow docs for details of MRVS.
Now, To get this done. I made the following changes—
Step 1: Add a new List Control in table: Catalog Item/Maintain Item (sc_cat_item)
Application Scope: Global Table: sc_cat_item Type: List Column Label: Multi-row variable set in Email
Column Name: u_multi_row_variable_set_in_email Reference: Variable Set Reference qual condition: Type - is - Multi Row
and bring this to the Catalog Item form for later Configuration Change. (Note: you can select the MRVS letter in this control)
Step 2: A script include. This will help in formatting the MRVS and return that in HTML tabular format.
Name: mrvsFormatter Application: Global Accessible from: All application scope
var mrvsFormatter = Class.create();
mrvsFormatter.prototype = Object.extendsObject(AbstractAjaxProcessor, {
/*
Call this function like this--
var objFncs = new mrvsFormatter();
gs.print(objFncs.getMRVSFormated('7b750d0b1b232814be65c955624bcb40')); //ritm --> sys_id
*/
getMRVSFormated: function(i_ritmSysId) {
var ritmSysId = i_ritmSysId,
mrvsSysIDes = '',
mrvsTable = '';
var grRITM = new GlideRecord("sc_req_item");
if (grRITM.get(ritmSysId)) {
mrvsSysIDes = grRITM.cat_item.u_multi_row_variable_set_in_email;
if (mrvsSysIDes != '') {
var grVarSet = new GlideRecord("item_option_new_set");
grVarSet.addQuery("sys_idIN" + mrvsSysIDes);
grVarSet.query();
while (grVarSet.next()) {
var mrvsSysId = grVarSet.sys_id;
var mrvsTitle = grVarSet.title;
var mrvsInternalName = grVarSet.internal_name;
mrvsTable = mrvsTable + "\n" + this.getFormatedTable(mrvsSysId, mrvsInternalName, mrvsTitle, i_ritmSysId);
}
}
}
return mrvsTable;
},
/*
Call this function like this--
var objFncs = new mrvsFormatter();
gs.print(objFncs.getFormatedTable('11e0039bdbc87700bc1a72fc0f9619bf', 'grp_owner_domain', '7b750d0b1b232814be65c955624bcb40'));
*/
getFormatedTable: function(i_mrvsSysId, i_mrvsName, i_mrvsTitle, i_ritmSysId) {
var mrvsSysId = i_mrvsSysId,
ritmSysId = i_ritmSysId,
mrvsName = i_mrvsName,
mrvsTitle = i_mrvsTitle,
col = '',
colHeader = '';
Table = '<table style="width: 100%; border-collapse: collapse; border: 0.0pt; float: left; font-family: arial, sans-serif; font-size: 12px; line-height: 25px; color: #55565A; padding-left: 4px; padding-right: 4px; padding-top: 2px; padding-bottom: 2px; text-align: justify; vertical-align: top;" cellspacing="0" cellpadding="2">',
TH = [],
TR = '',
rowCount = 0,
totalColumn = 0;
var grMultiRow = new GlideRecord('item_option_new');
grMultiRow.addQuery('variable_set=' + mrvsSysId + '^active=true');
grMultiRow.orderBy('order');
grMultiRow.query();
while (grMultiRow.next()) {
var variableName = grMultiRow.question_text.toString();
if (col == '')
col = variableName;
else
col = col + ',' + variableName;
}
TH = col.split(',');
totalColumn = TH.length;
colHeader = colHeader + '<tr style="height: 25px; text-align: left;">';
for (var h = 0; h < totalColumn; h++) {
colHeader = colHeader + '<td style="font-weight:bold; border-collapse: collapse; border-style: solid; border: solid #AEAAAA 1.0pt;">' + TH[h].toString() + '</td>';
}
colHeader = colHeader + '</tr>';
var now_GR = new GlideRecord('sc_req_item');
if (now_GR.get(ritmSysId)) {
var mrvs = now_GR.variables[mrvsName]; //.grp_owner_domain;
//gs.print(mrvs);
rowCount = mrvs.getRowCount();
for (var i = 0; i < rowCount; i++) {
TR = TR + '<tr style="text-align: left; height: 25px;">';
var row = mrvs.getRow(i);
var cells = row.getCells();
for (var k = 0; k < cells.length; k++) {
var cell = cells[k];
//gs.info(cell.getLabel() + ":" + cell.getCellDisplayValue())
var colVal = cell.getCellDisplayValue();
TR = TR + '<td style="border-collapse: collapse; border-style: solid; border: solid #AEAAAA 1.0pt;">' + colVal + '</td>';
}
TR = TR + '</tr>';
}
}
if (rowCount > 0) {
Table = Table + '<tr><td colspan=' + totalColumn + ' style="font-weight:bold; border: 0px; text-decoration: underline;">\n\n' + mrvsTitle + '\n</td></tr>';
Table = Table + colHeader;
Table = Table + TR;
Table = Table + '</table>';
} else
Table = ''; //If there is no data in mrvs don't print
return Table;
},
type: 'mrvsFormatter'
});Step 3: A Notification Email Scripts. this can be inserted in the Notification and ensures that the MRVS is printed on the notification email.
Name: mrvsEmailScript
(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,
/* Optional EmailOutbound */
email, /* Optional GlideRecord */ email_action,
/* Optional GlideRecord */
event) {
var scCatItemSysId = '', mrvsSysIDs = '', ritmSysId = '', mrvsTable = '';
scCatItemSysId = current.cat_item.sys_id;
mrvsSysIDs = current.cat_item.u_multi_row_variable_set_in_email;
ritmSysId = current.sys_id;
if (mrvsSysIDs != '') {
var objFncs = new global.mrvsFormatter();
mrvsTable = objFncs.getMRVSFormated(ritmSysId); //'7b750d0b1b232814be65c955624bcb40'));
if (mrvsTable != '') {
template.print(mrvsTable);
}
}
})(current, template, email, email_action, event);Now we are all set to use this in the Notification.
Step 4: If you don't have a Catalog Item with MRVS. Create it and then select your MRVS (in my case Internal Name: please_link_laptop_or_computer_to_user) in the "Include in". My MRVS looks as below-
Step 5: A Notifications (System Notifications -> Email -> Notifications). (I create a custom but you can use any existing Notification).
Name: Request Item Status Change Table: sc_req_item Send When: Record inserted or updated Updated: Checked Condition: State – Changes Who will receive -> Users/Groups in fields -> Request.Opened by What it will contain -> Subject: Number: ${number} Message HTML: ${mail_script:mrvsEmailScript}
Step 6: Now let's go to the Catalog Item form and select the MRVS (please_link_laptop_or_computer_to_user) in the LIST (ref: Step 1).
Step 7: Submit a Request and copy the RITM number. Go to the Emails table and search your email and click on "Preview Email"
Here you go!!!
Note: If you don't want a reusable code, only the function getFormatedTable of script include should be suffecient.
Hi Satyenragupta,
the script is helpful for me , i got one issue that it is populating the values of those variables set but it is not printing the variable labels , please find the screenshot.
