[SalesForce] Send mail when contract is less than X days from expiry

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!

Best Answer

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.

Related Topic