[SalesForce] Workflow Email Alert: Opportunity Close Date moves beyond current quarter

I need to send an email alert whenever an Opportunity Close Date is pushed out past the current quarter. This is the formula I'm trying to use, but the errors suggest that I can't use relative dates in a formula:

AND (
NOT(ISNEW()),
ISCHANGED(CloseDate),
PRIORVALUE(CloseDate) = THIS QUARTER,
CloseDate > THIS QUARTER
)

The error message I'm getting is Syntax error. Found 'THIS'. Any ideas on how to accomplish this without using 'THIS QUARTER'? Thanks!

Best Answer

In a workflow rule, the rule criteria is either 'criteria are met' or 'formula evaluates to true'.

In the former case, the individual expressions you enter are turned into SOQL by SFDC and hence the special date filters are available to you

But, in the second case, and the one you are using, the expression is evaluated like a formula field which does not have special date filters as an option - this is because the expression could be much more complex than something resolvable to a SOQL where clause

To work around this, create a formula field of type Number (1 decimal) on Opportunity.CloseDate_YYYY_Qn like this:

YEAR(CloseDate)  +     
CASE(MONTH(CloseDate),
1,1,
2,1,
3,1,
4,2,
5,2,
6,2,
7,3,
8,3,
9,3,
10,4,
11,4,
4
)/10)

Create another Number formula field on Opportunity called Today_YYYY_Qn__c as:

YEAR(TODAY()) + 
CASE(MONTH(TODAY()),
1,1,
2,1,
3,1,
4,2,
5,2,
6,2,
7,3,
8,3,
9,3,
10,4,
11,4,
4)

Now, your workflow rule can do simple comparison of CloseDate_YYYY_Qn > Today_YYYY_Qn__c as the resulting numbers (e.g. 2015.1 > 2014.4 will sort properly and hence will compare correctly as time goes forward

These formula fields have utility in other use cases and can inspire extension to do other relative date criteria in 'formula evaluates to true' scenarios.

Note that you can't compare strings in formula fields as in 

"2015-Q1" > "2014-Q4"

even though lexicographically they sort properly. The ">" operator isn't available for text operands.

Related Topic