1

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!

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
7
  • Thank you very much! This is a great idea, and you're right, it does inspire other ideas for how to use these and similar formula fields. The only thing I had to add is a DATEVALUE function around each of the formula field names since > wouldn't work with text. My final code is AND( NOT(ISNEW()), ISCHANGED(CloseDate), DATEVALUE(CloseDate_YYYY_Qn__c) > DATEVALUE(Today_YYYY_Qn__c) )
    – markrentschler
    Nov 14, 2014 at 22:49
  • crop1645, after activating this workflow, I'm getting an error when I try to move a close date past the current quarter: "Computed an invalid date, such as February 29th on a non-leap year. Please contact your administrator; formula fields can cause such errors." Is this because of the DATEVALUE function? Is there another way to use the ">" operator to compare the two dates?
    – markrentschler
    Nov 17, 2014 at 19:19
  • I don't understand why you are using DATEVALUE on a string that resolves to '2014-Q1'; DATEVALUE("2014-Q1") = #Error!. I noticed you said that '>' did not work on text - it should, it will compare strings lexigraphically. Is your custom formula field of type Text?
    – cropredy
    Nov 17, 2014 at 20:44
  • Yes, the formula fields are both text fields. When I try to use CloseDate_YYYY_Qn__c > Today_YYYY_Qn__c without the DATEVALUE, I get an error: Incorrect parameter type for operator '>'. Expected Number, Date, DateTime, received Text.
    – markrentschler
    Nov 17, 2014 at 22:31
  • well, my original answer was evidence of epistemic arrogance (I've been waiting for a chance to use this expression). You are right, the formula comparison operators don't work with text fields; I've updated my post.
    – cropredy
    Nov 18, 2014 at 3:04
1

Here is another option that won't require any additional "Helper" fields on the opportunity.

WFR to increase Push Counter when close date is set to a future quarter:

Evaluation Criteria: Evaluate the rule when a record is created, and every time it's edited

Field Criteria (formula):

IF (((YEAR( CloseDate )) *10 + (FLOOR(((MONTH(CloseDate)-1)/3)+1))  > (YEAR( PRIORVALUE(CloseDate) )) *10 + (FLOOR(((MONTH(PRIORVALUE(CloseDate))-1)/3)+1)))  && ISCHANGED(CloseDate) ,TRUE, FALSE) = TRUE

Field Update formula for Push Counter:

PRIORVALUE(Push_Counter__c) +1
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .