+ Start a Discussion
Nick Bosch 4Nick Bosch 4 

Validation rule that requires approval process

I'm trying to create a validation rule that prevents reps from moving an opportunity forward until it has been approved by our ops team. I've tried a number of versions of formulas on the forum but I can't seem to get it quite right.

The formula should say that if the opportunity - is a Change Request and the stage has changed or if its new and the stage is either Closed/Won, Closed/Won - Pending, or Closing - 90%, and the Approval Status is either New, Pending, or Rejected - then show the validation rule error that says it must be approved before advancing.

Can someone please highlight where I am going wrong here? I would greatly appreciate any help I can get.
 
AND (RecordType.Name = "Change Request", 
OR( 
ISCHANGED(StageName), 
ISNEW()), 
OR( 
ISPICKVAL(StageName, "Closed/Won"), 
ISPICKVAL(StageName, "Closed/Won - Pending"), 
ISPICKVAL(StageName, "Closing - 90%")), 
OR( 
ISPICKVAL(Approval_Status__c, "New"), 
ISPICKVAL(Approval_Status__c, "Pending"), 
ISPICKVAL(Approval_Status__c, "Rejected") 
) 
)

 
Best Answer chosen by Nick Bosch 4
Nick Bosch 4Nick Bosch 4
Figured it out. I was missing additional AND statements.
 
AND (RecordType.Name = "Change Request", 
AND( 
OR( 
ISCHANGED(StageName), 
ISNEW())), 
AND( 
OR( 
ISPICKVAL(StageName, "Closed/Won"), 
ISPICKVAL(StageName, "Closed/Won - Pending"), 
ISPICKVAL(StageName, "Closing – 90%"))), 
AND( 
OR( 
ISPICKVAL(Approval_Status__c, "New"), 
ISPICKVAL(Approval_Status__c, "Pending"), 
ISPICKVAL(Approval_Status__c, "Rejected") 
)) 
)

 

All Answers

Tuur Dutoit 13Tuur Dutoit 13
What exactly goes wrong when using this formula? Is it never triggered, or does it stop all changes?
Are you sure the validation rule is active?
Are you testing with records in the right state (record type, approval status and changing stage name)?
ujwal thejaujwal theja
What is the exact error you found. I have seen that, formula you have written is correct. But I have a small doubt in validation part. Could you please try with 'IF' statement.

IF((AND (RecordType.Name = "Change Request", 
OR( 
ISCHANGED(StageName), 
ISNEW()), 
OR( 
ISPICKVAL(StageName, "Closed/Won"), 
ISPICKVAL(StageName, "Closed/Won - Pending"), 
ISPICKVAL(StageName, "Closing - 90%")), 
OR( 
ISPICKVAL(Approval_Status__c, "New"), 
ISPICKVAL(Approval_Status__c, "Pending"), 
ISPICKVAL(Approval_Status__c, "Rejected") 

)), false, true).

I think it will work. Could you please confirm me. If it works oppostite to the criteria, please change value true in false place and false in true place.
Nick Bosch 4Nick Bosch 4
Ujwal - Thank you for the help. My formula didn't do anything. The rule wasn't triggered at all - aka I never saw an alert. When I use your rule it creates an alert for every change instead of just the ones I want. 

I've checked to make sure that I am using the correct names for the record type, stage, and approval status's. It creates an alert for every change though if I use that rule. Any ideas what the problem might be? Other things I could check?
ujwal thejaujwal theja
Nick- I got your point. Validation rule reflecting for either status or approval or ischanged, am I correct.

I have checked your senario- "The formula should say that if the opportunity - is a Change Request and the stage has changed or if its new and the stage is either Closed/Won, Closed/Won - Pending, or Closing - 90%, and the Approval Status is either New, Pending, or Rejected - then show the validation rule error that says it must be approved before advancing." and see that your logic is not correct. 

Please check the below code. I think this will solve your problem. If not, please confirm me. I will try once again.
 
IF((AND (RecordType.Name = "Change Request", 
AND(OR( 
ISCHANGED(StageName), 
ISNEW()), 
OR( 
ISPICKVAL(StageName, "Closed/Won"), 
ISPICKVAL(StageName, "Closed/Won - Pending"), 
ISPICKVAL(StageName, "Closing - 90%")), 
OR( 
ISPICKVAL(Approval_Status__c, "New"), 
ISPICKVAL(Approval_Status__c, "Pending"), 
ISPICKVAL(Approval_Status__c, "Rejected") 
) 
))), true, false).



 
Nick Bosch 4Nick Bosch 4
Figured it out. I was missing additional AND statements.
 
AND (RecordType.Name = "Change Request", 
AND( 
OR( 
ISCHANGED(StageName), 
ISNEW())), 
AND( 
OR( 
ISPICKVAL(StageName, "Closed/Won"), 
ISPICKVAL(StageName, "Closed/Won - Pending"), 
ISPICKVAL(StageName, "Closing – 90%"))), 
AND( 
OR( 
ISPICKVAL(Approval_Status__c, "New"), 
ISPICKVAL(Approval_Status__c, "Pending"), 
ISPICKVAL(Approval_Status__c, "Rejected") 
)) 
)

 
This was selected as the best answer
ujwal thejaujwal theja
Is that formula working fine for your senario, Nick?
Nick Bosch 4Nick Bosch 4
Ujwal - Yep, thank you for the help. When you clarified what the statement meant it made me realize I was missing the "AND"s.