Send mail when contract is less than X days from expiry

Question

EDIT: I should have mentioned that the main problem is not to make a time dependent workflow rule, the problem is that I want this to work for all existing contracts that most likely will never be updated before a customer actually renews their contract. I.e., I want a workflow rule or process builder process to run on existing objects that will not receive an edit, OR: bulk edit a dummy field for all existing objects with another tool (currently verifying dataloader.io) to ensure that the workflow rule time-dependent action will run for all existing contracts.

EDIT2: The solution was a combination of the answer by gNerb, David Reed, and a separate post from SalesForce forums that gave a suggestion of using dataloader.io. I created a dummy-field that was a checkbox, and used Dataloader.io to export all contracts, then change the value of the checkbox and do an update. This makes all objects update, and therefore SF recognises all records as edited, meaning the workflow rule will fire for all contracts.

---

Goal:
Accounts have Maintenance and Support Contracts that they renew every year. Currently, these are being tracked manually. The goal is to automatically send a mail to the Account Owner as soon as TODAY() becomes < X days before renewal.

Current setup:
I have a process builder that triggers on Contract Create or Edit, and has Recursion checked. This then has a criteria where the following formula should evaluate to TRUE:

AND(ISCHANGED([Contract].Notify_2_Months__c), [Contract].Notify_2_Months__c = TRUE)

The Notify_2_Months__c is a custom checkbox on the Contract layout. I use this criteria to make sure that the checkbox has changed and that my custom field is true, and I do this in order to avoid unnecessarily triggering mails for situations where changes to other fields are made manually, for example changing the amounts or names etc.). This box is checked when a Workflow Rule runs. The workflow rule is set to the following:

• Evaluation Criteria: Evaluate the rule when a record is created, and any time it's edited to subsequently meet criteria
• Rule Criteria: TODAY() < EndDate - 60 (this is to ensure that changes to old contracts or contracts that expire very soon, for example changing of names, changing of values etc., does not trigger a new mail)
• Time-dependent workflow actions: 60 Days Before Contract: Contract End Date
• Field update: my custom field

Problem:
If I manually check this box for a contract, I receive the mail notification from the Process Builder properly. However, if I create a dummy contract with all the necessary fields and let it sit for a day, the checkbox is checked over-night, and the "last modified" section will correctly update to the current date of that contract, but no mail is sent. I find this baffling because SF properly recognises that the record has been changed, but for some reason it doesn't send a mail when the workflow rule is the one checking the box.

Any ideas of what I need to do here? I found a similar question that went the APEX route, but I'd prefer this to be possible without going down that road (i.e., using Workflow Rules and Process Builder). I also thought that this ought to be possible using only workflow rules but I didn't come up with any solution for that just yet. All help is appreciated!

Answer 1

I had a bit of a challenge following your post

From what I understand, you have a workflow that has a time dependent action. This workflow fires when the end date is populated to a value that is 60 days out. That workflow than sets a field to "true". When the field is set to "true" a PB kicks in and fires an email.

To me all of this doesn't seem right.

The usual logic is that you have a check box to say ether or not an email should go out. If that check box is true and some other condition is met (such as the contract status != renewed), then a time dependent workflow fires and when the deadline passes if the criteria is still true, the email goes out. Infact, with this solution you don't even need the PB and it can all be done with 1 single WF which as your question says is what you'd prefer.

This logic would mean that the email is more likely to go out as it's not relying on a change to the record, instead its relying on the record not changing which is far more likely. Users can update any field they want outside of the end date, checkbox or status without impacting the WF as well. The checkbox acts as a way to stop the email in those rare cases where other circumstances pop up.

Answer 2

I think you're trying to work around a couple of problems that aren't really there - extra emails being sent from your Workflow Rule based on irrelevant changes to the record, and needing to manually check the date before a time-based workflow action gets enqueued.

If I understand your use case correctly, you can replace both workflow rules with a single rule. The rule should be set to evaluate when a record is created, with no criteria, and a time-dependent action set to go off 60 Days Before Contract: Contract End Date.

Taking a look at Considerations for Time-Dependent Actions and Time Triggers, we know that Salesforce will handle changes to the Date field for us (i.e., we don't have to set up any edit action on the workflow itself):

If you change a date field that is referenced by an unfired time trigger in a workflow rule that has been evaluated, Salesforce recalculates the unfired time triggers associated with the rule. For example, if a workflow rule is scheduled to alert the opportunity owner 7 days before the opportunity close date and the close date is set to 2/20/2011, Salesforce sends the alert on 2/13/2011. If the close date is updated to 2/10/2011 and the time trigger hasn't fired, Salesforce reschedules the alert for 2/3/2011. If Salesforce recalculates the time triggers to a date in the past, Salesforce triggers the associated actions shortly after you save the record.

And should we add any entry criteria, changes to those fields will be handled for us too:

Time-dependent actions remain in the workflow queue only as long as the workflow rule criteria are still valid. If a record no longer matches the rule criteria, Salesforce removes the time-dependent actions queued for that record.

So if you do want to limit your emails by criteria (such as a Status field being "Open" or something like that), again, you don't need to worry about having your workflow rule run checks again to ensure that the email's going out exactly once and at the right time. The platform machinery is meant to handle that for you.

Hence, you shouldn't need the checkbox field or the confusing extra workflow rule.

Answer 3

I would suggest you to take this route and it should work.

1. Through workflow rule, update the checkbox Notify_2_Months__c = TRUE.

In the field update configuration, be sure the choose Re-evaluate Workflow Rules after Field Change checkbox, otherwise process builder's process might not fire.

2. Then from process builder you could configure time dependent action to send email.