Email Script to include Variables, Variable Sets, and their values

[MyriahM]

Hello,
Our current script [shown below], created by our implementation partners, is pulling the sys_id instead of the display name for the reference fields within the variables [Approver, Requested for] in our email script. We are reaching out to see if someone might have a fix for this. Also, on a side note, we are looking to include variable sets as well. Any help and/or guidance would be much appreciated.

Script:

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

    var html = '<h2>Summary of Requested Item</h2>';

    var reqItem = new GlideRecord("sc_req_item");

    reqItem.addQuery("sys_id", current.sysapproval);

    reqItem.query();

    if (reqItem.next()) {

        html += '<h3>Request Details:</h3>';

        html += '<table border="1" cellpadding="5" style="border-collapse: collapse;">';

        html += '<tr><td><strong>Request Item:</strong></td><td>' + reqItem.number + '</td></tr>';

        html += '<tr><td><strong>Requested for:</strong></td><td>' + reqItem.requested_for.getDisplayValue() + '</td></tr>';

        html += '<tr><td><strong>Item:</strong></td><td>' + reqItem.cat_item.getDisplayValue() + '</td></tr>';

        html += '<tr><td><strong>Quantity:</strong></td><td>' + reqItem.quantity + '</td></tr>';

        html += '</table>';

        html += '<h3>Variables:</h3>';

        html += '<table border="1" cellpadding="1" style="border-collapse: collapse;">';

        var variables = new GlideRecord("sc_item_option_mtom");

        variables.addQuery("request_item", reqItem.sys_id);

        variables.orderBy("sc_item_option.item_option_new.order");

        variables.query();

        while (variables.next()) {

            var itemOption = variables.sc_item_option.item_option_new;

            var label = itemOption.getDisplayValue();

            var value = variables.sc_item_option.value;

            // Handle reference fields - FIX APPLIED HERE

            if (itemOption.type == 'reference' && value) {

                var refTable = itemOption.reference;

                if (refTable) {

                    var refGR = new GlideRecord(refTable);

                    if (refGR.get(value)) {

                        value = refGR.getDisplayValue(); // Always get the display value

                    }

                }

            }

            // Handle checkbox display

            if (itemOption.type == 'checkbox') {

                value = (value == 'true') ? 'Yes' : 'No';

            }

            if (value && value.trim() !== '') {

                html += '<tr><td><strong>' + label + ':</strong></td><td>' + value + '</td></tr>';

            }

        }

        html += '</table>';

    }

    template.print(html);

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

[Chaitanya ILCR]

Hi @MyriahM ,

try this for dispalyaValue instead of sysid

(function runMailScript(current, template, email, email_action, event) {
    var html = '<h2>Summary of Requested Item</h2>';

    var reqItem = new GlideRecord("sc_req_item");
    reqItem.addQuery("sys_id", current.sysapproval);
    reqItem.query();

    if (reqItem.next()) {
        html += '<h3>Request Details:</h3>';
        html += '<table border="1" cellpadding="5" style="border-collapse: collapse;">';
        html += '<tr><td><strong>Request Item:</strong></td><td>' + reqItem.number + '</td></tr>';
        html += '<tr><td><strong>Requested for:</strong></td><td>' + reqItem.requested_for.getDisplayValue() + '</td></tr>';
        html += '<tr><td><strong>Item:</strong></td><td>' + reqItem.cat_item.getDisplayValue() + '</td></tr>';
        html += '<tr><td><strong>Quantity:</strong></td><td>' + reqItem.quantity + '</td></tr>';
        html += '</table>';

        html += '<h3>Variables:</h3>';
        html += '<table border="1" cellpadding="1" style="border-collapse: collapse;">';

        var variables = new GlideRecord("sc_item_option_mtom");
        variables.addQuery("request_item", reqItem.sys_id);
        variables.orderBy("sc_item_option.item_option_new.order");
        variables.query();

        while (variables.next()) {
            var itemOption = variables.sc_item_option.item_option_new;
            var label = itemOption.getDisplayValue();
            var value = variables.sc_item_option.value;

            // Handle reference fields - FIX APPLIED HERE
            if ((itemOption.type == 'reference' || itemOption.type == '8') && value) {
                var refTable = itemOption.reference;
                if (refTable) {
                    var refGR = new GlideRecord(refTable);
                    if (refGR.get(value)) {
                        value = refGR.getDisplayValue(); // Always get the display value
                    }
                }
            }

            // Handle checkbox display
            if (itemOption.type == 'checkbox') {
                value = (value == 'true') ? 'Yes' : 'No';
            }

            if (value && value.trim() !== '') {
                html += '<tr><td><strong>' + label + ':</strong></td><td>' + value + '</td></tr>';
            }
        }

        html += '</table>';
    }

    template.print(html);
})(current, template, email, email_action, event);(function runMailScript(current, template, email, email_action, event) {
    var html = '<h2>Summary of Requested Item</h2>';

    var reqItem = new GlideRecord("sc_req_item");
    reqItem.addQuery("sys_id", current.sysapproval);
    reqItem.query();

    if (reqItem.next()) {
        html += '<h3>Request Details:</h3>';
        html += '<table border="1" cellpadding="5" style="border-collapse: collapse;">';
        html += '<tr><td><strong>Request Item:</strong></td><td>' + reqItem.number + '</td></tr>';
        html += '<tr><td><strong>Requested for:</strong></td><td>' + reqItem.requested_for.getDisplayValue() + '</td></tr>';
        html += '<tr><td><strong>Item:</strong></td><td>' + reqItem.cat_item.getDisplayValue() + '</td></tr>';
        html += '<tr><td><strong>Quantity:</strong></td><td>' + reqItem.quantity + '</td></tr>';
        html += '</table>';

        html += '<h3>Variables:</h3>';
        html += '<table border="1" cellpadding="1" style="border-collapse: collapse;">';

        var variables = new GlideRecord("sc_item_option_mtom");
        variables.addQuery("request_item", reqItem.sys_id);
        variables.orderBy("sc_item_option.item_option_new.order");
        variables.query();

        while (variables.next()) {
            var itemOption = variables.sc_item_option.item_option_new;
            var label = itemOption.getDisplayValue();
            var value = variables.sc_item_option.value;

            // Handle reference fields - FIX APPLIED HERE
            if ((itemOption.type == 'reference' || itemOption.type == '8') && value) {
                var refTable = itemOption.reference;
                if (refTable) {
                    var refGR = new GlideRecord(refTable);
                    if (refGR.get(value)) {
                        value = refGR.getDisplayValue(); // Always get the display value
                    }
                }
            }

            // Handle checkbox display
            if (itemOption.type == 'checkbox') {
                value = (value == 'true') ? 'Yes' : 'No';
            }

            if (value && value.trim() !== '') {
                html += '<tr><td><strong>' + label + ':</strong></td><td>' + value + '</td></tr>';
            }
        }

        html += '</table>';
    }

    template.print(html);
})(current, template, email, email_action, event);

for Variable sets you can refer below

https://www.servicenow.com/community/developer-articles/multi-row-variable-set-in-notifications-via-...

Please mark my answer as helpful/correct if it resolves your query.

Regards,
Chaitanya

[MyriahM]

Hello Chaitanya,
Thank you for the suggestions on the script. What you provided fixed the Approver field within the variables [shown below], but the Requested for still displays the sys_id. Any additional thoughts? 

[Chaitanya ILCR · Accepted solution]

Hi @MyriahM ,

(function runMailScript(current, template, email, email_action, event) {
    var html = '<h2>Summary of Requested Item</h2>';

    var reqItem = new GlideRecord("sc_req_item");
    reqItem.addQuery("sys_id", current.sysapproval);
    reqItem.query();

    if (reqItem.next()) {
        html += '<h3>Request Details:</h3>';
        html += '<table border="1" cellpadding="5" style="border-collapse: collapse;">';
        html += '<tr><td><strong>Request Item:</strong></td><td>' + reqItem.number + '</td></tr>';
        html += '<tr><td><strong>Requested for:</strong></td><td>' + reqItem.requested_for.getDisplayValue() + '</td></tr>';
        html += '<tr><td><strong>Item:</strong></td><td>' + reqItem.cat_item.getDisplayValue() + '</td></tr>';
        html += '<tr><td><strong>Quantity:</strong></td><td>' + reqItem.quantity + '</td></tr>';
        html += '</table>';

        html += '<h3>Variables:</h3>';
        html += '<table border="1" cellpadding="1" style="border-collapse: collapse;">';

        var variables = new GlideRecord("sc_item_option_mtom");
        variables.addQuery("request_item", reqItem.sys_id);
        variables.orderBy("sc_item_option.item_option_new.order");
        variables.query();

        while (variables.next()) {
            var itemOption = variables.sc_item_option.item_option_new;
            var label = itemOption.getDisplayValue();
            var value = variables.sc_item_option.value;

            // Handle reference fields - FIX APPLIED HERE
            if ((itemOption.type == 'reference' || itemOption.type == '8') && value) {
                var refTable = itemOption.reference;
                if (refTable) {
                    var refGR = new GlideRecord(refTable);
                    if (refGR.get(value)) {
                        value = refGR.getDisplayValue(); // Always get the display value
                    }
                }
            }

            // Handle checkbox display
            if (itemOption.type == 'checkbox') {
                value = (value == 'true') ? 'Yes' : 'No';
            }

            if (value && value.trim() !== '') {
                html += '<tr><td><strong>' + label + ':</strong></td><td>' + value + '</td></tr>';
            }
        }

        html += '</table>';
    }

    template.print(html);
})(current, template, email, email_action, event);
(function runMailScript(current, template, email, email_action, event) {
    var html = '<h2>Summary of Requested Item</h2>';

    var reqItem = new GlideRecord("sc_req_item");
    reqItem.addQuery("sys_id", current.sysapproval);
    reqItem.query();

    if (reqItem.next()) {
        html += '<h3>Request Details:</h3>';
        html += '<table border="1" cellpadding="5" style="border-collapse: collapse;">';
        html += '<tr><td><strong>Request Item:</strong></td><td>' + reqItem.number + '</td></tr>';
        html += '<tr><td><strong>Requested for:</strong></td><td>' + reqItem.requested_for.getDisplayValue() + '</td></tr>';
        html += '<tr><td><strong>Item:</strong></td><td>' + reqItem.cat_item.getDisplayValue() + '</td></tr>';
        html += '<tr><td><strong>Quantity:</strong></td><td>' + reqItem.quantity + '</td></tr>';
        html += '</table>';

        html += '<h3>Variables:</h3>';
        html += '<table border="1" cellpadding="1" style="border-collapse: collapse;">';

        var variables = new GlideRecord("sc_item_option_mtom");
        variables.addQuery("request_item", reqItem.sys_id);
        variables.orderBy("sc_item_option.item_option_new.order");
        variables.query();

        while (variables.next()) {
            var itemOption = variables.sc_item_option.item_option_new;
            var label = itemOption.getDisplayValue();
            var value = variables.sc_item_option.value;
            var itemOptionType = itemOption.type;
            // Handle reference fields - FIX APPLIED HERE
            if ((itemOptionType == '31' || itemOptionType == '8') && value) {
                var refTable = itemOption.reference;
                if (refTable) {
                    var refGR = new GlideRecord(refTable);
                    if (refGR.get(value)) {
                        value = refGR.getDisplayValue(); // Always get the display value
                    }
                }
            }

            // Handle checkbox display
            if (itemOption.type == 'checkbox') {
                value = (value == 'true') ? 'Yes' : 'No';
            }

            if (value && value.trim() !== '') {
                html += '<tr><td><strong>' + label + ':</strong></td><td>' + value + '</td></tr>';
            }
        }

        html += '</table>';
    }

    template.print(html);
})(current, template, email, email_action, event);

try this

Please mark my answer as helpful/correct if it resolves your query.

Regards,
Chaitanya

[MyriahM]

Hi @Chaitanya ILCR 
The script above worked in bringing in the displayName instead of the sys_id for both the Approver and Requested for. As I am slightly newer to scripting and JavaScript, could you please explain to me what you adjusted and how it fixed the issue?