Repeating, Multiply-Cascading, Dropdown Listboxes with Unique Entries in InfoPath

Article
07/29/2004

First, a few definitions:

Repeating: Inside of a Repeating Section or Repeating Table control

Cascading Dropdown Listboxes: This is where the selection in one dropdown listbox causes the available items in another dropdown listbox to be updated. For example, If I choose “Ford” for the “Make: “ dropdown listbox, the “Model: “ dropdown listbox will be updated to only show vehicles made by Ford.

Multiply-Cascading Dropdown Listboxes: That's pronounced mull-tih-plee, and is a term that I made up.This is similar to the previous concept, but this allows you to choose an item from any one of multiple dropdown listboxes, and it will filter out the values in all other dropdown listboxes.

Unique Entries: Any duplicate items for the dropdown listbox will be filtered out.

Ok, now down to business:

The scenario: You have a photo album that allows viewing photos in groups based on selections in dropdown listboxes. You have information about each photo's title, date, location, and web URL. When you select a value from one of the dropdowns (say, location = “Denver“) then you want all the other dropdowns to filter themselves

First, let's describe an XSD file which you will base your form off of. (photoalbum.xsd):

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xsd:schema targetNamespace="schemas.microsoft.com/office/infopath/2003/myXSD/2000-01-01T12:00:00" xmlns:my="schemas.microsoft.com/office/infopath/2003/myXSD/2000-01-01T12:00:00" xmlns:q="schemas.microsoft.com/office/infopath/2003/ado/queryFields" xmlns:d="schemas.microsoft.com/office/infopath/2003/ado/dataFields" xmlns:dfs="schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:xsd="www.w3.org/2001/XMLSchema">
     <xsd:element name="myFields">
          <xsd:complexType>
               <xsd:sequence>
                    <xsd:element ref="my:PhotoAlbum" minOccurs="0"/>
               </xsd:sequence>
               <xsd:anyAttribute processContents="lax" namespace="www.w3.org/XML/1998/namespace"/>
          </xsd:complexType>
     </xsd:element>
     <xsd:element name="PhotoAlbum">
          <xsd:complexType>
               <xsd:sequence>
                    <xsd:element ref="my:Photo" minOccurs="0" maxOccurs="unbounded"/>
               </xsd:sequence>
           </xsd:complexType>
      </xsd:element>
      <xsd:element name="Photo">
           <xsd:complexType>
                 <xsd:sequence>
                      <xsd:element ref="my:Title" minOccurs="0"/>
                      <xsd:element ref="my:Location" minOccurs="0"/>
                      <xsd:element ref="my:WebSite" minOccurs="0"/>
                  </xsd:sequence>
            </xsd:complexType>
     </xsd:element>
     <xsd:element name="Title" type="xsd:string"/>
     <xsd:element name="Location" type="xsd:string"/>
     <xsd:element name="WebSite" type="xsd:string"/>
</xsd:schema>

Use this schema, and create a new form from Data Source.

Right click on the Photo node in your data source, and insert a Repeating Table control.

Right click on each of the textboxes in the table columns, and convert them to dropdown listboxes.

Now that you've gotten your form layout mostly node, let's add an XML file that will serve as the data source for the items in each of the dropdown listboxes:

Here is some sample XML to use (Event.xml):

<?xml version="1.0"?>
     <PhotoAlbum>
           <Event Title="My Dog" Date="2004-10-31T12:00:00" Location="Redmond" Website="www.images.com/Dog" />
            <Event Title="My Parents" Date="2002-07-04T12:00:00" Location="New York" Website="www.images.com/Parents" />
           <Event Title="My School" Date="2001-01-01T12:00:00" Location="Denver" Website="www.images.com/School"/>
           <Event Title="My Car" Date="2000-01-01T12:00:00" Location="New York" Website="www.images.com/Car"/>
            <Event Title="My Ski Trip" Date="2005-01-01T12:00:00" Location="Denver" Website="www.images.com/Ski"/>
</PhotoAlbum>

Save this file somewhere, and then add it to your form as a resource (Tools->Resource Manager). Name the secondary data source “Event“

Now you can hook up the dropdown listboxes so that their items come from the “Event” data source.

Now is where it gets a bit ugly. InfoPath doesn't have UI to create these magical cascading dropdown listboxes. However, you can edit the current view's xsl file in order to achieve the desired effect. In order to edit the XSL file, you'll need to Extract (File->Extract Form Files) into a folder. Once you have your form extracted, you can edit the view1.xsl file like so:

<select class="xdComboBox xdBehavior_Select" title="" style="WIDTH: 100%" size="1" xd:CtrlId="CTRL7" xd:xctname="DropDown" value="" xd:boundProp="value" xd:binding="my:Title" tabIndex="0">
     <xsl:attribute name="value">
          <xsl:value-of select="my:Title"/>
     </xsl:attribute>
     <xsl:choose>
          <xsl:when test="function-available('xdXDocument:GetDOM')">
               <option/>
               <xsl:variable name="val" select="my:Title"/>
<xsl:variable name="loc" select="my:Location"/>
<xsl:variable name="web" select="my:WebSite"/>
               <xsl:for-each select="xdXDocument:GetDOM("Event")/PhotoAlbum/Event/@Title[((../@Location = $loc) or $loc ='') and ((../@Website = $web) or $web ='') and not(.=../preceding-sibling::*/@Title)]">
                    <option>
                         <xsl:attribute name="value">
                              <xsl:value-of select="."/>
                         </xsl:attribute>
                         <xsl:if test="$val=.">
                              <xsl:attribute name="selected">selected</xsl:attribute>
                         </xsl:if>
                         <xsl:value-of select="."/>
                    </option>
               </xsl:for-each>
          </xsl:when>
          <xsl:otherwise>
               <option>
                    <xsl:value-of select="my:Title"/>
               </option>
          </xsl:otherwise>
     </xsl:choose>
</select>

The edits you'll need to make are in red. There are also two other places (for the other two dropdown listboxes) where you'll need to make similar changes. I will leave that as an excercise for the reader. Here's an XSLT reference, for those of you who actually made it this far :)

More bad news... The InfoPath Designer has a tendency to overwrite any changes you make to the xsl. The way to keep your hand-edited xsl from being overwritten is to use an xd:preserve template.

The good news is that once you've done all this, you have fully achieved your goal of having repeating, multiply-cascading dropdown listboxes with uniqe entries., and you didn't have to write a single line of code to get them (some might argue that XSLT is worse to deal with than code, but that's another matter!)

This may be a lot easier using the Filters feature in InfoPath SP1, but I do recall that the SP1 Filters feature does not include showing only unique entries in a dropdown listbox.

Don't feel like messing with all this, but you still want to see what it looks like? Shoot me an email, and I'll send you a copy of the form.

Repeating, Multiply-Cascading, Dropdown Listboxes with Unique Entries in InfoPath

Additional resources