ARTICLE: Approval Reminder Email using Scheduled Job and Notification Email Script

[RiteshSwarnakar]

Requirement:

1. Send Approval Reminder email to Approver(s) for which the approval is pending for more than 7 days based on every Request or RITM.
2. All Approver(s) for each REQ or RITM should be in "TO:" and Requested by and Opened by should be in "CC:" in the Reminder email
3. Approval Reminder email will be triggered on Monday and Thursday every week.

Solution:

Step 1: Create an event : System Policy -> Events -> Registry -> New                                                              

Step 2: Create Scheduled Job to trigger email on every Monday and Thursday:                         

var gdt = new GlideDateTime();
var day = gdt.getDayOfWeekLocalTime();
if (day == 1 || day == 4) {             // check if Monday or Thursday
var ga = new GlideAggregate('sysapproval_approver');
ga.addEncodedQuery('state=requested^sysapprovalSTARTSWITHRITM^ORsysapprovalSTARTSWITHREQ^sys_created_on<=javascript&colon;gs.daysAgoEnd(7)');
ga.groupBy('sysapproval');         // to get unique REQ or RITM records only
ga.query();
while (ga.next()) {
    email_to = [];                          // this will contain all approver(s) related to each record
    var gr = new GlideRecord("sysapproval_approver");
    gr.addEncodedQuery('state=requested^sysapproval=' + ga.sysapproval);
    gr.query();
    while (gr.next()) {
        email_to.push(gr.approver.email);
    }
    gs.eventQueue('approval.schedule.reminder', gr, email_to);
}}

Step 3: Create Notification Email Script :

(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,/* Optional EmailOutbound */email, /* Optional GlideRecord */ email_action, /* Optional GlideRecord */event) {
	
         email.addAddress('cc', current.sysapproval.requested_for.email); 
        email.addAddress('cc', current.sysapproval.opened_by.email); 
    
template.print('<p>Dear Manager,</p><p>&nbsp;</p><p>You have request&nbsp;<span style="color: #0070c0;"><span class="ng-scope">'+  current.sysapproval.number +'</span> </span>waiting for your approval in ServiceNow which is greater than 1 week old.</p>><p>&nbsp;Go to <a href="https://instance.service-now.com/sp" target="_blank" rel="noopener noreferrer" data-auth="NotApplicable">ServiceNow</a> to see a list of all your outstanding approvals &amp; details .</p>');

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

Step 4: Create Notification: System Notification -> Email -> Notifications

a) When to send: Event is fired and select the event which was created in step 1.

b) Who will receive: Event parm 1 contains recipient should be checked

c) What it will contain: In Message HTML write ${mail_script: <Email Script name created in step 3>}

[David-Ramirez]

Thanks Rithesh, nice article, just one comment/question, on the scheduled job script, when triggering the event with gs.eventQueue(), I think the second parameter (GlideRecord of the event table) should be changed since in your code gr is on the approval table and not on the target task table (REQ or RITM), so the current line: 

 gs.eventQueue('approval.schedule.reminder', gr, email_to);

should  be updated after defining a GlideRecord on the task table and the GlideRecord parameter, be replaced with something like:

var taskGR = new GlideRecord('task');
if(taskGR.get(ga.getValue('sysapproval'))){ 
    gs.eventQueue('approval.schedule.reminder', taskGR, email_to);
}

Thanks,

David

[kushwan_2042]

Thanks Ritesh.

[VaranAwesomenow]

I believe with updates to ServiceNow platform and workflow data fabric below would be the approach.

If you want to achieve it using group approval, you can consider leveraging SLAs on sysapproval_group table

If you want to achieve at a single RITM level then you can use a flow that triggers on RITM record.

If you want to apply this email reminder on RITM approvals in general then go with flow with trigger as schedule and table and query sysapproval_approver records then write notification logic accordingly. Only downside of flow + schedule is it wont take holiday calendar into consideration, which is available using SLAs.