Multiple emails are being sent via scheduled email actions from Process builder

Question

Per our requirement, we need to send an email alert 7 days prior to a date field on the Case object. In the criteria we have built a formula to check the record type and if that date is being changed (Recordtype && ISCHANGED(Datefiled)). In the scheduled action, we added a condition to send an email alert 7 days prior to the same date field. If there are multiple changes on the date field in a way that it is more than 7 days from now and finally we update that date field to a value within 7 days or exactly 7 days from now, system is sending multiple emails.

Eg: If today's date is 3/4/XX and I updated the date field to 3/13/XX, 3/12/XX and finally to 3/11/XX, I'm getting three emails(As there are 3 changes) instead of one.

Is there any way to make it work as expected?

Answer 1

I'd like to see what native solutions others may offer, but I just created an app that can effectively solve this.

The app is named Unleash Your Formula and basically copies and syncs with a formula field with a non formula field https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3u00000MS8T1EAL.

I see it solving your problem as follows:

1. Create a formula checkbox field to reflect if today is the day to send the email (Datefield - 7 = TODAY(), you may also want to add that the formula is only true after a certain time of the day).
2. Using the app, map this formula field to a regular checkbox field that you'll create for this purpose.
3. Have the process builder be triggered if this new checkbox equals true.

With this approach, making changes before the date the email is actually sent won't trigger duplicate emails.