Filtered Drop Down Lists 101

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)

image

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.

image

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

image

You should now have a form that looks like this

image

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

image

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

image

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.

image

image

Click the “Add” button to create a new data filter.

image

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.

image 

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.  image

image

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

image 

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>

25 thoughts on “Filtered Drop Down Lists 101

  1. I’d love to see the sample for use with web based forms. I know that filtering is non-supported in web based forms and I’ve seen some samples use quite complex code behind files, but I can’t imagine that is the only way to do that.

    Is there another less complex method?

  2. Hi- great post…

    BUT – i have a question/challenge:

    I have cascading/drop-down with unique filtering working just fine.

    but when i try to replicate the same into a repeating table, the filtering doesnt work…

    Any hints?

  3. Ben,

    I’ve followed your directions and I get a “Infopath has encountered an error. The operation failed. Unspecified error” message at the final step of setting up the data connection for the MAKE drop-down list. I’ve setup my CarList.xml file as specified.

    I’m using Infopath 2007. Any thoughts. Unfortunately I can’t attach the screenshot for you.

    Thanks

  4. Can you be a bit more specific with the data structure required for InfoPath2003? You mention it with the unique name feature that’s not available. I was able to get the first pull down to take unique names, but then I get all choices in the second field. I’ve tried filter make = make focusing on changing the left side object. I haven’t found any combination (main or secondary) that works.

  5. A problem here is that when a value is changed in a dropdown it doesn’t remove (or clear) the options selected in the other boxes that are not values associated with the new value selected. So you would have to somehow clear the lists when values are changed. Or am I missing something here?

  6. Hey Jon,
    InfoPath 2003 doesn’t provide the “Display only Unique entries” feature. With this in mind you will need to structure your data similar to the following


    Holden
    Ford
    Porche


    XR6
    XR8
    Focus
    Boxter
    Astra

    Red Blue Silver Black

  7. Hey Patrice,
    The best way to clear your previously selected options, is to use a rule on your drop down list that when fired clears any cascading drop down lists.
    If you need more details on how to this feel free to let me know

    Cheers,

  8. Lori
    The principal is still the same, however rather than filtering the results in a web service you will need to add code to the form to do the filtering for you.
    If you have a large result set potentially comming back it might be best to use a Web Service as an intemediate step to reduce the data being returned.

    Hope this helps

  9. How can I submit all the repeating table data to a sharepoint list ? I have a submit set up, but only the first lines gets submitted.

  10. I need an answer for th first post please

    “I’d love to see the sample for use with web based forms. I know that filtering is non-supported in web based forms and I’ve seen some samples use quite complex code behind files, but I can’t imagine that is the only way to do that.

    Is there another less complex method?”

  11. Ben,

    Hope you can help as I’m a bit stuck!!

    I’m fairly ok at InfoPath supported by jscript etc. I’ve developed a form that has repeating sections which basically ask questions with dropdown list answers. The list is populated from an external xml file (created, managed & maintained as another Ip form) The repeating section (in the main form) is constructed on a button command. the repeating section has a variable called counter with a corresponding variable in the external file. On this basis I populate the repeating section dropdown (from a repeating table in the external file) using something like :-

    externaldatasource/answers[local counter = external counter]

    answers is what appears in the local dropdown list.

    This nearly works fine, however, if there’s an answer in section[2] that appears in section[1] then section[1] data is displayed.
    Sorry please bear with me! for example if section[1] (local)dropdown (popultated from external xml) has Yes & No & section[2] has Yes & Maybe – then the Maybe is ok i.e. I can reliably get the other external repeating table data, but the Yes answer returns the associated data relating to section[1]. It’s feels like the data is cached in someway.

    Local
    – repeating
    counter
    question
    Answer – string
    desirability – attribute of answer

    External
    – repeating
    counter (cut across from external using jscript on button command)
    question
    – repeating
    Answer
    Desirability

    In essence the external holds the question, the possible answers and corresponding desirability that i want the local file to read.

    I understand .not, preceding, forward sibling etc, using none of which seem suitable. It’s like I want to purge any cached data on the reading of data in each local section[n].

    Hope this understandable/reproducable and fixable. I’ve searched forums etc. to no avail. – you fix this and i’ll arrange for Aus to win the Ashes!

    terryboulter@aol.com

  12. Sorry for wasting anyone’s time who tried to understand earlier post – problem now resolved – error on my part. It’s the use of current() that I hadn’t quite got correct.

  13. very helpful..but how about implementing these (Make, Model and Color) using sharepoint? You only provided a tutorial on XML..i have a problem on what to put on my list in sharepoint. 🙂

  14. It is extremely interesting for me to read the blog. Thanks the author for it. I like such themes and everything connected to this matter. I definitely want to read a bit more soon.

  15. I have the same question as Patrice: A problem here is that when a value is changed in a dropdown it doesn’t remove (or clear) the options selected in the other boxes that are not values associated with the new value selected. So you would have to somehow clear the lists when values are changed.

    I’m not sure how to create rule on drop down list that when fired clears any cascading drop down lists.
    I need some details how to do that
    Thnx!

  16. I understand this post is pretty old but what the hell maybe someone is still monitoring?? This is good stuff, I however, want to filter only one data source LastName so that I can have a dropdown box show all the LastNames in my table, this way users can press “B” and it will show all the B last names. Once the user selects the right name the other textbox fields in the form autopopulate from the other records associated with the selected last name… I appreciate your direction.
    Jeff

  17. Hi Ben – Thsi is quite an old blog post but some issues stil remain.. atleast so it seems to me..

    For example:
    When values on 1 set of Parent and respective children dropdown have values selected, it gets tricky when the parent value changes.

    The display value is wiped out but the value ID which is sitting behind the child control display cannot be wiped out.. it remains and shows up as a number.

    Any solution to this so that when the parent value is modified, the child dropdown is set to blank value?

    I hope the question is clear.

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *