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

[snehabinani26]

try using this as an encoded query.

"start_dateRELATIVEGT@hour@ahead@1"

1. var gr = new GlideRecord('change_request');  
2. gr.addQuery('active', 'true');  
3. gr.addEncodedQuery(start_dateRELATIVEGT@hour@ahead@1);  
4. gr.query();  
5. while(gr.next()) {  
6. gs.eventQueue("change.due",gr,"","");  
7. }  

Hope this helps you.

[Wayne Richmond]

Edit: Ignore the below reply, it's accepted it now. I'll test this and see how I get on, thanks!

Hi, thanks for the reply. Unfortunately the compiler doesn't like the @ symbol:

"Could not save record because of a compile error: JavaScript parse error at line (3) column (40) problem = illegal character"

[Wayne Richmond]

I still can't get this to work. To test this I have several Change Requests with start times an hour apart, before and after my current time. I then Execute the Scheduled Task and check the Event Log. SnehaBinani's suggestion doesn't seem to trigger any events.

Does anyone else have any suggestions?

Thanks

[amlanpal · Accepted solution]

Hi Wayne,

Could you please modify the script in your Scheduled Jobs as of below and give it a try.

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.query();  

while(gr.next()) {  

gs.eventQueue("change.due", gr, "", "");  

}  

I hope this helps.Please mark correct/helpful based on impact