2

I am trying to determine what the following validation rules are saying or mean. Can you please help?

VALIDATION RULE 1

IF(AND( Amount > 100000,ISPICKVAL(StageName, "Closed Won")),NOT(AND(Amount<= Credit_Approved_Amount__c,TODAY()<= Credit_Expiration_Date__c, ISPICKVAL( Credit_Status__c, "Approved"))),IF(ISBLANK(Comm_Type__c),NOT(AND(Amount<= Credit_Approved_Amount__c,TODAY()<= Credit_Expiration_Date__c, ISPICKVAL( Credit_Status__c, "Approved"))),FALSE) )

VALIDATION RULE 2

AND (ISPICKVAL(StageName, "Closed & Booked"), (Credit_Approved_Amount__c < Amount || Credit_Expiration_Date__c < TODAY () || ISPICKVAL(Credit_Status__c, "Waiting for More Info") || ISPICKVAL(Credit_Status__c, "Declined") || LEN(Account.GP_Customer_Id__c) = 0 || ISBLANK(Credit_Approved_Amount__c) || ISBLANK( Credit_Expiration_Date__c ) || ISPICKVAL( Credit_Status__c , "") ))

Thank you.

3

First formula:

There are a few different operators here. I'll start from the top of the first formula and work my way down. The first formula starts with an IF function, which has three parameters. Call them A, B, C. In this case, A is the block that starts with AND, B starts with NOT, and C starts with IF. IF evaluates the initial condition (A), and returns B if (A == true), or C if (A == false). Another operator that appears is AND. AND returns true if all of its conditions are true.

It sounds complicated, it's helpful to break compound formulas like these up into pieces. Here is the formula again with indentations and annotations to help explain (comments marked by ###):

The first formula with notes:

IF(                                                       ### our initial IF which 
                                                              will evaluate A, and 
                                                              return B if true, 
                                                              C if false
    AND(                                                  ### this is A. 
        Amount > 100000,
        ISPICKVAL(StageName, "Closed Won")
    ),
    NOT(                                                  ### this is B.
        AND(
            Amount<= Credit_Approved_Amount__c,
            TODAY()<= Credit_Expiration_Date__c, 
            ISPICKVAL( Credit_Status__c, "Approved")
        )
    ),
    IF(                                                   ### this is C
        ISBLANK(Comm_Type__c),                            ### this is CA
        NOT(                                              ### this is CB
            AND(
                Amount<= Credit_Approved_Amount__c,
                TODAY()<= Credit_Expiration_Date__c, 
                ISPICKVAL( Credit_Status__c, "Approved")
            )
        )
        ,
        FALSE                                             ### this is CC
    ) 
)

So since A is using an AND operator, for A to be true, Amount must be greater than 100000 and StageName must be 'Closed Won.' If only one is true and the other is false, A will be false.

So let's say A is true, then we return B. To determine the value of B, you'll see that there is another AND, wrapped with a NOT. Start with the inner AND - if all three of these statements are true (Amount less than or equal to the value of the field Credit_Approved_Amount__c, Credit_Expiration_Date__c earlier than today, Credit_Status__c is 'Approved'), then the AND will return true. Otherwise, return false. The NOT that wraps this AND means that whatever the AND operator returns, the NOT returns the opposite. So if all three of these statements are true, the AND will return true, but the NOT will return false.

If A is false, then we return C. C begins with another IF. Remember that the IF operator evaluates its first parameter (CA), and returns its second parameter (CB) if true, the third (CC) if false. In this case, you'll see that CB matches our inner formula B exactly, which I explained above. So if CA is true (Comm_Type__c is blank), CB is returned. Otherwise, CC is returned, which is just a nice clean 'false' in this case.

Second formula:

The second formula is actually much simpler. The || operator performs the same function as OR, meaning that it returns true if any of its component statements are true. For example, if you had OR(A, B, C, D) and A B and C were false, but D was true, then the whole OR would return true. For this second forumla, if StageName is 'Closed & Booked' and ANY one or more of the following statements are true, then the whole formula will return true.

AND (
    ISPICKVAL(StageName, "Closed & Booked"),
    (
        Credit_Approved_Amount__c < Amount 
        || 
        Credit_Expiration_Date__c < TODAY () 
        || 
        ISPICKVAL(Credit_Status__c, "Waiting for More Info") 
        || 
        ISPICKVAL(Credit_Status__c, "Declined") 
        || 
        LEN(Account.GP_Customer_Id__c) = 0 
        || 
        ISBLANK(Credit_Approved_Amount__c) 
        || 
        ISBLANK( Credit_Expiration_Date__c ) 
        || 
        ISPICKVAL( Credit_Status__c , "") 
    )
)
2

VALIDATION RULE 1

IF(AND( Amount > 100000,ISPICKVAL(StageName, "Closed Won")),NOT(AND(Amount<= Credit_Approved_Amount__c,TODAY()<= Credit_Expiration_Date__c, ISPICKVAL( Credit_Status__c, "Approved"))),IF(ISBLANK(Comm_Type__c),NOT(AND(Amount<= Credit_Approved_Amount__c,TODAY()<= Credit_Expiration_Date__c, ISPICKVAL( Credit_Status__c, "Approved"))),FALSE) )

IF Amount is greater than 100,000 and the Stage Name picklist is Closed Won

THEN check if Amount is less than or equal to the Credit Approved Amount and Today's date is less than or equal to the Credit Expiration date and the Credit Status picklist is Approved and if this is true return false and if false return true (this is the NOT on the start of this)

OTHERWISE if the check on Amount is greater than 100,000 and the Stage Name picklist is Closed Won is false

THEN IF check is Comm Type Blank and if it is

THEN check if Amount is less than or equal to the Credit Approved Amount and Today's date is less than or equal to the Credit Expiration Date and the Credit Status Picklist value is Approved and if False return True and if True return False.

(Note that you are looking for a true value to trigger the validation rule, you have to try and think about that backwards I find, you are writing an exception catcher so you want your formula to be true when someone is doing something they shouldn't.

Also I find it easier if you break these things down into a more readable format like

IF(
 AND( 
  Amount > 100000,ISPICKVAL(StageName, "Closed Won")),
   NOT(
    AND(
     Amount<= Credit_Approved_Amount__c,TODAY()<= Credit_Expiration_Date__c, ISPICKVAL( Credit_Status__c, "Approved"))),
   IF(ISBLANK(Comm_Type__c),
    NOT(
     AND(
      Amount<= Credit_Approved_Amount__c,TODAY()<= Credit_Expiration_Date__c, ISPICKVAL( Credit_Status__c, "Approved"))),
FALSE)
)

VALIDATION RULE 2

AND (ISPICKVAL(StageName, "Closed & Booked"), (Credit_Approved_Amount__c < Amount || Credit_Expiration_Date__c < TODAY () || ISPICKVAL(Credit_Status__c, "Waiting for More Info") || ISPICKVAL(Credit_Status__c, "Declined") || LEN(Account.GP_Customer_Id__c) = 0 || ISBLANK(Credit_Approved_Amount__c) || ISBLANK( Credit_Expiration_Date__c ) || ISPICKVAL( Credit_Status__c , "") ))

This one is a lot more straight forward, basically it's saying If the Stage Name picklist is "Closed & Booked" and any of the other field values being checked below are True then the validation returns an error. So plain Enlgish If Stage Name is "Closed & Booked"

AND (think of this as Stage Name must be that value and all of the rest of these values only need one to be true for the validation to fire) Credit Approved Amount is less that Amount

OR Credit Expiration Date is less than Today

OR Credit Status picklist is "Waiting for More Info" OR "Declined"

OR The Length of GP Customer ID on Account object is 0

OR Credit Approved Amount is blank (might want to use ISNULL here)

OR Credit Expiration Date is blank

OR Credit Status picklist is null (set to none/unset)

THEN return true and fire the validation rule otherwise false and don't fire it.

AND(
  ISPICKVAL(StageName, "Closed & Booked"), 
   (Credit_Approved_Amount__c < Amount || 
   Credit_Expiration_Date__c < TODAY () || 
   ISPICKVAL(Credit_Status__c, "Waiting for More Info") ||
   ISPICKVAL(Credit_Status__c, "Declined") ||
   LEN(Account.GP_Customer_Id__c) = 0 ||
   ISBLANK(Credit_Approved_Amount__c) || 
   ISBLANK( Credit_Expiration_Date__c ) || 
   ISPICKVAL( Credit_Status__c , "") )
)
  • Ok so for our order of stages we go from Closed Won to Closed & Booked and we have a case where something gets to Closed Won and then when we change it to Closed & Booked we are getting an error. And I am trying to determine what that is. And my guess is that if it gets to closed won and is an opportunity that is under 100,000 and comm type is not blank then it can save at Closed Won. And from there when changing to Closed and Booked it is looking for those fields for credit to be complete. And since they are not complete then it triggers a "true" response thus generating an error. Correct? – Rachel Linder Jun 1 '15 at 21:52
  • @RachelLinder yep I'd say it's the second validation that's acting as a catch all in that criteria. You could try adding other conditionals in there to avoid this but I'd instead look at simplifying the current validation rules and adding another perhaps for the large number of OR checks in the second validation rule. – Shane Porter Jun 2 '15 at 23:48
  • I met with our operations director and created an excel document that shows the scenarios we are trying to cover when we move from Opportunity Stage "Commit" to Opportunity Stage "Closed Won" OR Opportunity Stage "Closed & Booked" Is there a way I can add an image here to show this? – Rachel Linder Jun 16 '15 at 21:55
  • Upload it to imgur.com and post the link might work. – Shane Porter Jun 16 '15 at 22:28
  • Ok I am giving this a try :). The link is: imgur.com/tjTlrqC – Rachel Linder Jun 18 '15 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.