Update mail script to include a table

[Brian Lancaster]

I am working on a notification to go out to users who have records in a table.  I am able to make it so they get one email with a listing of all the records they have instead of individual.  I was only able to get it to show like this.

Number: TISU0001, State: Work In Progress, etc.

This is one per line.  How can I change my mail script so that it comes to them in a table format like below.

[Shashikant Yada]

Something like OOB incident_comment mail script:

(function runMailScript(current, template, email, email_action, event) {
template.print('<p><font size="4" color="#999999" face="helvetica"><strong>');
template.print(gs.getMessage('Comments') + ':');
template.print('</strong></font></p>');
template.print('<p><font size="3" color="#808080" face="helvetica">' + gs.getMessage('${comments}') + '</font></p>');
})(current, template, email, email_action, event);

Thanks
Shashikant

Hit Helpful or Correct on the impact of response.

[ccajohnson]

Please let us know how this notification is being triggered as well as how you are gathering the information you want to display in the notification. I believe I have done something similar with Knowledge Articles in that we are sending a digest of articles that are to expire rather than one notification for each that is expiring.

[Brian Lancaster]

It is a scheduled job that with a gs.eventQueue to fire the notification.  Then in my mailscript I am using glide record to query the table.  The scheduled job is also a glide record query but I have it set to keep track of the owner so it only fires once per unique owner.

[ccajohnson · Accepted solution]

This sounds very similar to what I have. My guess is that you want some way of capturing those records, then iterating through them. Here is what I have for KB articles:

function checkRevDate() {
    var aDt = new GlideDateTime();
    aDt.addDays(7);
    var cDate = aDt.toString().split(' ')[0];
    var kb = new GlideRecord('kb_knowledge');
    kb.addQuery('workflow_state', '!=', 'retired');
    kb.addQuery('author', '!=', '');
    kb.addQuery('valid_to', '<=', cDate);
    kb.query();
    kbArray = [];
    while (kb.next()) {
        kbArray.push(kb.author.toString());
    }

    var arrUtil = new global.ArrayUtil();
    var uKbArray = arrUtil.unique(kbArray);
    for (k = 0; k < uKbArray.length; k++) {
        var kno = new GlideRecord('kb_knowledge');
        kno.addQuery('workflow_state', '!=', 'retired');
        kno.addQuery('author', '!=', '');
        kno.addQuery('author', uKbArray[k]);
        kno.addQuery('valid_to', '<=', cDate);
        kno.orderBy('author');
        kno.query();
        var kbID = [];
        kbCount = 0;
        while (kno.next()) {
            kbID.push(kno.sys_id.toString());
            kbCount++;
            if (kbCount == kno.getRowCount()) {
                gs.eventQueue('kb_knowledge.review', kno, uKbArray[k], kbID);
            }
        }
    }
}

This will capture an array of sys_id for each author found. Then, in my notification email script:

(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
        /* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
        /* Optional GlideRecord */ event) {
        template.print("<table>");
        template.print("<tr><th align='left'>Number</th><th align='left'>Short Description</th><th align='left'>Valid to date</th></tr>");
        var kbRec = new GlideRecord('kb_knowledge');
        var sKbRec = event.parm2.split(',');
        var tbl = current.getTableName();
        for (x = 0; x < sKbRec.length; x++) {
            kbRec.get(sKbRec[x]);
            var link = createLinkForObject(tbl,kbRec.sys_id,kbRec.number);
            template.print("<tr><td>" + link + "</td><td>" + kbRec.short_description + "</td><td>" + kbRec.valid_to + "</td></tr>");
        }
        template.print("</table>");
})(current, template, email, email_action, event);

function createLinkForObject(strTableName, strSysID, strLabel) {
    return '<a href="' + gs.getProperty('glide.servlet.uri') + 'nav_to.do?uri=' + gs.generateURL(strTableName, strSysID) + '">' + strLabel + '</a>';
}

This builds a table for each of the given columns needed. I get each of the records based upon the array of sys_id passed as a parameter built from the scheduled job. Then, I put in all of the details from each record I got. As an added feature, I also build a link for each record so the user can click on the link and get into the record.

Feel free to pull from this method and use with your own table (since it appears to be a custom table).

[Brian Lancaster]

My query once a user has more then one issue listed is putting duplicate values in the array.  I tried it in the background script to see if I can figure it out.  This user has to records in the table but my array is getting 2 matching sys_id from the table instead of two unique values.  Any idea what might be causing this?

var gr = new GlideRecord ('u_issue_tracker');
var issues = [];
gr.addQuery('u_owner', 'd09da0270f2857800c2049bce1050e7d');
gr.addQuery('u_active', true);
gr.query();
while (gr.next()){
  issues.push(gr.sys_id);
} 
gs.log(issues);

Results: 

*** Script: bbab2d9d373a93002e4cde7543990e13,bbab2d9d373a93002e4cde7543990e13