Approval Reminders through Scheduled Jobs

[achen]

Hello,

I am trying to create a scheduled job that sends out a notification to approvers who have approvals older than two days.  I am using this script to query and send out the notification but it is not working for some reason.

// Define the GlideDateTime for two days ago
var twoDaysAgo = new GlideDateTime();
twoDaysAgo.addDaysUTC(-2);

// Create a GlideRecord for your approval table (e.g., sysapproval_approver)
var approvalGR = new GlideRecord('sysapproval_approver');

// Add a condition to filter records with the state "requested"
approvalGR.addQuery('state', 'requested');

// Add a condition to filter records older than two days
approvalGR.addQuery('sys_created_on', '<', twoDaysAgo);

// Execute the query
approvalGR.query();

while (approvalGR.next()) {
    // Get the approver's user record
    var approverUser = new GlideUser(approvalGR.approver);

    // Construct the notification message
    var notificationMessage = "Dear " + approverUser.getDisplayValue() + ",\n\n";
    notificationMessage += "This is a reminder that you have pending approval tasks in ServiceNow.\n";
    notificationMessage += "These tasks were created more than two days ago and require your attention.\n\n";
    notificationMessage += "Request: " + approvalGR.short_description + "\n";
    notificationMessage += "Created On: " + approvalGR.sys_created_on.getDisplayValue() + "\n";
    notificationMessage += "Due Date: " + approvalGR.due_date.getDisplayValue() + "\n\n";
    notificationMessage += "Please log in to ServiceNow and review these approval tasks as soon as possible. ";
    notificationMessage += "Your timely action is crucial to ensure the smooth flow of processes.\n\n";
    notificationMessage += "Thank you for your prompt attention to this matter.\n\n";
    notificationMessage += "Best regards,\nYour Organization Name";

    // Send the notification using GlideNotification
    var notification = new GlideNotification();
    notification.setSubject("Approval Reminder");
    notification.setText(notificationMessage);
    notification.setRecipients(approverUser.getID());
    notification.insert();

    // Optionally, you can log this action in the approval record
    approvalGR.comments = 'Reminder notification sent to approver.';
    approvalGR.update();
}

[Tai Vu · Accepted solution]

Hi @achen ,

Let's try this approach.

1. Define an Event Registry in the Approvals [sysapproval_approver] table.

2. Fire the event in the script. 

// Define the GlideDateTime for two days ago
var twoDaysAgo = new GlideDateTime();
twoDaysAgo.addDaysUTC(-2);

// Create a GlideRecord for your approval table (e.g., sysapproval_approver)
var approvalGR = new GlideRecord('sysapproval_approver');

// Add a condition to filter records with the state "requested"
approvalGR.addQuery('state', 'requested');

// Add a condition to filter records older than two days
approvalGR.addQuery('sys_created_on', '<', twoDaysAgo);

// Execute the query
approvalGR.query();

while (approvalGR.next()) {
    gs.eventQueue('<event_name>', approvalGR);

    // Optionally, you can log this action in the approval record
    approvalGR.comments = 'Reminder notification sent to approver.';
    approvalGR.update();
}

3. Create a Notification with this Event is fired, and add the Approver as the Audience.

Let me know if it works for you.

Cheers,

Tai Vu

[Tai Vu · Accepted solution]

Hi @achen ,

Let's try this approach.

1. Define an Event Registry in the Approvals [sysapproval_approver] table.

2. Fire the event in the script. 

// Define the GlideDateTime for two days ago
var twoDaysAgo = new GlideDateTime();
twoDaysAgo.addDaysUTC(-2);

// Create a GlideRecord for your approval table (e.g., sysapproval_approver)
var approvalGR = new GlideRecord('sysapproval_approver');

// Add a condition to filter records with the state "requested"
approvalGR.addQuery('state', 'requested');

// Add a condition to filter records older than two days
approvalGR.addQuery('sys_created_on', '<', twoDaysAgo);

// Execute the query
approvalGR.query();

while (approvalGR.next()) {
    gs.eventQueue('<event_name>', approvalGR);

    // Optionally, you can log this action in the approval record
    approvalGR.comments = 'Reminder notification sent to approver.';
    approvalGR.update();
}

3. Create a Notification with this Event is fired, and add the Approver as the Audience.

Let me know if it works for you.

Cheers,

Tai Vu

[achen]

Hi Tai,

So I created the event registry 

that will run when the scheduled job is triggered-

// Define the GlideDateTime for two days ago

var twoDaysAgo = new GlideDateTime();

twoDaysAgo.addDaysUTC(-2);

// Create a GlideRecord for your approval table (e.g., sysapproval_approver)

var approvalGR = new GlideRecord('sysapproval_approver');

// Add a condition to filter records with the state "requested"

approvalGR.addQuery('state', 'requested');

// Add a condition to filter records older than two days

approvalGR.addQuery('sys_created_on', '<', twoDaysAgo);

// Execute the query

approvalGR.query();

while (approvalGR.next()) {

    gs.eventQueue('<approval.reminders>', approvalGR);

    approvalGR.comments = 'Reminder notification sent to approver.';

    approvalGR.update();

}

 

and should send the approval template that is originally sent but the only email that is being sent out is that the comment was added.  Did I do something wrong?

 

   

[Tai Vu]

Hi @achen 

Make sure the event name is correct.

So you have created an event registry named open.approval.reminders.

The script should be.

// Define the GlideDateTime for two days ago
var twoDaysAgo = new GlideDateTime();
twoDaysAgo.addDaysUTC(-2);

// Create a GlideRecord for your approval table (e.g., sysapproval_approver)
var approvalGR = new GlideRecord('sysapproval_approver');

// Add a condition to filter records with the state "requested"
approvalGR.addQuery('state', 'requested');

// Add a condition to filter records older than two days
approvalGR.addQuery('sys_created_on', '<', twoDaysAgo);

// Execute the query
approvalGR.query();

while (approvalGR.next()) {
    gs.eventQueue('open.approval.reminders', approvalGR);

    // Optionally, you can log this action in the approval record
    approvalGR.comments = 'Reminder notification sent to approver.';
    approvalGR.update();
}

Also make sure the Notification set the correct event.

Work pretty well per my check.

Cheers,

Tai Vu

[achen]

Hello,

so after I updated the event name to open.approval.reminders it is still only updating the comment field.

It looks like the event might not be getting called.

I Updated the notification event as well.

Do you see anything in my code that might be broken?

Thank you,

Alex