cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Nintex Newbie

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!

9D370F7F-4889-4825-8E2F-A2983A544C55.jpeg

Labels: (1)
Tags (2)
0 Kudos
Reply
2 Replies
Nintex Newbie

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

Hi,

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

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

 

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

0 Kudos
Reply
Highlighted
Nintex Employee
Nintex Employee

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

Hi,

 

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
Reply