+ Start a Discussion
Rachel LinderRachel Linder 

Validation Rule to Keep an Opportunity Stage being changed to Closed & Booked if a Specific product is not approved

I am trying to write a rule that will prevent someone from moving the stage of an opportunity to Closed & Booked if the IIS Cost is not approved for a specific opportunity product called IIS - Professional Services - PS.These are the fields I am working with

OPPORTUNITY Stage - Picklist Value - value would be Closed & Booked
OPPORTUNITY PRODUCT NAME - Picklist Value - value would be IIS - Professional Services - PS
OPPORTUITY PRODUCT APPROVED BY - Picklist Value - value would be Blank

If the last field is blank then you cannot move the stage to closed and booked. What wwould this rule look like. And would it be on the opportunity or opportunity product?
Thanks.



AND (ISPICKVAL(Opportunity.StageName, "Closed & Booked"),
(Product2.Name  = 'IIS - Professional Services - PS'),   ISBLANK( IIS_PS_Cost__c ))
Best Answer chosen by Rachel Linder
TempleHoffTempleHoff
So, you want the validate rule to return false if the Stage is Closed & Booked and the sum of Sales Prices on unapproved Professional Services is greater than 0?   So, its ok.  I would probably aggrigate on Quantity rather than Price, just in case a Price is $0.  And you could expose the PS_Cost_Approved__c field on your page layout long enough to see the value and see if that tells you anything (or run a quick report to see it).

All Answers

TempleHoffTempleHoff
Create a hidden roll-up summary field on Opportunity of Oppoortunity Product.  Select SUM.  Aggregate the volume field.  Select Only records meeting certian criteria...  Select a unique field to identify your Product2 as IIS.  You may need to create a hidden field on Opportunity Product to equal the Product2 name or use the description field.  After Spring 14 Release you should be able to use the Product Name.  For the second criteria make sure your approved by is NOT approved.   Then for your field validation just ensure your hidden summary field is 0 in order to set the Opportunity to Closed & Booked.
Rachel LinderRachel Linder
Ok I am doing something wrong....

    Here is the hidden field on the opportunity:

Custom Field Definition Detail

Field Information
Field Label PS Cost Approved Object Name Opportunity
Field Name PS_Cost_Approved   
API Name PS_Cost_Approved__c   
Description Looks to see if PS is approved
Help Text
Created By Rachel Linder, 3/10/2014 2:10 PM Modified By Rachel Linder, 3/11/2014 9:19 AM
Roll-Up Summary Options
Data Type Roll-Up Summary Summary Type SUM
Summarized Object  Opportunity Product   
Field to Aggregate Opportunity Product: Sales Price
Filter Criteria
(Product Name MirrorequalsIIS - Professional Services - PS) and (IIS PS Cost Approved Byequals)

    Here is the validation rule written for the opportunity:

Validation Rule Detail

Rule Name PS_Cost_Approval_before_Closed_Booked Active Checked
Error Condition Formula AND (
ISPICKVAL( StageName , "Closed & Booked"),
( PS_Cost_Approved__c > 0)
)
Error Message Cannot save as Closed Booked because PS Cost has not been approved for the IIS - Professional Services - PS Product line. Please send a task to Arsen Ovanesoff to approve cost.
Error Location Top of Page
Description a rule that makes it mandatory for IIS PS cost to be approved before we can move the stage to “Closed Booked”.

So, can you please take a look and let me know what is happening.
TempleHoffTempleHoff
So, you want the validate rule to return false if the Stage is Closed & Booked and the sum of Sales Prices on unapproved Professional Services is greater than 0?   So, its ok.  I would probably aggrigate on Quantity rather than Price, just in case a Price is $0.  And you could expose the PS_Cost_Approved__c field on your page layout long enough to see the value and see if that tells you anything (or run a quick report to see it).
This was selected as the best answer
Rachel LinderRachel Linder
Thanks for all your help. I was able to accomplish this without any roll-up fields and such.