Formula to require any 2 out of 6 fields, lookup and single line text

We 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!


0 Kudos
2 Replies

Re: Formula to require any 2 out of 6 fields, lookup and single line text


I came up with this solution.

For each lookup and single line of text control, add a calculated value with a formula as such:






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.



2020-05-26 06_48_43-Nintex Forms Designer - f - Item - Internet Explorer.png



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

0 Kudos
Nintex Employee
Nintex Employee

Re: Formula to require any 2 out of 6 fields, lookup and single line text



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.

0 Kudos