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).
