You need to sign in to do that
Don't have an account?
Danielle 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
)))
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
)))
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!
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.
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!
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)))
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,
)
)