+ Start a Discussion
Danielle Pratola 5Danielle Pratola 5 

Validation Rule not firing properly on Checkbox

UGH Admin with Validation Rule nesting problems :-/   In summary, this rule is specific to users in the FL market, when a Tour_Status__c = any of our completed stages, either Tour_Completed_By_Sales_User__c (user lookup) OR Tour_Completed_By_Operations__c (CHECKBOX) must be populated.  The error is throwing properly on the Sales User field but it is also erroring when I turn the Operations User Checkbox True.  Any advice? 

AND((CreatedBy.Division ="Florida"),
(ISPICKVAL(Tour_Status__c, "Tour Completed - NPA")||
ISPICKVAL(Tour_Status__c, "Tour Completed - Requests follow up call")||
ISPICKVAL(Tour_Status__c, "Tour Completed - Wants to think about it")||
ISPICKVAL(Tour_Status__c, "Tour Completed - Declined")),
((ISBLANK(Tour_Completed_By_Sales_User__c)|| Tour_Completed_By_Operations__c
)))
Philip NelsonPhilip Nelson
Hey Danielle,

If I'm understanding your question correctly, it looks like you just need the formula tweaked.

Currently, the third part of your formula says if Tour Completed By Sales User is blank (in other words FALSE) OR if Tour Complete By Operations is TRUE. This is why it fires when you check the operations checkbox.

Instead of using 'ISBLANK', I would suggest changing that to 'NOT' (as in not true or un-checked) and adding a 'NOT' around the Tour Completed By Operations field, as shown below. Then I think you should be set.

/* The following the things must be true*/
AND(
    (
        /* Division must be Florida */
        CreatedBy.Division = "Florida"
    ),
    (
        /* Tour Status must be a completed stage */
        ISPICKVAL(Tour_Status__c, "Tour Completed - NPA") ||
        ISPICKVAL(Tour_Status__c, "Tour Completed - Requests follow up call") ||
        ISPICKVAL(Tour_Status__c, "Tour Completed - Wants to think about it") ||
        ISPICKVAL(Tour_Status__c, "Tour Completed - Declined")
    ),
    (
        /* Tour Coupleted By Sales User OR Tour Completed By Operations must NOT be false */
        NOT(Tour_Completed_By_Sales_User__c) ||
        NOT(Tour_Completed_By_Operations__c)
    )
)

Hope this helps!
Danielle Pratola 5Danielle Pratola 5
Thank You @Philip!  That helps a bit... maybe I can work it a bit more from there.  I'm getting an error on the NOT(Tour_Completed_By_Sales_User__c) bc it's a lookup field, which is why my logic was like that. i was completely overlooking the check true part though.  Going to play with this... I'll be back!
Philip NelsonPhilip Nelson
Ah, yes, missed that... sorry about that.

Then you should be able to do this:


/* The following the things must be true*/
AND(
    (
        /* Division must be Florida */
        CreatedBy.Division = "Florida"
    ),
    (
        /* Tour Status must be a completed stage */
        ISPICKVAL(Tour_Status__c, "Tour Completed - NPA") ||
        ISPICKVAL(Tour_Status__c, "Tour Completed - Requests follow up call") ||
        ISPICKVAL(Tour_Status__c, "Tour Completed - Wants to think about it") ||
        ISPICKVAL(Tour_Status__c, "Tour Completed - Declined")
    ),
    (
        /* Tour Coupleted By Sales User must have a value OR Tour Completed By Operations must NOT be false */
        ISBLANK(Tour_Completed_By_Sales_User__c) ||
        NOT(Tour_Completed_By_Operations__c)
    )
)

If this solves your issue, please take a moment to mark a post as "Best Answer" to help others in the community with similar questions. Thank you.
 
Danielle Pratola 5Danielle Pratola 5
Thanks again for the response Philip - it's the strangest thing, when I remove the Sales User logic and just test for the operation user checkbox I can get it to fire off properly.  Same thing vice versa, removing the ops user checkbox and just testing the sales user logic it fires off properly.  When I try to use both in an 'or' statement it fires improperly.  I'm going to (try to) step away from it for the weekend and start fresh again Monday.  I'll keep you posted!
Philip NelsonPhilip Nelson
Yeah, I think you are finding out the difference betwen the OR function and the OR operator (||), which I didn't think about either.

The OR operator (||) works by checking the first condition looking for TRUE, and if it finds it, skips checking the second condition. It only checks the second condition if the first condition is FALSE. This is sometimes referred to a short-circuit operator.

The OR function ( OR(condition1,condition2) ) checks both conditions and returns true if any of them are TRUE and returns FALSE only if both of the conditions are FALSE.

So, here's the correction including changing the tour stage check just to be consistent and removing some extra parenthesis:

/* The following the things must be true*/
AND(
    /* Division must be Florida */
    CreatedBy.Division = "Florida"
    ,
    /* Tour Status must be a completed stage */
    OR(
        ISPICKVAL(Tour_Status__c, "Tour Completed - NPA"),
        ISPICKVAL(Tour_Status__c, "Tour Completed - Requests follow up call"),
        ISPICKVAL(Tour_Status__c, "Tour Completed - Wants to think about it"),
        ISPICKVAL(Tour_Status__c, "Tour Completed - Declined")
    ),
    /* Tour Coupleted By Sales User must have a value OR Tour Completed By Operations must NOT be false */
    OR(
        ISBLANK(Tour_Completed_By_Sales_User__c),
        NOT(Tour_Completed_By_Operations__c)
    )
)

Let me know how this works!
Danielle Pratola 5Danielle Pratola 5
Philip!  Thank You SO much for the explanation around the 'OR' function.  That was helpful to learn.  Pasting below what what my full rule looks like at this point.  The syntax saves fine, but runtime error is popping up on the front end.  SF is requiring a a value in both of those fields when I try to save the record, a value in either or does not allow a save.  I think we're close!  Going to play more... maybe it's the 'NOT'!? 

AND((CreatedBy.Division ="Florida"),
(ISPICKVAL(Tour_Status__c, "Tour Completed - New patient acquisition")||
ISPICKVAL(Tour_Status__c, "Tour Completed - Requests follow up call")||
ISPICKVAL(Tour_Status__c, "Tour Completed - Wants to think about it")||
ISPICKVAL(Tour_Status__c, "Tour Completed - Declined")),
OR(ISBLANK(Tour_Completed_By_Sales_User__c),
NOT(Tour_Completed_By_Operations__c)))
Philip NelsonPhilip Nelson
Yeah, that's makes sense... ugh, I hate double-negatives!

So, I think it's time to leave the OR behind and just use a IF formula:

AND(
    (
        CreatedBy.Division ="Florida"
    ),
    (
        ISPICKVAL(Tour_Status__c, "Tour Completed - New patient acquisition")||
        ISPICKVAL(Tour_Status__c, "Tour Completed - Requests follow up call")||
        ISPICKVAL(Tour_Status__c, "Tour Completed - Wants to think about it")||
        ISPICKVAL(Tour_Status__c, "Tour Completed - Declined")
    ),
    IF(ISBLANK(Tour_Completed_By_Sales_User__c),
        IF(NOT(Tour_Completed_By_Operations__c),
            true,
            false,
        )
        true,
    )
)