+ Start a Discussion
SkeeterSkeeter 

Prevent Oppty Stage Change Validation Rule

I'm having problems getting my validation to work.  It should prevent the stage to be changed to anything but unqualified unless one of the following is true.

1. Owner Division does not contain = ‘Finance’, ‘IT’, ‘Marketing’
2. Score > 0
3. Business Type = Renewal
4. Owner Division equals ‘Finance’ and Total <= $20,000
5. Total = 0

This is what I have, but it doesn't seem to be working.  It is allowing me to change the stage when no statements above are true.
 
OR( 
AND( 
NOT(ISPICKVAL(StageName,"Unqualified")), 
ISCHANGED(StageName), 
NOT( 
OR( 
CONTAINS(Opportunity_Division__c,"IT"), 
CONTAINS(Opportunity_Division__c,"Finance"), 
CONTAINS(Opportunity_Division__c,"Marketing"), 
Score__c > 0, 
ISPICKVAL(Business_Type__c,"Renewal"), 
Total__c = 0.00)), 
AND( 
NOT(ISPICKVAL(StageName,"Unqualified")), 
ISCHANGED(StageName), 
CONTAINS(Opportunity_Division__c,"Finance"), 
NOT(Total__c <= 20000))))

 
James LoghryJames Loghry
Here's what I came up with.  I had trouble understanding what you're looking for exactly though, as there are several double negatives in the scenarios you posted.  The gist is that validation rules return TRUE for the ERROR condition though,  which looks the opposite of the formula you posted.
 
AND(
    NOT(ISPICKVAL(StageName,”Unqualified”))
    ,ISCHANGED(StageName)
    ,OR(
            CONTAINS(Opportunity_Division__c,"IT")
            ,CONTAINS(Opportunity_Division__c,"Finance") 
            ,CONTAINS(Opportunity_Division__c,"Marketing")
            ,Score <= 0
            ,NOT(ISPICKVAL(Business_Type__c,"Renewal”))
            ,AND(Total_c <> 0,OR(Contains(Opportunity_Division__c,”Finance”),Total__c > 20000))
    )
)
James LoghryJames Loghry
Looks like textpad added in some gnarly double quotes around Unqualified when I pasted the formula, so you'll have to fix those too.