Incrementing number field with formula in workflow

Question

I have the following requirement

1. Send an email when a record is created to the assigned user(field in the record).
2. Starting from the 3rd day from the day the record was created start sending emails to the assigned user for one week.

I was thinking of using workflow to achieve this. The plan(design) was to two workflows to achieve the result.

First workflow is fired when the record is created using ISNEW() rule criteria. This WF will have one email trigger.It will also have time dependent email action(after 3days) of an email trigger and a field update(a hidden number field called timeTriggerCheck incremented by one).

The second WF to have rule criteria of ISCHANGED(timeTriggerCheck) and ISVAL(timeTriggerCheck)<= 9.

However i don't see any way to update the hidden number by one every day via WF formula. Nor do i see any way to check if the value of the number field is less than 10.

Can the requirement be fulfilled using Workflows in the above way. I do feel one workaround would be to keep only the first WF and keep all time based email trigger under it. Something in the following order:

3 days after rule trigger date

4 days after rule trigger date

...

9 days after rule trigger date

However the number of such days might increase and hence it seems quite inappropriate to manually create such records.

Answer 1

This is what I did to get this tested and working:

1. Field Delay__c on account. Default value = 0 you can alter in increment formula

2. Field Send_On__c (dateTime) formula as createddate + Delay__c

3. WFR ON Account, eval criteria Created and subsequently edited, criteria CreatedDate NOT EQUAL (leave third field blank) and Delay__c <= 9 3a. Immediate Actions - WF Field Update to increment Delay formula. In this example if 0 add 3 days, otherwise add 1.

3b Email alert to send email of your choosing 3c Time triggered 0 days from Send_On__c as the trigger 3d - Time triggered actions is the same as exist in the immediate actions

Feel free to rename fields as needed. You could also simply adjust the formula to increment times triggered to add 2 if the value is 0, then 1 otherwise. All up to you.

What this does is:

1. Record Created, immediate action happens, sends email, increments counter. Now Send_On__c = next day + 3 if insert and next day when TBWFR fires.

2. Next Send On day, time triggered fires, increments counter, sends email, and resets itself to the next day to fire

To test:

1. Create record - You will get email
2. Review TBWF queue. - You will see an entry for tomorrow
3. Increment counter manually to 2 to simulate tomorrows run
4. Review TBWF queue. - You will see an entry for 2 days for now

On insert the TBWF Queue should should something similar to:

And the account fields will be

Now, change the Delay to 4 and refresh the TBW Queue logs and it should be

Answer 2

What you're asking for can't be done with a trigger unless you use the trigger to create a recurring Event. Triggers only execute when a record is created or updated. Unless you had a scheduled job that updated the records each day, a trigger wouldn't be guaranteed to fire and execute for you.

What you describe doing from day 3 to day 9 potentially could work and is described in the following kb article Creating an Automatic Birthday Email. However, in that article, more time is allowed between the send and the update of the counter. There may be issues with trying to send the email every 24 hours because of the delay needed between the time the email is sent, the update to the "flag" is made, and the time the rule is evaluated again. It may work fine for you and then again, it may not. You'd simply need to try it.

A more "robust" way to do this would be to create a scheduled batch job that sends the emails from day 3 to day 9. Add the user to a queue of names to be used by the scheduled batch job that sends the same email during the period of days that you want the user to receive it. You could use your checkbox or, even better, a date as a way of selecting them via a query (using the date method would allow for a more selective and efficient query).