+ Start a Discussion
Mike FitchMike Fitch 

Help with Validation Rule for Opportunity Probability Field.

I created a validation rule for the standard probability stage in opportunities. What I need the rule to do is limit the users to entering percentages only divisible by 10 with three exceptions. 1) They can never use 50% for any stage. 2) The minimum is 10% unless the stage is 'closed lost' which has a probability of 0%. 3) The maximum is 90% unless the stage is 'closed won' which has a probability of 100%. I can get the error message to come up only when 100% is entered on a stage other then closed won. The system still allows 0% and 50%. Here is my rule:

OR(
AND(
!ISPICKVAL(StageName,"Closed Won"),
Probability<>0.50,
MOD(Probability*100,10)<>0)
,
AND(
!ISPICKVAL(StageName,"Closed Won"),
Probability=1)
)

Can someone help me and show me what I'm doing wrong?
Thanks
Best Answer chosen by Mike Fitch
Pavan Kumar KajaPavan Kumar Kaja
. Changed Accordingly and mark if this is resolved.

OR(
AND(
ISPICKVAL(StageName,"Closed Lost"),
Probability <> 0),
AND(
MOD(Probability*100,10)<>0,
!ISPICKVAL(StageName,"Closed Lost"),
!ISPICKVAL(StageName,"Closed Won")
),
AND(
OR(
Probability = 0.5,
Probability = 0,
Probability = 1),
!ISPICKVAL(StageName,"Closed Lost"),
!ISPICKVAL(StageName,"Closed Won")
),
AND(
ISPICKVAL(StageName,"Closed Won"),
Probability <> 1)
)

All Answers

Pavan Kumar KajaPavan Kumar Kaja
Try Below one.

OR(
AND(
!ISPICKVAL(StageName,"Closed Won"),
MOD(Probability*100,10)<>0)
,
Probability = 0.5,
AND(
!ISPICKVAL(StageName,"Closed Won"),
Probability=1)
)
Mike FitchMike Fitch
I tried what you suggested and it's still allowing values that it shouldn't allow. This is a complex rule (at least for me) with a few conditions. Let me be more specific. There are 8 total stages. Identifying, Confirming, Value Prop, Proposal, Presenting, Closing, Closed Won and Closed Lost. For the first six stages (Identifying, Confirming, Value Prop, Proposal, Presenting and Closing) the conditions are as follows:

They can only enter a probabilty that is divisible by 10 except for 0, 50 and 100.
10, 20, 30, 40, 60, 70, 80 and 90 being the accepted values. Any other value besides those mentioned will bring up an error message.

in addition, Closed Won can only be 100% and Closed Lost can only be 0%. 

I had my original code working before but it was obviously flawed. I'm not even sure if I'm close on this one. I'm very new to salesforce so please bear with me.
Pavan Kumar KajaPavan Kumar Kaja
Finally try below one.

OR(
AND(
ISPICKVAL(StageName,"Closed Lost"),
Probability <> 0),
AND(
MOD(Probability*100,10)<>0,
!ISPICKVAL(StageName,"Closed Lost"),
!ISPICKVAL(StageName,"Closed Won")
),
AND(
Probability = 0.5,
!ISPICKVAL(StageName,"Closed Lost"),
!ISPICKVAL(StageName,"Closed Won")
),
AND(
ISPICKVAL(StageName,"Closed Won"),
Probability <> 1)
)
Mike FitchMike Fitch
I tried the new formula and the Closing stage accepted a 0% Probability. Only Closed Lost should accept 0%. The code might be flawed from the start. Should I rethink the entire formula? 
Pavan Kumar KajaPavan Kumar Kaja
Modified according to ur comments.

OR(
AND(
ISPICKVAL(StageName,"Closed Lost"),
Probability <> 0),
AND(
MOD(Probability*100,10)<>0,
!ISPICKVAL(StageName,"Closed Lost"),
!ISPICKVAL(StageName,"Closed Won")
),
AND(
OR(
Probability = 0.5,
Probability = 0),
!ISPICKVAL(StageName,"Closed Lost"),
!ISPICKVAL(StageName,"Closed Won")
),
AND(
ISPICKVAL(StageName,"Closed Won"),
Probability <> 1)
)
Mike FitchMike Fitch
Ok it's getting closer to working the way i need it to. Everything works good now except no other stage besides 'Closed Won' should allow a 100% value. 
Pavan Kumar KajaPavan Kumar Kaja
. Changed Accordingly and mark if this is resolved.

OR(
AND(
ISPICKVAL(StageName,"Closed Lost"),
Probability <> 0),
AND(
MOD(Probability*100,10)<>0,
!ISPICKVAL(StageName,"Closed Lost"),
!ISPICKVAL(StageName,"Closed Won")
),
AND(
OR(
Probability = 0.5,
Probability = 0,
Probability = 1),
!ISPICKVAL(StageName,"Closed Lost"),
!ISPICKVAL(StageName,"Closed Won")
),
AND(
ISPICKVAL(StageName,"Closed Won"),
Probability <> 1)
)
This was selected as the best answer
Mike FitchMike Fitch
I got it fixed. I added that Probability = 1 line and it works perfectly. Thank you so much for all of your help!!!

AND(
OR(
Probability = 0.5,
Probability = 0,
Probability = 1),
!ISPICKVAL(StageName,"Closed Lost"),
!ISPICKVAL(StageName,"Closed Won")

Rahul MehataRahul Mehata
I have requirement when probability is 3o% or higher than 30% then Primary partener which is lookup field must be required. I have tried with this but its not working
AND(Probability>=30,ISBLANK(Primary_Partner__c))