Send email 2 months before due date

Cesar Alves1 · ‎09-01-2021

Hi!
I have a problem sending an email when a policy is about to expire. The idea is to notify the person 3 months, 2 months and 1 month before the "valid to" field reaches the scheduled date.

I created an event to trigger the notification and a scheduled job to call the event. But my doubt is in the correct filter to identify only the documents that actually apply the rule above. Could you please tell me if the form I did is correct and how can I get 2 months? I couldn't find this in the predefined filter. Below are details of what I've done

Event

Scheduled Job

Filter (encoded query 3 months)

var gr = new GlideRecord('sn_compliance_policy');
gr.addEncodedQuery('state=published^valid_toONNext quarter@javascript:gs.beginningOfNextQuarter()@javascript:gs.endOfNextQuarter()');
gr.query();

while (gr.next()) {
gs.eventQueue("ebx.policy_review", gr);
}

Ankur Bawiskar · ‎09-01-2021

Hi,

you can use flow designer based approach with no script

Create an email notification for when a policy is due to expire in 1 month

OR

Scheduled job

you can use this script in schedule job to send 90 days, 60 days or 30 days before the date

sendEmail();

function sendEmail(){
	try{
		var gr = new GlideRecord('sn_compliance_policy');
		gr.addQuery('state=published');
		gr.query();
		while(gr.next()){
			var gdt = new GlideDateTime(gr.valid_to);
			var nowTime = new GlideDateTime();

			var duration = GlideDateTime.subtract(gdt, nowTime);
			var days = duration.getDayPart();
			if(days == 30 || days == 60 || days == 90){
				gs.eventQueue("ebx.policy_review", gr);
			}
		}
	}
	catch(ex){
		gs.info('Exception'+ex);
	}
}

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

Voona Rohila · ‎09-01-2021

Hi

Try this which works for all the cases

var gr = new GlideRecord('sn_compliance_policy');
gr.addEncodedQuery('end_dateRELATIVEGT@month@ahead@1');//add additional filters.
gr.query();

while (gr.next())
{
var dm = gs.nowDateTime(); //capture the current system date
var diff = gs.dateDiff(dm,gr.valid_to, true); //calculate the difference
var one_day=86400;
var m = Math.round(diff/one_day);
gs.print(m);
if(m == 60 || m == 90 || m==120)
    gs.eventQueue("ebx.policy_review", gr);

}

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for
Kind Regards,
Rohila V
2022-25 ServiceNow Community MVP