Validation rule for storing the record in entered any one value in given fields

Question

My Validation Rule:

AND ( ISBLANK ( Fax ) && ISBLANK ( Phone ) && ISBLANK ( Website ) )

Here I done the validation rule for to stored the record to the account if minimum any one of the value in fax, Phone and Website is not blank.

here I also able to given more then one value in the mentioned three field it's allow to save the record.(even it's values are given all mentioned three field's also)

If I give all fields in the blank in the record it's provide the error instant of storing record.

But Now I want to change the validation rule for the following condition:

1.If the record is allow to stored in account when only any "one" value is not blank in the fax, phone and website field in the account.

2.If I entered the more then one value(if two values given like fax and website or website and phone in like pair) in the above mentioned three field (fax, phone and Website) then it's not allow to save the record and it's given an error.

3.If I entered all the value to the record in the above mentioned field (fax, phone, website) then it's also not allowed to save the record and produce the error.

4.If I doesnot entered the any value to the (fax, Phone and website) then it's also produce the error to try to save the record.

For Answer's Thanks in advance please help me to solve this task.

Answer 1

So if I'm correct, the only allowed scenario is that (any) 1 of these 3 fields is filled and the other 2 are empty.

You could of course hard-code each of these 3 allowed scenario's, and then put a NOT in front of it:

NOT(
AND ( NOT(ISBLANK ( Fax )) && ISBLANK ( Phone ) && ISBLANK ( Website ) )
,
AND ( ISBLANK ( Fax ) && NOT(ISBLANK ( Phone )) && NOT(ISBLANK ( Website ) ))
,
AND ( ISBLANK ( Fax ) && ISBLANK ( Phone ) && NOT(ISBLANK ( Website ) ))
)

But this doesn't look very neat nor is it very maintenance-friendly. I'd suggest to go with a bit more flexible approach, like this:

(
   IF(ISBLANK(Fax),0,1)
   +
   IF(ISBLANK(Phone),0,1)
   +
   IF(ISBLANK(Website),0,1)
)
<> 1

So for everything that is not-blank, add 1. If the total's not 1 then raise an error.

Answer 2

OR( 


AND(NOT(ISBLANK ( Fax ))&& NOT(ISBLANK (Phone ))),
AND(NOT(ISBLANK ( Phone )) && NOT(ISBLANK (Website))),
AND(NOT(ISBLANK (Website)) && NOT(ISBLANK ( Fax ))), 
AND ( ISBLANK ( Fax ) && ISBLANK ( Phone ) && ISBLANK ( Website ) ) 

)

Here I tested to the each condition for the requirement of the question and I joined all the condition to the AND notation so It's provides the value if both are true only.

And the all AND notation are connected to the OR notation so it's to be test to the each condition if any one is to be true it provide the output.