Unable to send a table format data in emails

Kartik Magadum
Kilo Sage

‎05-21-2025 02:50 AM

Dear All,

I am trying to send a table format data in emails, but the strange is i am able to see the table headers/columns when i preview the notification, but unable to send a table in emails. 

 

I created a scheduled job to trigger a notification, and I've included a email script in notification. 

I've attached a code, please check and help.

 

Scheduled job - It's working as expected. 

var assignedArray = [];
var unique = [];
var audit = new GlideRecord('sys_audit');
audit.addEncodedQuery('tablename=sn_compliance_control^fieldname=u_process_responsible^oldvalueISEMPTY');
audit.query();
while (audit.next()) {
    var recordGR = new GlideRecord('sn_compliance_control');
    recordGR.addEncodedQuery('state!=retired');
    recordGR.addQuery('sys_id', audit.documentkey);
    recordGR.orderByDesc('sys_created_on');
    recordGR.query();
    if (recordGR.next()) {
        assignedArray.push(recordGR.getValue('u_process_responsible'));
    }
}
for (i = 0; i < assignedArray.length; i++) {
    if (unique.indexOf(assignedArray[i]) == -1) {
        unique.push(assignedArray[i]);
    }
}
for (i = 0; i < unique.length; i++) {
    gs.eventQueue('sn_compliance', recordGR, unique[i]);
}

 

Mail Script: 

 

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

    var instanceUrl = gs.getProperty('glide.servlet.uri') + "nav_to.do?uri=";

    var emailBody = '<html><body style="font-size:10px; font-family:Verdana;">';
    emailBody += '<h4 style="font-size:12px; font-family:Verdana;">Summary of assigned controls:</h4>';
    emailBody += '<table border="1" style="border-collapse:collapse; width:100%; font-size:8px; font-family:Verdana">';
    emailBody += '<tr style="background-color:#f2f2f2; font-size:8px; font-family:Verdana;">';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Framework Referencer</th>';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Entity</th>';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Control Number</th>';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Control Name</th>';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Process Responsible</th>';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Control Owner</th>';
    emailBody += '</tr>';



    var audit = new GlideRecord('sys_audit');
    audit.addEncodedQuery('tablename=sn_compliance_control^fieldname=u_process_responsible');
    audit.query();
    while (audit.next()) {
        if (!audit.oldvalue.nil() || audit.newvalue.nil()) {
            continue; // Skip if old value is not empty or new value is empty
        }

        var recordGR = new GlideRecord('sn_compliance_control');
        recordGR.addQuery('sys_id', audit.documentkey);
        recordGR.addQuery('u_process_responsible', event.parm1);
        recordGR.orderByDesc('sys_created_on');
        recordGR.query();
        if (recordGR.next()) {

            var linkToControl = '<a href="' + gs.getProperty('glide.servlet.uri') + 'now/risk/portal/record/sn_compliance_control/' + recordGR.sys_id + '">' + recordGR.number + '</a>';
            var frame = recordGR.u_framework_reference;
            if (frame != 'ISMS') //- (IS NOT)Information Security
            {
                gs.info('inside 1st if condition');

                emailBody += '<tr>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.u_framework_reference + '</td>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.profile.applies_to.company.u_companycode + ' ' + recordGR.profile.applies_to.company.name + ' ' + recordGR.profile.u_unit_code + ' ' + recordGR.profile.name + '</td>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + linkToControl + '</td>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.name + '</td>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.u_process_responsible.name + '</td>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.owner.name + '</td>';
                emailBody += '</tr>';


            } else if (frame == 'ISMS') //- Information Security
            {

                emailBody += '<tr>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.u_framework_reference + '</td>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.profile.name + '</td>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + linkToControl + '</td>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.name + '</td>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.u_process_responsible.name + '</td>';
                emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.owner.name + '</td>';
                emailBody += '</tr>';

            }
        }
    }

    emailBody += '</table>';
    emailBody += '</body></html>';
    template.print(emailBody);

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

 

When i preview the notification: 

KartikMagadum_0-1747820822458.png

 

 Thanks.!

Kartik Magadum

0 Helpfuls
  • 349 Views
3 REPLIES 3

ShafrazMubarak
Giga Guru

‎05-21-2025 03:32 AM 

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

    // event.parm1 holds the sys_id of the user (u_process_responsible) for this specific email
    var processResponsibleSysId = event.parm1; 

    var emailBody = '<html><body style="font-size:10px; font-family:Verdana;">';
    emailBody += '<h4 style="font-size:12px; font-family:Verdana;">Summary of assigned controls:</h4>';
    emailBody += '<table border="1" style="border-collapse:collapse; width:100%; font-size:8px; font-family:Verdana">';
    emailBody += '<tr style="background-color:#f2f2f2; font-size:8px; font-family:Verdana;">';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Framework Referencer</th>';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Entity</th>';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Control Number</th>';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Control Name</th>';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Process Responsible</th>';
    emailBody += '<th style="padding:8px; font-size:10px; font-family:Verdana;">Control Owner</th>';
    emailBody += '</tr>';

    // Query directly for controls assigned to the current recipient
    var recordGR = new GlideRecord('sn_compliance_control');
    recordGR.addQuery('u_process_responsible', processResponsibleSysId); // Filter by the user for THIS email
    recordGR.addQuery('state!=retired'); // Add any relevant filters for active controls
    // Add the audit check logic here if you ONLY want to send controls that were *newly* assigned since the last run
    // This part depends heavily on how you define "assigned controls" for the email.
    // If you only want *newly assigned* controls since the last run, you'll need a better way to filter.
    // For simplicity, let's assume you want *all* controls where process_responsible is the recipient.
    recordGR.query();

    // Variable to track if any data rows were added
    var hasData = false;

    while (recordGR.next()) {
        hasData = true; // Indicate that data rows are being added
        var linkToControl = '<a href="' + gs.getProperty('glide.servlet.uri') + 'now/risk/portal/record/sn_compliance_control/' + recordGR.sys_id + '">' + recordGR.number + '</a>';
        var frame = recordGR.u_framework_reference.toString(); // Ensure it's a string for comparison

        emailBody += '<tr>';
        emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.u_framework_reference.getDisplayValue() + '</td>'; // Use getDisplayValue() for reference fields
        
        // Handle entity lookup carefully. profile.applies_to.company might not always be there.
        // Use try-catch or null checks for robustness if these are optional.
        if (frame != 'ISMS') {
            emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">';
            // Added null checks for nested objects
            if (recordGR.profile.nil()) {
                emailBody += 'N/A Profile';
            } else {
                if (!recordGR.profile.applies_to.nil() && !recordGR.profile.applies_to.company.nil()) {
                    emailBody += recordGR.profile.applies_to.company.u_companycode + ' ' + recordGR.profile.applies_to.company.name;
                }
                if (!recordGR.profile.nil()) {
                    emailBody += ' ' + recordGR.profile.u_unit_code + ' ' + recordGR.profile.name;
                }
            }
            emailBody += '</td>';
        } else { // ISMS
            emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">';
            if (!recordGR.profile.nil()) {
                emailBody += recordGR.profile.name;
            } else {
                emailBody += 'N/A Profile';
            }
            emailBody += '</td>';
        }

        emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + linkToControl + '</td>';
        emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.name + '</td>';
        emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.u_process_responsible.getDisplayValue() + '</td>'; // Use getDisplayValue()
        emailBody += '<td style="padding:8px; font-size:8px; font-family:Verdana;">' + recordGR.owner.getDisplayValue() + '</td>'; // Use getDisplayValue()
        emailBody += '</tr>';
    }

    emailBody += '</table>';

    // Optional: Add a message if no controls were found
    if (!hasData) {
        emailBody += '<p style="font-size:10px; font-family:Verdana;">No new assigned controls found for you.</p>';
    }

    emailBody += '</body></html>';
    template.print(emailBody);

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

ShafrazMubarak
Giga Guru
In response to ShafrazMubarak

‎05-21-2025 03:33 AM

When you preview a notification with a mail script, it often executes the mail script's HTML structure (the static <table> and <th> tags) but might not fully simulate the GlideRecord queries and their results in the exact same context as a triggered event. It shows the static HTML you've defined, but when the recordGR.query() inside the mail script found no records (due to the incorrect sys_audit logic or query conditions), it wouldn't populate the <tr> tags.

This revised mail script should correctly populate the table with relevant data for each recipient

0 Helpfuls

Ankur Bawiskar
Tera Patron
Tera Patron

‎05-21-2025 03:51 AM

@Kartik Magadum 

how it looks when email is sent?

If table is there but when the actual email is received in Outlook the table is not seen properly then you can try to use Simple HTML table and don't use much style etc, avoid style tags, close the tags properly

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader
0 Helpfuls