Ask Search:
Bart WardBart Ward 

Validation Rule for Opportunity Stages

Hello again. I'm trying to write a validation rule that will prevent our sales people from moving an opportunity stage backward to "Prospect" after it has advanced past that stage. We have many stages, so the rule is rather lengthy. Please bear with me.

This is what I have so far:

OR(
ISPICKVAL( StageName, "Deferred Opportunity"),true, 
ISPICKVAL( StageName, "Webex Complete"),true,
ISPICKVAL( StageName, "Id. Decision Makers"),true,
ISPICKVAL( StageName, "GM/DP Webex needed"),true,
ISPICKVAL( StageName, "second Webex set"),true,
ISPICKVAL( StageName, "Second WX Complete"),true,
ISPICKVAL( StageName, "Third WX"),true,
ISPICKVAL( StageName, "Proposal/Price Quote"),true,
ISPICKVAL( StageName, "Onsite Visit Requested"),true,
ISPICKVAL( StageName, "Onsite Visit Completed"),true,
ISPICKVAL( StageName, "Negotiation/Review"),true,
ISPICKVAL( StageName, "Pending Installment Approval"),true,
ISPICKVAL( StageName, "Installments Approved"),true,
ISPICKVAL( StageName, "Closed or Won"),true,
ISPICKVAL( StageName, "Closed or Lost"),true,
IF(
ISPICKVAL(StageName, "Prospect"),true,false))

I'm not getting any syntax errors, but it won't let me place the opportunity into any stages other than "Prospect" when I'm testing it. Any stage other than "Prospect" throws the error message. Obviously we want to advance the stages forward, just not back to "Prospect".

As always, your help is greatly appreciated.
 


Best Answer chosen by Moderator (salesforce.com) 
Steve MolisSteve Molis
Here's one I wrote using a CASE function and some basic match that you can hack up and use. 

CASE( StageName ,
"Prospecting",1,
"Qualification",2,
"Needs Analysis",3,
"Value Proposition",4,
"Id. Decision Makers",5,
"Perception Analysis",6,
"Proposal/Price Quote",7,
"Negotiation/Review",8,
"Closed Won",9,
"Closed Lost",9,0)
<
CASE(PRIORVALUE(StageName) ,
"Prospecting",1,
"Qualification",2,
"Needs Analysis",3,
"Value Proposition",4,
"Id. Decision Makers",5,
"Perception Analysis",6,
"Proposal/Price Quote",7,
"Negotiation/Review",8,
"Closed Won",9,
"Closed Lost",9,0)


All Answers

Steve MolisSteve Molis
Here's one I wrote using a CASE function and some basic match that you can hack up and use. 

CASE( StageName ,
"Prospecting",1,
"Qualification",2,
"Needs Analysis",3,
"Value Proposition",4,
"Id. Decision Makers",5,
"Perception Analysis",6,
"Proposal/Price Quote",7,
"Negotiation/Review",8,
"Closed Won",9,
"Closed Lost",9,0)
<
CASE(PRIORVALUE(StageName) ,
"Prospecting",1,
"Qualification",2,
"Needs Analysis",3,
"Value Proposition",4,
"Id. Decision Makers",5,
"Perception Analysis",6,
"Proposal/Price Quote",7,
"Negotiation/Review",8,
"Closed Won",9,
"Closed Lost",9,0)


This was selected as the best answer
Marc PannenbergMarc Pannenberg
With this many values, it's going to be tough. You would have to use the function "PRIORVALUE" and compare that to the ISPICKVAL.

Check out this cheat sheet (page 18).
Marc PannenbergMarc Pannenberg
Very elegant, Steve. I will have to steal it...
sakshi kaushiksakshi kaushik
hey Bart,

let me try to understand your problem.. So if once a salesrep has selected stage Prospect , he can never select this option again.. Is that right?

In a nutshell , once an opportunity has been through Prospect , it can never be Prospect again ?

From this pespective , I think of approach to mark a flag as soon as opportunity enters prospect. And in the validation rule just check if user has selected Stage= Prospect and flag=true.

Did i get it right ?
Steve MolisSteve Molis
Thanks @Orionite  help yourself, I did a little pressie on using basic math to get around complex nested OR's, AND's, PICKVALS, etc...  at Dreamforce '11.  If you're on the DF Portal I think the deck has been made public (lots of neat stuff in there from @impStout, Jeff Shelton, and mark Epperson too)  
sakshi kaushiksakshi kaushik
Bart@ Do you not WANT to come back to any stage once moved past it or just Prospect?
Bart WardBart Ward
ihskas, you are right. I don't want them to be able to take it back to "Prospect" once it has passed that stage. I saw a validation rule called,"Prevent Sales Reps from Moving Opportunity Stage Backwards", that sums up what you are saying. I was just trying to avoid writing an approval process to go with it. I am still fairly new to Salesforce and don't really understand about the flag and checkbox.
Bart WardBart Ward
ihskas, I'm sorry, I was just informed it cannot go back to ANY PREVIOUS STAGE. Sorry for the confusiion.
sakshi kaushiksakshi kaushik
Bart@ Never mind.. In the process I learnt something new.

SteveMo@ I went through deck and its so awesome. Found a lot of cool stuff to learn!!
Steve MolisSteve Molis
Thanks @ihskas !
salesforce mickysalesforce micky
Hi Steve,

I have tried the Code above . I need some modification in my code as itis working fine for the backward functionality i.e when i save a record like if i have saved in Stage5 i cannot save it in stage4 .........like this it is working fine for each stage

The Same functionlaity i want to be Worked in Forward direction  i.e when i save a record stage in i.e Stage1 it has to be saved in Stage2 only but not other stage .

I need anyone help to get out from this........

Here is my code for Backward functionality

CASE(LocationStage__c,
"Stage1",1,
"Stage2",2,
"Stage3",3,
"Stage4",4,
"Stage5",5,
"Stage6",6,
"Stage7",7,
"Stage8",8,
"Stage9",9,
0)
>

CASE(PRIORVALUE(LocationStage__c) ,
"Stage1",1,
"Stage2",2,
"Stage3",3,
"Stage4",4,
"Stage5",5,
"Stage6",6,
"Stage7",7,
"Stage8",8,
"Stage9",9,
0) + 1


I have also tried in Forward Direction . which works on changing the record in forward only i.e from Stage1 it will move to Stage2 and from Stage2 to Stage3 ..............Stage8 to Stage9  only this code will not work for Backward functionality like 
When i save a record in Stage9 then it should not be changed to any other Stage , But its is changing in this code
Here is the Code which i implemented in my Dev Edition

I Have also implemented in as i mentyioned above in forward direction with some other code but i have missed it.


Steve i need your help, 

Warm Regards
Micky

Stephanie CarrollStephanie Carroll
Is there a way to apply the logic from the best answer below to a formula within a workflow rule that cannot use priorvalue? 



CASE( StageName ,
"Prospecting",1,
"Qualification",2,
"Needs Analysis",3,
"Value Proposition",4,
"Id. Decision Makers",5,
"Perception Analysis",6,
"Proposal/Price Quote",7,
"Negotiation/Review",8,
"Closed Won",9,
"Closed Lost",9,0)
<
CASE(PRIORVALUE(StageName) ,
"Prospecting",1,
"Qualification",2,
"Needs Analysis",3,
"Value Proposition",4,
"Id. Decision Makers",5,
"Perception Analysis",6,
"Proposal/Price Quote",7,
"Negotiation/Review",8,
"Closed Won",9,
"Closed Lost",9,0)
Steve MolisSteve Molis
@Stephanie Carroll  

Can you elaborate on what you're trying to do in your WFR? 
Steve MolisSteve Molis
Can you post a screenshot of your WFR?  When it is the WFR set to evaluate?  You can only use the PRIORVALUE and ISCHANGED function in a WFR if the evaluation criteria is set to Evaluate the rule when a record is: created, and every time it’s edited. 

PRIORVALUE Function => https://help.salesforce.com/HTViewHelpDoc?id=customize_functions_i_z.htm&language=en_US#PRIORVALUE (https://help.salesforce.com/HTViewHelpDoc?id=customize_functions_i_z.htm&language=en_US#PRIORVALUE)

Stephanie CarrollStephanie Carroll
Hi @Steve Molis!

I think your second comment told me what I was missing.  I had it set to created, and everytime it is edited to subsequently meet the critieria when it should have been set to created, and every time it's edited.  

I essentially just copied and pasted the case fucntions you posted on this thread, but wanted it to create a new task when a stage was moved from a farther stage to an earlier stage rather than prevent them from being able to do it.  Thank you for your help!  I was at dreamforce this past year and one of your sessions was my favorite by far. You certainly are very quick and helpful! 

I changed the evaluation criteria and it got rid of the error I was encountering about priorvalue. 

CASE( StageName ,
"Identify",1,
"Gather",2,
"Verify",3,
"Qualified",4,
"Bid Decision",5,
"Quote",6,
"Trial",7,
"Negotiate",8,
"Paperwork Pending",9,
"Closed Won",10,
"Closed Lost",10,0)
<
CASE(PRIORVALUE(StageName) ,
"Identify",1,
"Gather",2,
"Verify",3,
"Qualified",4,
"Bid Decision",5,
"Quote",6,
"Trial",7,
"Negotiate",8,
"Paperwork Pending",9,
"Closed Won",10,
"Closed Lost",10,0)
Shaik RizwanShaik Rizwan
Dear Steve,
Your answer using Case is working fine with me (Stage cannot be backward) but it should NOT be with all. Administrator or certain user can change it to backstage. How can we put or add condition to this validation, allowing admin or any user to change stage backward. 
Laurentiu LulciucLaurentiu Lulciuc
By adding  && $Profile.Name <>"System Administrator" this will alow only for System Administrator profile to change the stage backwards. Like this:

CASE( StageName ,
"Identify",1,
"Gather",2,
"Verify",3,
"Qualified",4,
"Bid Decision",5,
"Quote",6,
"Trial",7,
"Negotiate",8,
"Paperwork Pending",9,
"Closed Won",10,
"Closed Lost",10,0)
<
CASE(PRIORVALUE(StageName) ,
"Identify",1,
"Gather",2,
"Verify",3,
"Qualified",4,
"Bid Decision",5,
"Quote",6,
"Trial",7,
"Negotiate",8,
"Paperwork Pending",9,
"Closed Won",10,
"Closed Lost",10,0)&& $Profile.Name <>"System Administrator"

 
Steve MolisSteve Molis

Shaik Rizwan

Sorry, that post is over 4 years old, so I do not follow it anymore.  Anyway, to answer your question you can exempt a User, Role, or Profile from a VR by amending the Formula with either a User ID, Username, Role Name or Profile Name like this: 
$User.Id <> "005500000000aBc"

$User.Username  <> "User.Name@yoursfdc.org"

$UserRole.Name <> "The RoleName"

$Profile.Name <> "The ProfileName"

But the preferred way to do it would be to use a Custom Setting and add that to your VR Formula like this:
Custom Settings | Salesforce
https://help.salesforce.com/HTViewHelpDoc?id=cs_about.htm&language=en_US (https://help.salesforce.com/HTViewHelpDoc?id=cs_about.htm&language=en_US)
AND( 
$Setup.ValidationRules__c.Exempt__c = FALSE,
CASE( StageName , 
"Prospecting", 1, 
"Qualification", 2, 
"Needs Analysis", 3, 
"Value Proposition", 4, 
"Proposal/Price Quote", 5, 
"Negotiation/Review", 6, 
"Closed - Won", 7, 
"Closed - Lost", 7, 
0) 
< 
CASE(PRIORVALUE(StageName), 
"Prospecting", 1, 
"Qualification", 2, 
"Needs Analysis", 3, 
"Value Proposition", 4, 
"Proposal/Price Quote", 5, 
"Negotiation/Review", 6, 
"Closed - Won", 7, 
"Closed - Lost", 7, 
0) 
)



 
Steve EarlySteve Early
Here is another variation of "moving backwards" prevention. We have only a few sales stages (which helps) and one of our business units has the following requirement:

Once an opportunity is past the first stage (Opportunity Qualified) it can never go back to that stage again. It has to be moved forward, or to Won or Lost.
Once it gets to Final Proposal, it can go back to Technical Sales if we discover some additional technical requirement that hadn't been investigated previously, or needed further clarification.
Once it gets to Commercial Negotiation, it cannot go back to any previous stage - it has to go to Won or Lost.

Here is the Validation Rule I created to meet this requirement based on Steve Mo's post:

AND(
CONTAINS(Record_Type_Name__c, "[business unit record type - formula field]"),  [you could use RecordTypeId]
ISCHANGED(StageName) &&
CASE(StageName,
"Opportunity Qualified",1,
"Technical Sales",2,
"Final Proposal",3,
"Commercial Negotiation",4,
"Closed Won",5,
"Closed Lost",5,
0)
<
CASE(PRIORVALUE(StageName), 
"Opportunity Qualified",1,
"Technical Sales",2,   [prevents movement back to first stage]
"Final Proposal",2,  [allows this stage to go back one to Technical Sales]
"Commercial Negotiation",5,  [prevents movement back to any stage - can only go to Won or Lost]
"Closed Won",6, 
"Closed Lost",6,
0))
&& $Profile.Name <> "System Administrator"

In looking at this now, closed won and lost could probably have a value of 5 instead of 6. Anyway - I hope this helps somebody.
Amanda ElmoreAmanda Elmore
I'm trying to do something similar. We do not use the lead function so I'm trying to modify our opportunities setting to work for 'leads'. Currently our opportunity stages are:
Work In Progress (not included in pipeline reports)
Stretch 120
Stretch 60
Commit
Closed Won
Closed Lost


Typically opportunities should move in the order outlined above by may skip Work In Progress and start with Stretch 120 or Stretch 60. However, there are no restrictions on moving back and forth between stages - a Commit opportunity may move back to Stretch 120. (not the best practice but we're working on adoption first and we will revisit this!) So I do not want to prevent going backwards in stages.

But, I've created a Lead stage that would also be omitted from the reps sales pipeline. This stage is to be used for MQL leads that sales should follow up on and determine if it's a real opportunity. If so, then the stage should be moved from Lead to whatever stage is most appropriate... BUT once it's moved from Lead it should NEVER be allowed to move back to Lead. Additionally, I'm thinking about creating an additional closed stage for Disqualified Lead so that 'bad leads' are not counted in the Closed Lost opportunities.

We're on professional version so we have some limitations but could the case function work for this instance? I'm a fairly beginner admin so I've only minimally worked with validation code. I appreciate your input!
Steve MolisSteve Molis
Man! talk about "a blast from the past" that thing is almost as old as our daughter MillieMo.  


Amanda Elmore, let me take a crack at it and see what I can do (no promises)
Amanda ElmoreAmanda Elmore
Thanks Steve! I appreciate the help :)

I did end up creating another additional stage for Disqualified Lead.
Current stages now equal:
  • Lead (once moved from this stage it should never move back, omitted from pipeline)
  • Work In Progress (omitted from pipeline)
  • Stretch 120
  • Stretch 60
  • Commit
  • Closed Won
  • Closed Lost
  • Disqualified Lead (reserved for leads that never make it to a sales qualified opportunity) * I also have validation set so that an opportunity can not be reopened if the stage is last marked as this value.
Thanks so much!!!
Steve MolisSteve Molis

Amanda Elmore

I think you need to break this into separate Validation Rules for each condition.  Trying to write a "One Size Fits All" Validation Rule doesn't really save you anything, and you can end up playing Loophole Whack-a-Mole.  Also you can only have 1 error message per Validation Rule, and the error message should be as informative and helpful to the user as possible.  But when you create a single Rule that evaluates 20 combinations of fields, values, etc you're stuck with a generic Error like this
User-added image
instead of this... 
User-added image
Amanda ElmoreAmanda Elmore
That does make sense. But I keep getting hung up on how to prevent the user from selecting Lead value if it's already been moved from Lead. Really, Lead should only be an option when creating a new opportunity. I feel like what I'm trying to do may be more complex than I originally thought.
Steve MolisSteve Molis
Oh wait....   

"Lead should only be an option when creating a new opportunity."  

Then your VR Formula would be something like
 
AND(
NOT(ISNEW),
ISCHANGED(StageName),
TEXT(StageName) = "Lead")



 
Amanda ElmoreAmanda Elmore
AHHHHH!!!! I was totally over complicating it! I was thinking I'd need to use record types or some sort of additional boolean field. I did add () to your formula to get the below to work:

AND(
NOT(ISNEW()),
ISCHANGED(StageName),
TEXT(StageName) = "Lead")

For you!

User-added image
Steve MolisSteve Molis
Cheers! 

c[_]*[_]D