0

I am trying to create a validation rule that is triggered when more than one of the three fields is filled in. So far I have created one which doesn't run as long as at least one field is filled in... but I want to restrict this to only one field.

AND(
 ISBLANK(Field1__c), 
 ISBLANK(Field2__c),
 ISBLANK(Field3__c)
)

How can I rewrite this validation rule to say either none or one of those fields can be filled in.

Improve this question

3 Answers 3

Reset to default
3

Use the following formula:

(
 IF(ISBLANK(Field1__c),1,0)
 + IF(ISBLANK(Field2__c),1,0)
 + IF(ISBLANK(Field3__c),1,0)
) < 2
Improve this answer
0

What is needed here is to check each field, is it's not empty check if the other two fields are empty. As soon as one is not empty the validation should fail.

Following formula should get you on your way:

IF(NOT(IsBlank(Field1__c)) , 
OR(NOT(IsBlank(Field2__c)),NOT(IsBlank(Field3__c))), 

IF(NOT(IsBlank(Field2__c)) , 
OR(NOT(IsBlank(Field1__c)),NOT(IsBlank(Field3__c))), 

IF(NOT(IsBlank(Field3__c)) , 
OR(NOT(IsBlank(Field1__c)),NOT(IsBlank(Field2__c))), 
true 
) ) )

To put it in words:

If field1 is not empty -> check if field2 or field3 is also not empty

If field2 is not empty -> check if field1 or field3 is also not empty

If field3 is not empty -> check if field2 or field1 is also not empty

else none of the fields is set -> so fail because exactly one has to be filled in.

You can even simplify the logic because in the second step you already know that field1 is empty. So a simple check that field3 is empty is sufficient.

The third check is in this case redundant as field1 and field2 are already empty (previous checks told you so). Simply put false in case field3 is filled in.

So simplified:

IF(NOT(IsBlank(Field1__c)) , 
OR(NOT(IsBlank(Field2__c)),NOT(IsBlank(Field3__c))), 

IF(NOT(IsBlank(Field2__c)) , 
NOT(IsBlank(Field3__c)), 

IF(NOT(IsBlank(Field3__c)) , 
false, 
true 
) ) )
Improve this answer
1
  • This solution won't scale very well in terms of compile size. Note also that you can always replace any instance of IF(condition, true, false) with simply condition, and IF(condition, false, true) with NOT(condition). You should strive for as small a compile size as you can, all else being equal.
    – Adrian Larson ♦
    Commented Dec 19, 2016 at 20:49
0

(ISBLANK( City__c ) && ISBLANK( Country__c ) && NOT(ISBLANK( Street__c ))) || (NOT(ISBLANK( City__c )) && ISBLANK( Country__c ) && ISBLANK( Street__c )) ||(ISBLANK( City__c ) && NOT(ISBLANK( Country__c )) && ISBLANK( Street__c )) ||(ISBLANK( City__c ) && ISBLANK( Country__c) && ISBLANK( Street__c ))

Improve this answer
1
  • 1
    Welcome to SFSE! Please take the tour and read How to Answer. Right now, this answer is what we call a code dump, which tend not to make great answers. Explaining how the solution works is just as important as the solution itself. The goal is to help people understand the issue at hand so that they (and others) can work through similar issues in the future.
    – Derek F
    Commented May 12, 2020 at 11:18

You must log in to answer this question.

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