Scheduled Job Script trigger notification 7 days before Due Date

[Tim Whonder]

Hey all,

Our GRC team has created a custom Due Date field on their sn_grc_issue form. They requested that we create notifications that fire at specific intervals.

1. 30 days prior to the Due Date being realized

2. 7 days prior to the Due Date being realized

3. On the date that the Due Date was realized

4. 3 days after the Due Date has passed.

Thanks to the help of some Samaritans on here, I created a business rule for firing on the date that the Due Date was realized:

gs.eventQueueScheduled('sn_grc.issue.duedate.met',current,gs.getUserID(),gs.getUserName(),current.u_due_date);

I'm told that the other 3 will not trigger the event with a business rule and that a scheduled job will be better. What is the best way to do this using a scheduled job?

[Konstantin7]

That is correct, the business rule wouldn't work for this case as something needs to trigger the business rule.

The best way is with a scheduled job. Have the scheduled job run daily at low impact time. Have the script do the following:

• Check if any records are active on sn_grc_issue table. (add any other conditions to narrow down the list, besides the Due Date field).
• Create a loop to go through all the records that meet the initial criteria.
• Within the loop:
• • Set of if...else... conditions checking the number of days difference between current_date and due_date. Start with checking if the difference is 30 days, else if 7 days, else if Today, else if 3 days after the current date.
  • Within each IF statement, trigger the event.

IF you want to reduce the loop:

• have 4 pulls from sn_grc_issue table, each checking for active records and meets the Due Date criteria.
• If found, loop through and trigger event for each record.

[Tim Whonder]

Can you provide an example of what that script would look like?

The field name is: u_start_date.

The event name is: sn_grc.issue.startdate.met

[rajesh9885]

Something similar

Please change as your convenience

var src = new GlideRecord('sn_grc_issue');
src.addQuery("u_start_date","<=","javascript:gs.daysAgoStart(-10)") // expire in 10 days
while(src.next())
{
gs.eventQueue(<name>)
}

[Konstantin7 · Accepted solution]

Assuming that sn_grc_issue.u_start_date is Date/Time [glide_date_time] field, the Scheduled Job script would be like:

//Check for 30 Days in Future:
var in30d = new GlideRecord('sn_grc_issue');
//The u_start_date is between the start and end of date in 30 days
in30d.addQuery('u_start_date','>=','javascript:gs.daysAgoStart(-30)'); //[start]
in30d.addQuery('u_start_date','<=','javascript:gs.daysAgoEnd(-30)'; //[end]
in30d.query();

while(in30d.next()){
	gs.eventQueue('sn_grc.issue.startdate.met',in30d,in30d.[field_name_of_needed_user].sys_id,in30d.[field_name_of_needed_user].name,in30d.u_start_date);
}

//Check for 7 Days in Future:
var in7d = new GlideRecord('sn_grc_issue');
//The u_start_date is between the start and end of date in 7 days
in7d.addQuery('u_start_date','>=','javascript:gs.daysAgoStart(-7)'); //[start]
in7d.addQuery('u_start_date','<=','javascript:gs.daysAgoEnd(-7)'; //[end]
in7d.query();

while(in7d.next()){
	gs.eventQueue('sn_grc.issue.startdate.met',in7d,in7d.[field_name_of_needed_user].sys_id,in7d.[field_name_of_needed_user].name,in7d.u_start_date);
}

//Check for Current Date:
var today = new GlideRecord('sn_grc_issue');
//The u_start_date is between the start and end of date in 7 days
today.addQuery('u_start_date','>=','javascript:gs.daysAgoStart()'); //[start]
today.addQuery('u_start_date','<=','javascript:gs.daysAgoEnd()'; //[end]
today.query();

while(today.next()){
	gs.eventQueue('sn_grc.issue.startdate.met',today,today.[field_name_of_needed_user].sys_id,today.[field_name_of_needed_user].name,today.u_start_date);
}

//Check for 3 Days Ago
var m3d = new GlideRecord('sn_grc_issue');
//The u_start_date is between the start and end of date in 7 days
m3d.addQuery('u_start_date','>=','javascript:gs.daysAgoStart(3)'); //[start]
m3d.addQuery('u_start_date','<=','javascript:gs.daysAgoEnd(3)'; //[end]
m3d.query();

while(m3d.next()){
	gs.eventQueue('sn_grc.issue.startdate.met',m3d,m3d.[field_name_of_needed_user].sys_id,m3d.[field_name_of_needed_user].name,m3d.u_start_date);
}