This is quite a lengthy answer but we have done this for a few different tables to give users weekly reminders of various different things like projects, approvals, etc.
What you will need to do to get all of them in one email is fire an event for any user whom has an outstanding approval, in a scheduled script job (sysauto_script).
var users = []; //start with an empty list of approval users
var apps = new GlideRecord('sysapproval_approver');
apps.addQuery('state','requested'); //find requested approvals, you may want to specify which table i.e line below
apps.addQuery('sysapproval.sys_class_name', 'change_request'); //only find change related approvals
apps.query();
while (apps.next());
//now add the outstanding approver to the list
users.push(apps.approver);
}
//now we need to dedupe the list of users
var au = new ArrayUtil();
var noDupesApprovalUsers = au.unique(users);
//now we need to go through this unique list of users and fire an event against the users which in turn fire an email notification which will go and gather all of their outstanding approvals.
for (var i=0; i< noDupesApprovalUsers.length; i++) {
gs.eventQueue('approval.reminder',noDupesApprovalUsers[i],'sys_user');
}
OK so now if you look in event log, you should see some events being fired called approval.reminder which is an event for every user that we have found who has an outstanding approval.
now we need to build a notification and template to find that users outstanding approvals and bunch them into ONE email.
So create a notification and it will be on sys_user table when approval.reminder event is fired:
Subject: Something like you have approvals outstanding....
Now I normally use an email template and script include to store the script and HTML in order to build a table of those approval records.
<mail_script>
var dem = new ApprovalReminderEmail(); //call the script include
template.print(dem.send(current.sys_id)); //call the send function and pass in the users sys_id
</mail_script>
</font>
Within this script include you are going to find the users outstanding approval records via GlideRecord and them add them to a HTML Table
send : function (user){
var outputString = ''; //put some html in here and your table header columns, styles etc.
var approvals = new GlideRecord('sysapproval_approver');
approvals.addQuery('state','requested');
approvals.addQuery('sysapproval.sys_class_name','change_request');
approvals.addQuery('approver',user);
approvals.query();
while (approvals.next()({
// up to you how pretty you want to make your html but I normally build a var called outputString and keep adding to that with my new records i.e.
outputString = outputString +"<tr><a href=" + uri + "sysapproval_approver.do?sys_id="+approvals.sys_id+">"+approvals.sysapproval.getDisplayValue()+ "</a>"+" "+"</td><td width='150px'>" + approvals.sysapproval.short_description + " "+"</td><td width='150px'>" + approvals.sysapproval.start_date + " "+"</td><td>" + approvals.sysapproval.end_date + " "+"</td><td width='100px'>" + approvals.sysapproval.phase.getDisplayValue() + " "+"</td><td width='200px'>"+ approvals.sysapproval.assigned_to.getDisplayValue() + "</td></tr>";
}
//eventually you should end up with some html and a table
then just return all of that and I normally log it whilst building for troubleshooting bad html etc.
| | | gs.log(outputString,'approval reminders'); |
| | | return outputString; |
