I want to query only records which were created 3, 7, 14 days ago from current time

[Priya Singh 2 2]

Hi,

I need to send reminder to the approvers who did not approve it for last 3 days, 7 days, 14 days since the Policy Exception record got created-

1. I created 3 Events 

2. 3 Notifications 

3.1 Scheduled job (Where time zone is US/Central) with below script - 

var today = new GlideDateTime();

var threeDaysFromNow = new GlideDateTime();

threeDaysFromNow.addDaysLocalTime(3);

var threeDaysFromNowStart = new GlideDateTime(threeDaysFromNow.getDate() + ' 00:00:00');

var threeDaysFromNowEnd = new GlideDateTime(threeDaysFromNow.getDate() + ' 23:59:59');

var sevenDaysFromNow = new GlideDateTime();

sevenDaysFromNow.addDaysLocalTime(7);

var sevenDaysFromNowStart = new GlideDateTime(sevenDaysFromNow.getDate() + ' 00:00:00');

var sevenDaysFromNowEnd = new GlideDateTime(sevenDaysFromNow.getDate() + ' 23:59:59');

var fourteenDaysFromNow = new GlideDateTime();

fourteenDaysFromNow.addDaysLocalTime(14);

var fourteenDaysFromNowStart = new GlideDateTime(fourteenDaysFromNow.getDate() + ' 00:00:00');

var fourteenDaysFromNowEnd = new GlideDateTime(fourteenDaysFromNow.getDate() + ' 23:59:59');

// Query Policy Exception Table for 3 days

var threedaysago = gs.daysAgoStart(3);

var gr = new GlideRecord('sn_compliance_policy_exception');

gr.addQuery('state', '6');

gr.addQuery('sys_created_on', '>=', threeDaysFromNowStart);

gr.addQuery('sys_created_on', '<=', threeDaysFromNowEnd);

gr.query();

while (gr.next()) {

    gs.eventQueue('pe.first.reminder.email', gr, gr.approvers.getDisplayValue());

}

// Query Policy Exception Table for 7 days

var gr1 = new GlideRecord('sn_compliance_policy_exception');

gr1.addQuery('state', '6');

gr1.addQuery('sys_created_on', '>=', sevenDaysFromNowStart);

gr1.addQuery('sys_created_on', '<=', sevenDaysFromNowEnd);

gr1.query();

while (gr1.next()) {

    gs.eventQueue('pe.second.reminder.email', gr1, gr1.approvers.getDisplayValue());

}

// Query Policy Exception Table for 14 days

var gr2 = new GlideRecord('sn_compliance_policy_exception');

gr2.addQuery('state', '6');

gr2.addQuery('sys_created_on', '>=', fourteenDaysFromNowStart);

gr2.addQuery('sys_created_on', '<=', fourteenDaysFromNowEnd);

gr2.query();

while (gr2.next()) {

    gs.eventQueue('pe.third.reminder.email', gr2, gr2.approvers.getDisplayValue());

}

But it is not working as expected I mean it's not querying correct records, please advise on this.

[Mark Manders]

Why not make it easy on yourself and create a flow? On creation of the record you start with a wait condition, after the wait period you check the state, if that's not the correct one, use the 'send notification' action, then another wait, etc. And if on any of those waits it turns out that a reaction did come in, you can just end the flow.

[Priya Singh 2 2]

Thank You for you response!

Could you please help with the flow ?

[Mark Manders]

What help do you need? 

Create a flow with the trigger on creation of your record.

Use the flow logic 'wait for a duration of time' and then do a check on the record you have from the trigger, to see if it has been updated (state still on 6). Then use the 'send notification' action to trigger your notification. If it's not on state 6, you end the flow. After the notification you add the next 'wait a duration of time' logic, etc.