Filter a Repeating Drop-Down List on Previous and Future Selections - Matt Faus
in

InfoPath Dev

Matt Faus

Filter a Repeating Drop-Down List on Previous and Future Selections

It is likely that you will someday need to create a form that allows only one selection of an item even though the field is held in a repeating table. One example of this is a form that handles seating arrangements at a music hall. As seats are bought you should not be able to select the seats that have already been sold out.

In this task we will create a simple form that has a repeating drop-down list that is populated from another repeating table in the form. After the appropriate filters are applied each instance of this drop-down list will only display selections that have not already been made from other rows in the form. To start, let's create a new blank form.

Create the form:

  1. Open InfoPath, select Design a Form, and then select New Blank Form from the Design a Form task pane.
  2. From the controls task pane, select repeating table, change the number of columns to one, and then click OK.
  3. Type List of Seats in the header of the table, and then resize the table to 250px from the Table Properties dialog box.
  4. Press Enter twice to enter some whitespace, and then type Record of Sold Seats:.
  5. From the controls task pane, select repeating table, make sure the number of columns is three, and then click OK.
  6. Type Seat in the first column's header, First Name in the second column's header, and then type Last Name in the third column's header. Refer to figure 1.


Figure 1. The form design.

Revise the schema for clarity:

As you were inserting the controls into the form you might have noticed that InfoPath automatically generated an XML Schema to map these controls to. We will now modify this schema to be easier to understand.

  1. Open the data source task pane, double-click the node labeled group1, rename the node to Seats, and then click OK.
  2. Using Figure 2 as a guide, rename the rest of the nodes with the following values:
    • group2 = Seat
    • field1 = SeatName
    • group3 = SoldTickets
    • group4 = SoldTicket
    • field2 = SeatSold
    • field3 = FirstName
    • field4 = LastName


Figure 2. Renaming the schema.

Add the filtered drop-down list:

  1. Right-click the textbox control in the first column of the second table, hover over Change To, and then select Drop-Down List Box from the fly out menu.
  2. Now that we have a drop-down list, double-click the control to open the Drop-Down List Box Properties dialog box.
  3. In the List Box Entries section, select the option to Look Up Values In The Form's Data Source, and then click the XPath button to the right of the Entries field.
  4. From the box that displays select the myFields/Seats/Seat node, and then click the Filter Data button in the bottom left corner.
  5. Click the Add button, select The Expression from the first drop-down list, delete everything in the textbox and insert this expression:

not(my:SeatName = current()/preceding-sibling::my:SoldTicket/my:SeatSold)

  1. Click the And >> button, select The Expression from the first drop-down list again, delete everything from the textbox and then replace with this expression:

not(my:SeatName = current()/following-sibling::my:SoldTicket/my:SeatSold)

  1. Click OK on all open dialog boxes until you return to the view.


Figure 3. Specifying the filter conditions.


Figure 4. Setting up the drop-down list.

Try it:

  1. Open the form in Preview Mode and type values into the List of Seats table.
  2. Select a seat from the first row, insert a new row, and then select another seat.
  3. Notice that only options that have not been selected are displayed for selection. Cool, huh?


Figure 5. Using the form.

Further Considerations:

One further aspect to consider about this scenario is how you want to handle the situation when you insert the last possible row in the second table. You should not allow the user to insert any additional rows but should allow them to delete previous rows. There are a few ways to solve this problem, including using two instances of the same table with conditional formatting and using a custom button to control the insertion and deletion of rows. These techniques and the application of their use to your problem domain are left as an exercise for the reader.

Published May 23 2006, 05:35 PM by Matt Faus
Filed under: ,

Comments

 

nikopol said:

Hello,

I'm very interested in this method and I would like to do the same thing using a drop-down list populated by an Access database (instead of a repeating table). I just want that it's not possible to choose an item already chosen.

Is it possible? I've tried a few handlings without any result (my attemps filter all or nothing :s)

Thanks very much in advance ;)

Nicolas.

April 17, 2007 8:10 AM
 

nikopol said:

Re-hello,

I've finally found the solution here : http://blogs.msdn.com/infopath/archive/2006/08/10/694530.aspx

See you.

Nicolas.

April 18, 2007 6:34 AM
 

Default value in repeating table | keyongtech said:

Pingback from  Default value in repeating table | keyongtech

January 18, 2009 9:34 AM
 

filtering in infopath « Dot Net Solutions said:

Pingback from  filtering in infopath « Dot Net Solutions

December 8, 2010 8:29 AM
 

Mel Balsamo said:

When designing an InfoPath form that integrates with a secondary data source such as a SharePoint list

October 16, 2011 2:19 PM

About Matt Faus

Matt holds a BS in Computer Science and Software Engineering from the University of Texas at Dallas. During his studies, he helped Qdabra Software develop DBXL and InfoPath solutions. During that time he lived in Dallas, Seattle, and Valencia, Spain. He now works at Microsoft, developing for Hotmail.
Copyright © 2003-2018 Qdabra Software. All rights reserved.
View our Terms of Use.