Ben Walters InfoPath MVP

A couple of weeks ago I received an email from Dominic asking for help with creating filtered drop down lists in InfoPath. So I thought I would put together a couple of posts on how to achieve this functionality in both InfoPath 2003 and InfoPath 2007.

For this post I'm going to look at how to filter drop down data while using the InfoPath client, as filtering of data is not supported in browser based forms I will cover this in a separate post.

The first thing we are going to need is some data to filter, at the end of this post is a list of cars with their makes and models that I have used for this example. Simply take the data listed below and paste it into a new XML file called CarList.xml

So with our data taken care of it's time to create our form. Now for these examples I'm going to be using InfoPath 2007 however the same principals ring true when filtering data in InfoPath 2003. Open InfoPath and design a new form, match the data source to the one displayed below (note: the data type for each of the fields is simply text)

With the car repeating section created right click on the car group and select "Repeating table" to add a new repeating table to the form design.

Right click on the Make, Model, and Color text boxes and change them to Drop Down Lists

You should now have a form that looks like this

With our three drop down lists created we now need to fill them with data, open the properties dialog for the Make drop down list and select the "Look up values from an external data source" option.

Click on the Add button to create a new external data connection,

• Select Receive Data Connection Click next
• Select XML document then click next
• Navigate to the XML Document you created using the Car Data below then click next
• The select "Include the data as a resource file in the form template or template part option" then click next
• Name your data source "CarList", Make sure the "Automatically retrieve data when the form is loaded is check then click Finish

You should now have a properties dialog that looks similar to the following

Click on the button next to the Entries: text box to select the data you want displayed in the drop down you will be presented with the field selector dialog, as we don't need to filter any of the Make data you can select the make node and then click ok

Make sure the show only entries with unique display names is checked (note: this feature is only available in InfoPath 2007, this means that you will need to structure your data slightly differently for an InfoPath 2003 form) then click the OK button to close the properties dialog for the "Make" drop down.

now comes the fun part setting up the filtered drop down lists, open the properties dialog for the "Model" drop down, and select the option to source data from an external data source (you will be able to use the existing connection to the CarList.xml file that we made earlier)

Click on the entries button to select the data to be displayed and select the Model field, before clicking ok click on the "Filter Data" button.

Click the "Add" button to create a new data filter.

Creating the filter is pretty straight forward, the first Drop Down represents the field that you are comparing, this case it is the "Make" node from our CarList.xml file. The second drop down represents the equation type that will be used Equal to, Not Equal to etc... The third and final drop down represents the field you want to compare to, in this example it's the "Make" field in our forms data source. When you click on the "Select a field or group..." option you will be able to select the "Make" field from the main data source.

Click ok to close the "Select a Field or Group" dialog, and click OK on each of the remaining open dialogs to close them and commit their changes.

Now if we click on the preview button on our form we should see the filtered drop downs working. 

The added bonus is that because our filtering is based on the current item it works just as effectively in a repeating list.

For now this is where I leave you, you'll notice however that there is one more drop down in the form that has not been setup, I figured I would leave the Colour drop down for you to setup. Basically all you have to do is filter the Colour drop down data based on the selection in the Model drop down. Next time I'll show you how to achieve the same functionality in a web based form

Car list XML Sample

<CarList>
  <Cars>
    <Car>
      <Make>Ford</Make>
      <Model>XR6</Model>
      <Color>Blue</Color>
    </Car>
    <Car>
      <Make>Ford</Make>
      <Model>XR6</Model>
      <Color>Red</Color>
    </Car>
    <Car>
      <Make>Ford</Make>
      <Model>XR6</Model>
      <Color>Yellow</Color>
    </Car>
    <Car>
      <Make>Holden</Make>
      <Model>Commodore</Model>
      <Color>Green</Color>
    </Car>
    <Car>
      <Make>Holden</Make>
      <Model>Commodore</Model>
      <Color>Orange</Color>
    </Car>
    <Car>
      <Make>Holden</Make>
      <Model>Commodore</Model>
      <Color>Aqua</Color>
    </Car>
    <Car>
      <Make>Ford</Make>
      <Model>Focus</Model>
      <Color>Black</Color>
    </Car>
    <Car>
      <Make>Ford</Make>
      <Model>Focus</Model>
      <Color>Light Blue</Color>
    </Car>
    <Car>
      <Make>Ford</Make>
      <Model>Focus</Model>
      <Color>White</Color>
    </Car>
    <Car>
      <Make>Holden</Make>
      <Model>Astra</Model>
      <Color>Pink</Color>
    </Car>
    <Car>
      <Make>Holden</Make>
      <Model>Astra</Model>
      <Color>Cornflower Blue</Color>
    </Car>
    <Car>
      <Make>Toyota</Make>
      <Model>Camry</Model>
      <Color>White</Color>
    </Car>
    <Car>
      <Make>Toyota</Make>
      <Model>Camry</Model>
      <Color>Gold</Color>
    </Car>
    <Car>
      <Make>Toyota</Make>
      <Model>Camry</Model>
      <Color>Red</Color>
    </Car>
    <Car>
      <Make>Porsche</Make>
      <Model>Boxter</Model>
      <Color>Red</Color>
    </Car>
    <Car>
      <Make>Porsche</Make>
      <Model>Boxter</Model>
      <Color>Blue</Color>
    </Car>
    <Car>
      <Make>Porsche</Make>
      <Model>Boxter</Model>
      <Color>Black</Color>
    </Car>
    <Car>
      <Make>Porsche</Make>
      <Model>Carrera</Model>
      <Color>White</Color>
    </Car>
    <Car>
      <Make>Porsche</Make>
      <Model>Carrera</Model>
      <Color>Yellow</Color>
    </Car>
  </Cars>
</CarList>