Send an email 1 hour prior to a date/time in a record

[Wayne Richmond]

Hi. I am trying to set up an email notification that will be sent to the assignee of a change 1 hour prior to the planned start date. To do this I've attempted to create a Scheduled Job that runs hourly, checks for records where the planned start date is 1 hour away and then creates an Event. The event should then trigger the email notification. However, I'm having trouble figuring out how to work out the '1 hour from now' part. This is my script in the Scheduled Job:

var gr = new GlideRecord('change_request');
gr.addQuery('active', 'true');
gr.addQuery('start_time','>',gs.hoursAgo(-1));
gr.addQuery('start_time','<',gs.hoursAgo(-3));
gr.query();
while(gr.next()) {
gs.eventQueue("change.due");
gr.update();
}

However, this isn't working. The event is running 89 times (that's how many active Changes I have in our dev instance) so clearly the range I've set up isn't working. Also, the email notification isn't firing but I suspect that's something else I need to tweak.

Any help appreciated as always.

Thanks

[Wayne Richmond]

Thank you Amlan, that worked. I had to add an additional line so it only fires the event for changes that are starting within a one hour time period, rather than anything greater than an hour. Like so:

var gr = new GlideRecord('change_request');
gr.addActiveQuery('active', 'true');   //It will fetch all Active Records
gr.addEncodedQuery('start_dateRELATIVEGT@hour@ahead@1');   //It will decide that the Planned Start Date is after One hour
gr.addEncodedQuery('start_dateRELATIVELT@hour@ahead@2');   //It will decide that the Planned Start Date is before Two hours
gr.query();
while(gr.next()) {
gs.eventQueue("change.due", gr, "", "");
}