Help in a formual field for workflow rule criteria

Question

I have a requirement ,where certain conditions should get met for an email notification to go the owner before 100 days of the contract end date. So i have created the a time based workflow rule and a email alert.

Condition is 1:

1)Renewable status is equal to pipeline , next qtr

2)Next Year Status is not equal to renewed or renewed lost.

3)Renewed is true

So while creating a Rule criteria i have used the filter as : This is the Criteria met conditions i have given for 100 days .

((Contract: Contract End DateNOT EQUAL TOnull) AND (Contract: Renewal StatusEQUALS pipeline) ) OR (Contract: Renewal StatusEQUALS next qtr) OR (Contract: Next Year  Status NOT EQUAL TO Renewed) OR (Contract: Next Quarter Renewal StatusNOT EQUAL TO Renewed lost)

Now when the 100 days email is sent to the owner ,their is an update done for the field SRR (picklist).

a)If SRR is YES --- then an email notification is sent for 15 /30/45 days .

b)If SRR is No ---- then stop sending email

c)If SRR --not updated with any value and left blank .

Then i need to send an email notification for 90 days before the contract end date to the owner.

1)Renewable status is equal to pipeline , next qtr --picklist field

2)Next Year Status is not equal to renewed or renewed lost.---picklist field 3)Renewed is true ---formula field make it checked

4)SRR is Blank(not updated) ---picklist field.

NOw if the SRR field is YES and 15 /30 /45 days email to be sent . If SRR is NO or Blank --stop sending the email.

How do i add the 3 & 4 Condition in the formula field.

Formula used is rule criteria:

AND( 
OR( 
ISPICKVAL(Renewable status, 'next qtr'), 
ISPICKVAL(Renewable status, 'pipeline') 
), 
NOT(ISPICKVAL(Next Year Status, 'renewed' )), 
NOT(ISPICKVAL(Next Year Status, 'renewed lost')) 
)

Can u let me know which one is better writing a criteria condition or Formula evaluated true . Any help very much appreciated.

Answer 1

You can do this in one rule with two time triggers.

First, include the criteria w AND logic

ISBLANK(TEXT(SRR))

Then create two time triggers with send email actions on both

TT1: 100 days before contract end date
action: send email

TT2: 90 days before contract end date
action: send email

100 days before end date, SRR is blank and email will send.

If, after the 100 day alert, SRR is updated (by user) to yes / no, then the rule will no longer meet the criteria and the 90 day alert will not send.

If SRR remains blank, the 90 day alert will send.

You will need a separate rule that sends the 15/30/45 day alert if SRR = YES.

Note that you could alternatively build this all in one process using process builder. I still prefer workflow rules as I feel the bulkification on workflows is still more robust than process builder but this will eventually be resolved.