Reminder Emails for Approvals

TK36 · ‎06-08-2018

Hi,

I am using this code to send reminders to Managers where approval has been pending for more than 7 days. It has been created to run as a daily scheduled job.

The script does run OK and sends out the chase email. However I have noticed that it sends the chase every day after the request is 7 days or more old. This would mean if someone is on leave they may get multiple emails for the same approval. IE if the request is raised 6 days before they go on leave and they do not approve, then they are off for 2 weeks, they may get over 10 emails for the same request.

Is it possible to add something into the script that updates the request to show the chase has been sent? So it then waits another 7 days before sending the chase email again?

var ritm = new GlideRecord('sc_req_item');
var encQuery = 'active=true^state=1'; //1 is state pending approval;
ritm.addEncodedQuery(encQuery);
ritm.query();

while (ritm.next()) {

var chkApprvl = new GlideRecord('sysapproval_approver');
chkApprvl.addQuery('document_id', ritm.sys_id);//find the approval related to this item
chkApprvl.addQuery('state', 'requested');//its state should be requested
chkApprvl.addQuery('sysapproval.sys_class_name','sc_req_item');
chkApprvl.addQuery('sys_updated_onRELATIVELE@dayofweek@ago@7'); //this checks if the approval has not be updated for 7 days

chkApprvl.query();

while(chkApprvl.next()) {
gs.eventQueue('sc_req_item.approval.overdue', chkApprvl, chkApprvl.approver, chkApprvl.approver.getUserName()); //this will create the event
ritm.work_notes = 'Reminder email to approver sent after 7 days.';
ritm.update();
}
}

Raju Koyagura · ‎06-08-2018

Did you try using daysAgo() method

Ex: replace daysAgoStart with daysAgo(number of day)

var gr = new GlideRecord('sysapproval_approver');
gr.addQuery('state', 'requested');
gr.addQuery('sys_updated_on', '<', gs.daysAgoStart(5));
gr.query();

https://developer.servicenow.com/app.do#!/api_doc?v=jakarta&id=r_GS-daysAgo_N

View solution in original post

Raju Koyagura · ‎06-08-2018

I am bit confused, trying to understand the requirement. First time reminder notice triggered and mail sent after 7 days of approval request, since your schedule job executes everyday the same request reminder again triggers so you don't want to send the same record reminder? If yes, there are two ways you can achieve this, get the approval records using exact 7 days (not greater than that) and trigger every time different records notification triggers. Else create some hidden field on approval table and check the flag once you send notification, in your query get only records which are not sent using the flag.

TK36 · ‎06-08-2018

Hi Rajukoyagura

Thanks for replying. The requirement is to send a reminder every 7 days until the request has been approved.

If I go with first approach (get the approval records using exact 7 days (not greater than that) ), then all those unapproved requests greater than 7 days will get missed. i.e. Once a reminder email has been sent, they can be reminded again automatically.

Will investigate the second approach and let you know if there are any issues.

Regards,

Tanmaya

TK36 · ‎06-08-2018

Hi,

Before looking into adding a new field, I thought of updating the approval in schedule job, so revised code looks as below:

However I noticed that instead of updating the approval, it has created another entry into sysapproval_approver table. Is that how it is supposed to work?

Raju Koyagura · ‎06-08-2018

For your requirement I think hidden field approach works however you need to create two schedule jobs, one should run every day that means it will the flag for the records who meet the condition. The second schedule job should run once in week and which actually send the notifications of only flagged records (already notified before).