0

I've tried several iterations of formulas, but can't get the last section to work. It is intended that the Validation would fire when trying to change to the specific status, if the criteria were met. The criteria needed for the Validation to fire:

  1. Status = Secondary Review AND
  2. Description_of_Issue__c = blank AND EITHER
  3. Project_Name__c = blank OR
  4. Original_Order_Number__c = blank

Expressed another way: 1 AND 2 AND (3 OR 4)

It would also be acceptable if the rule fired for 1 AND 2 AND 3 AND 4 but I believe that scenario is covered with the above formula.

Here's the latest missed mark:

(
    ISPICKVAL ( Status, "Secondary Review" )
    &&
    OR (
        ISBLANK(Description_of_Issue__c),
        ISBLANK(Project_Name__c ),
        NOT(ISBLANK(Original_Order_Number__c ))
    )
)
||
(
    ISPICKVAL ( Status, "Secondary Review" )
    &&
    OR (
        ISBLANK(Description_of_Issue__c),
        NOT(ISBLANK(Project_Name__c )),
        ISBLANK(Original_Order_Number__c )
    )
)
||
(
    ISPICKVAL ( Status, "Secondary Review" )
    &&
    OR (
        ISBLANK(Description_of_Issue__c),
        ISBLANK(Original_Order_Number__c ),
        ISBLANK(Project_Name__c )
    )
)

The only version of this that works is for all fields, but that's not what I need. Any insight/help is greatly appreciated!

1 Answer 1

1

In general, I don't recommend mixing && and || with AND() and OR(). Choose one, and stick with it. In formulas, my preference is to use AND() and OR()

In general, if you find yourself repeating things in formulas (or in code), that's an indication that you can simplify things.

Working from a plain-language description of what you're trying to achieve is, in my opinion, a good way to go about building formulas. The issue here is in how you've translated it from "plain language" into "formula".

Using AND() and OR(), instead of composing things like

1 AND 2 AND (3 OR 4)

you compose things like

AND(
    1,
    2,
    OR(
        3,
        4
    )
)

So if your description is accurate, then the formula that you are looking for is

AND(
    ISPICKVAL(Status, "Secondary Review"),
    ISBLANK(Description_of_Issue__c),
    OR(
        ISBLANK(Project_Name__c),
        ISBLANK(Original_Order_Number__c)
    )
)

I find it kinda hard to describe my exact thought process in building formulas. In this instance though, I might say that I build it from inside-out.

That is, you know that 3 OR 4 are a package deal. They can't be separated. So OR(3,4) becomes a single "unit" that you then use as a building block for the rest of your formula.

7
  • On a second reading, it looks like you may not be familiar with Boolean logic. OR(1, 2) will result in true if either 1 or 2 is true (and AND(1, 2) only results in true if both 1 and 2 are true). This is why you can remove the other 2 "branches" where you're checking if one is blank and the other is not blank (assuming you appropriately move the ISBLANK(Description_of_Issue__c) outside of the OR())
    – Derek F
    Jul 28, 2023 at 23:56
  • Also, it's good to keep in mind that validation rules are better thought of as invalidation rules. If the result is true, then the rule will complain and stop the record from being saved. If what you're describing is when your data is valid, then you'd just need to wrap this entire thing inside of a NOT() (which could be simplified using De Morgan's Laws).
    – Derek F
    Jul 29, 2023 at 0:03
  • This is awesome (thanks so much) but it didn't do what I was looking for. This formula allows the status to change with either 'Project Name' or 'Original Order Number' blank. To advance to the 'Secondary Review' status, we need 'Description of Issue' filled in, and either 'Project Name' OR 'Original Order Number', but both is also fine. Neither is not. I agree, syntax gets confusing when you are talking about making a VR file - it's an anti-rule. Also, thanks for the pointers, really appreciate that. Jul 31, 2023 at 21:45
  • @ChristinHead ah, so it sounds like you don't need the OR() at all here, if you remove the OR() (but keep the stuff inside the OR(), so that it becomes part of the overall AND(), then it should work. In other words, the condition you want to prevent is when Status is "Secondary Review" AND Description of Issue is not blank AND Project Name is blank AND Original Order Number is blank.
    – Derek F
    Jul 31, 2023 at 22:48
  • Unfort, that gets me a VR that fires when all conditions are true, which is not what we are looking for. Those last two fields are an EITHER/OR situation. We need to allow a status change to "SR", but only as long as Field 1 and EITHER Field 2 OR Field 3 are not blank. (We can have a case with no project, just a direct order to reference.) Thanks for your continued help, and if you have any other ideas, I'm all ears! Aug 1, 2023 at 15:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .