Enforcing unique values in a repeating list


Have you ever created a form which allows the user to choose items from a list and you wanted to make sure the user doesn’t choose the same item twice?  If you’ve got InfoPath 2007 you can use the new Multi Select List Box, but if you’ve got InfoPath 2003, you’re still in luck, this blog entry is for you!

 

Note: You should be familiar with XPath expressions before preceding.

 

Let’s start with a repeating table with a dropdown control bound to a secondary data source.

 


 

There are a few choices when it comes to enforcing unique values selected by the user:



  1. Only show values that have not already been selected by the user.


  2. Show a validation error when the user selects something which has already been selected.


  3. Write code using the Changing event.

 

This blog entry will cover both options 1 and 2.

 

Option 1 – Only show values that have not already been selected by the user

 

Only showing certain values implies that a filter is being applied to the dropdown.  To apply a filter, click the Filter Data… button when selecting the entries for the dropdown to show the condition builder.  Unfortunately, there is no UI in the condition builder to build an expression that means “don’t show anything that is already in the list”.  Thus, we’ll have to construct this manually by selecting The Expression.

 


 

You might be tempted to put the following expression:

. != xdXDocument:get-DOM()/my:myFields/my:items/my:item/my:product

This will return true if any one value from the list matches the current value.  In other words, this condition will always return true if there are two entries in the list which are different.  (Definitely not what we are looking for)

 

The correct condition is a slight adjustment to the former expression:

not(. = xdXDocument:get-DOM()/my:myFields/my:items/my:item/my:product)

This will only return true if none of the values from the list matches the current value.  In other words, only values that are not in the list will be displayed.

 

Preview the form and you will see that the dropdown only lists the products that have not already been selected.

 


 

 

Option 2 – Show a validation error when the user selects something which has already been selected

 

The only way this will work properly is if we add a hidden calculated field along with the product selection.

(The reason for this is a bit too complex to explain here, so it’ll have to wait for another time.)

 


 

The formula for the unique field is the following:

not(../my:product = (../preceding-sibling::my:item | ../following-sibling::my:item)/my:product)

This will set Unique to true if and only if the product isn’t already selected preceding or following the current item.

 

The next and final step is to use data validation on the dropdown to show an error.

 


 

Preview the form and you will see that the dropdown will show a validation error if the items are not unique.

 


 

– Gary

Software Development Engineer

Comments (3)

  1. infopath says:

    Shoutout to Matt Faus and InfoPathDev.com for writing a great article on this subject – http://www.infopathdev.com/howto/tutorials/default.aspx?i=1fe635d7e2cd4c478b7a1eea929153ea

  2. Worked Great says:

    Thanks for sharing!  It would have taken me forever to figure this out.

    Eric Halsey

  3. Sameer Munir says:

    Gr8 Nice Article work for me cheers :)