+2We are on prem 2013, and I have 1 lookup list with three separate columns looking up to that one list. I also have three separate single line of text fields to allow users to choose at up to six Vendor options. We want users to pick at least two - - any two. I need a formula that would require two out of the six fields. See image. Thanks!
+8Hi,
I came up with this solution.
For each lookup and single line of text control, add a calculated value with a formula as such:
if(isNullOrEmpty(prefVen1),0,1)
This will populate the field with either 0 (if empty) or 1.
Add another calculated value which sums all the 0 and 1s up.
To the sum field, add a validation rule so that it is invalid if its value is below 2.
Screenshot
EDIT:
Just discovered that you can sum up without using all the IFs, making the formula for the sum field like this:
!isNullOrEmpty(prefVen1) + !isNullOrEmpty(prefVen2) + !isNullOrEmpty(prefVen3) //and so on
You could also take above formula and put it in a validation rule directly
This way you don´t need to add all calculated values for each control
+23Hi,
This was a little tricky but I found a solution.
Advanced users would probably use JavaScript on a classic form.
The only way I know how to count controls that are not empty is to use the count() but this only applies to Repeating sections.
So I created 2 repeating sections. One for the lookup and one for the single lines of text and set them to default to 3 rows and made them so that rows could not be added or deleted.
I then added a Calculated value control that summed up the populated controls.
Then set a validation rule on the calculated control to say that the sum could not be less than 2.
I attached a sample form for you to test. I used a Choice control instead of a lookup for testing and POC but it should still work with a lookup control.
This has less controls and calculations than the above solution so will be more efficient.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
