How to send a email based change record state updated

[Pavan Kumar28]

Hi all,

I am trying to send an email to the change requester, but I am missing some logic. My requirement is to trigger the email automatically on a weekly basis. For example, if the change is approved on December 20th (Friday), the email should be sent to the requester on December 27th (next Friday). The email should calculate 7 days from the state change (eg: from "Scheduled for Approval" to "In Progress") and not from other field updates (eg: work notes, additional comments, etc.).
My current code is sending emails for other field updates as well. Can someone please help me correct this?

My code:

var grChange = new GlideRecord('change_request');

   //grChange.addEncodedQuery('state=-4^sys_updated_on>=javascript:gs.beginningOfToday()');

grChange.addEncodedQuery('state=-4^sys_updated_onRELATIVEGT@dayofweek@ago@7');

    grChange.query();

    while (grChange.next()) {

        gs.log('Change moved from "Scheduled for Approval" to "In Progress": ' + grChange.number);

        gs.eventQueue('Weekly.CR.CTask.Update', grChange);

    }

Regards,
Pavan.

[shloke04]

Hi @Pavan Kumar28 ,

Write a after update BR on Change Request Table and Use the Conditions as mentioned below:

BR Details:

When: After Update

Condition: State Changes From "Scheduled for Approval" AND State Changes TO "In Progress"

Script:

For reference I have attached a screenshot as well where instead of Authorize and Assess replace it with the state you want this BR to trigger.

(function executeRule(current, previous /*null when async*/ ) {

    // Add your code here
    
    var approvalTimeStamp = new GlideDateTime(current.sys_updated_on);
    approvalTimeStamp.addDaysLocalTime(7);
    gs.eventQueueScheduled('Your Event Name here', current, current.requested_by, '', approvalTimeStamp);

})(current, previous);

2. Once the BR is created, please ensure to have the Notification is setup as well which will trigger based on the event name mentioned in the Business rule.

3. Within the Notification make sure to have Parm1 check box marked as True within the Recipient tab as shown below for reference for this notification to work. 

[Nilesh Wahule]

Hi @Pavan Kumar28 ,

If you wanted to send notification only once means simple record based notification is enough, but as you want to send on a weekly basis, you should know when was the state was changed (  from "Scheduled for Approval" to "In Progress" ). I don't think its possible to track the update on the field OOTB without audit history. 

So here is my solution,

1. Create a custom field which is hidden on the form but active to store the timestamp for storing the next notification date  e.g. Change Notification Date

2. Create a business rule on change request to trigger first notification and update the Change Notification Date as below

3. Create a Scheduled Job to fire the event and update the next notification date in the field. 

var grChange = new GlideRecord('change_request');
grChange.addEncodedQuery('u_notifcation_dateONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()^ORDERBYDESCsys_updated_on');
 grChange.query();
 while (grChange.next()) {
        gs.eventQueue('Weekly.CR.CTask.Update', grChange);
        var sevenDaysAhead = new GlideDateTime().addDays(7);
	    grChange.u_notifcation_date = sevenDaysAhead;
        grChange.update();
    }

Please adjust the additional query if you wated to check only the active records etc.

---------------------------------------------------------------------------------------------------

Please mark my answer as helpful/correct if it resolves your query.

Thanks,
Nilesh Wahule

---------------------------------------------------------------------------------------------------

[Ankur Bawiskar]

@Pavan Kumar28 

You can use flow designer for this with no code. Why did you start with scheduled job?

1) trigger flow as per this condition

Field Changes from "Scheduled for Approval" to "In Progress"

2) then use Wait for Duration flow logic and wait for 7 days

3) then use Send Email flow action to send email

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@Pavan Kumar28 

something like this. please enhance with your filter conditions, email body etc

If my response helped please mark it correct and close the thread so that it benefits future readers.