+ Start a Discussion
galkagalka 

Help with Opportunity Validation Rule

Hi everyone,

 

I am having difficulties with creating  validation rules in the Opportunity page.  What I want is to have a validation rule for the CloseDate field depending on the value from the Stage picklist, as follows:

Stage: Presentation - Close date can't be within 6 months of today

 

AND(
OR (
ISPICKVAL(StageName, "Presentation/Demo"),
ISCHANGED( CloseDate )),
CloseDate - TODAY() < 180 )

 

Stage: Quote Delivered - Close date can't be within 4 months of today

 

AND(
OR (
ISPICKVAL(StageName, "Quote Delivered"),
ISCHANGED( CloseDate )),
CloseDate - TODAY() < 120 )

 

Stage: Quote accepted - Close date cannot be within 75 days.

 

AND(
OR (
ISPICKVAL(StageName, "Quote Accepted/Moving to Procurement"),
ISCHANGED( CloseDate )),
CloseDate - TODAY() < 75 )

 

What is happening now is that the first rule (180 days) works seemingly fine.  But for the second and third, if the close date is outside of 120 days but less than 180 days, I get the error that it can't be within 180 days, and the same thing (with corresponding values for days) is happening for the third rule. 

 

What am I doing wrong? 

 

TIA,

 

G

 

Best Answer chosen by Admin (Salesforce Developers) 
Steve MolisSteve Molis

Just in case anyone has a similar VR problem

 

VR1
AND(TEXT(StageName) = "Presentation/Demo", CloseDate - TODAY() < 180)

VR2
AND(TEXT(StageName) = "Quote Delivered", CloseDate - TODAY() < 120)

VR3
AND(TEXT(StageName) = "Quote Accepted/Moving to Procurement",  CloseDate - TODAY() < 75) 

VR4 (optional)
AND(IsClosed = TRUE, CloseDate > TODAY())

 

All Answers

Steve MolisSteve Molis

What if you just used a VR like this?

 

CloseDate - TODAY() < 
CASE(StageName,
"Presentation/Demo" , 180,
"Quote Delivered", 120,
"Quote Accepted/Moving to Procurement", 75,
0)

 

galkagalka

Thanks for your suggestion!  Is there a way to have a different error message for each Stage Name/ number of days with this rule? 

galkagalka

I don't think this works correctly either - it allows me to save the opportunity with the close date that doesn't fit the requirements as specified for each stage...

Steve MolisSteve Molis

Can you post a screenshot of a record that violates the VR but does not trigger it?

Steve MolisSteve Molis

For that you'll need 3 VR's (or 1 per eaqch message)

galkagalka

 

In this scenario, close date should not be within 180 days of today.

Steve MolisSteve Molis

I've tested this on my DE SFDC org and it works, double-check the spelling and punctuation of your VRand the Picklist Values.  They need to match EXACTLY, spelling, punctuation, upper/lower case, blank spaces...  I'll bet the Ponderosa that the values in your VR are not exactly the same as your Opportunity Stages.

 

In your first post you said

 

"AND(
OR (
ISPICKVAL(StageName, "Presentation/Demo"),
ISCHANGED( CloseDate )),
CloseDate - TODAY() < 180 )" 

 

but in your screenshot the Stage is: "Presentation / Demo"  <- that's not the same

galkagalka

You are right, thank you for catching it! 

 

Now, if I want to create three separate VRs for each stage, is this the correct formula:

 

CloseDate - TODAY() <
CASE(StageName,
"Presentation / Demo" , 180,
0)

 

and so on, for each of the stages? 

 

I really appreciate your help, thank you very much! 

Steve MolisSteve Molis

Yeah that's basically it 

 

CloseDate - TODAY() < CASE(StageName, "The Picklist Value" , NN, 0)

 

 

 

PS.  You owe me a beer!

galkagalka

Where do I send it?  : )

galkagalka

Well, I just ran into a problem.  When I change the stage to Closed Won (which does not have a VR associated with it at all), and I want the date to be *before today*,  I get an error message saying "Close date cannot be within 120 days".  I can't tell why it's picking this particular VR/error message and not any other ones. 

 

This is a problem because users may be going in to mark deals as closed after the fact, and we need the ability to enter a close date without any restrictions as long as the stage is "Closed Won". 

 

TIA!

Steve MolisSteve Molis

1. I have a VR that will work for that...

2. It will cost you more beer.

galkagalka

Told you I'm down with beer, tell me where to send it! 

 

 

Steve MolisSteve Molis

Okay, can you go to the Opportunity object and go through ALL the Opportunity VR's you have and find any that have that specific error message? 

 

Do any of the VR's that you wrote reference an Opportunity Stage that have a Status Type = Closed or Won?

galkagalka

Here's the VR that gives that error message:

 

CloseDate - TODAY() <
CASE(StageName,
"Quote Delivered", 120,
0)

 

This is the only VR with that error message, and there are no VRs that reference the Stage Name "Closed Won"

Steve MolisSteve Molis

Okay so does the Stage "Quote Delivered" = Closed/Won on your SFDC org?  Are there any other stages that equal Closed/Won?

galkagalka

No, Quote Delivered and Closed Won are two different values in the Stage picklist.  They aren't equal, and no stage is equal to Closed Won.

 

Before the latest VRs were added last week ( with your help!),  there was no problem of changing the close date to a date prior to today's date when marking the stage as Closed Won.

Steve MolisSteve Molis

That doesn't make sense, there has to be something else in play that is triggering the VR.  Do me a favor and go to your Oppty Stages and take a screenshot of the complete Stage Mapping like this.  Also post a screenshot of the Oppty that triggered the VR error.

 

Steve MolisSteve Molis

Just in case anyone has a similar VR problem

 

VR1
AND(TEXT(StageName) = "Presentation/Demo", CloseDate - TODAY() < 180)

VR2
AND(TEXT(StageName) = "Quote Delivered", CloseDate - TODAY() < 120)

VR3
AND(TEXT(StageName) = "Quote Accepted/Moving to Procurement",  CloseDate - TODAY() < 75) 

VR4 (optional)
AND(IsClosed = TRUE, CloseDate > TODAY())

 

This was selected as the best answer