Validation Rule

Question

I am looking a validation rule to trigger if the opportunity related to the case is of a certain "Type", a certain Status ("Site Survey") is selected and a custom field of broadband required is "Yes".

If this criteria is met then checks are carried out to make sure certain fields are not left blank such as the "installation address" and "hardware items".

I have carried out some testing before I add the different "Types" (Opportunity Type is pulled into the case using a custom formula field called "Opportunity_Formula__c") to the validation rule and everything works fine. For example if "installation address" field is left blank then the rule will fire.

However when I add the "Opporunity_Formula__c" to the rule it is firing when it is not meant to for example even when all the fields have input and are not left blank. Below is a screenshot of the formula: Here is my code below:

AND(
    ISPICKVAL(Broadband_required__c , "Yes"),
    ISPICKVAL (Status, "Site Survey"),
    OR(
        ISPICKVAL( Type_of_Broadband__c ,""),
        ISBLANK( Survey__c ),
        ISBLANK( Number_of_Users__c ),
        ISBLANK ( Installation_Address__c ),
        ISBLANK ( Hardware_Items__c ),
        ISPICKVAL( Phone_System_Type__c ,""),
        Opportunity_Formula__c  = "New iPecs Cloud",
        Opportunity_Formula__c = "New iPecs On-Prem",
        Opportunity_Formula__c = "New Gamma HZN"
    )
)

Any help would be much appreciated

Answer 1

From your description, this just sounds like a case of poor criteria grouping.

Validation rules fire when the overall result is true. Taking your statement that it works fine without the inclusion of Opportunity_Formula__c, let us assume that the Case you're testing this on:

• Has the "Site survey" status
• Broadband_required__c is "yes"
• All of the other fields being required are not blank

AND() returns true when all of the criteria inside of it are true
OR() returns true when any of the criteria inside of it are true

From our assumptions earlier, the first 2 parts of your AND() are true, so we can ignore them and focus on the OR(). If the OR() returns true here, then the validation rule will fire.

So, simplified, your validation rule looks like this

AND(
    true,
    true,
    OR(
        false,
        false,
        false,
        false,
        false,
        false
    )
)

What happens when you introduce Opportunity_Formula__c? Your (partially evaluated) validation rule looks like this

AND(
    true,
    true,
    OR(
        <false x6>,
        Opp type == New iPecs Cloud?,
        Opp type == New iPecs On-Prem?,
        Opp type == New Gamma HZN?
    )
)

If the Opportunity type is any of those 3 values, it turns the entire OR() to true. Given that the rest of the AND() is true, this causes the validation rule to evaluate to true and complain (even though all of the fields are filled in!)

The fix here is that you need to separate the check against Opportunity type from your blank field checks (or rather, to keep the blank field checks separated from everything else).

AND(
    ISPICKVAL(Broadband_required__c , "Yes"),
    ISPICKVAL (Status, "Site Survey"),
    OR(
        Opportunity_Formula__c  = "New iPecs Cloud",
        Opportunity_Formula__c = "New iPecs On-Prem",
        Opportunity_Formula__c = "New Gamma HZN"
    ),
    OR(
        ISPICKVAL( Type_of_Broadband__c ,""),
        ISBLANK( Survey__c ),
        ISBLANK( Number_of_Users__c ),
        ISBLANK ( Installation_Address__c ),
        ISBLANK ( Hardware_Items__c ),
        ISPICKVAL( Phone_System_Type__c ,"")
    )
)

Because Opportunity_Formula__c can only have a single value, you want to use OR() to check for equality (is it equal to any one of these?). Using AND() for that (or just putting those 3 checks in the top-level AND() like the status and broadband required checks) would ensure that the validation rule would never fire.

By doing that, you're saying that all of those 6 fields being populated is sufficient for the record to be valid (regardless of the other conditions).