Create a Repeating Cascading List from a Hierarchical Data Source - Greg Collins
in

InfoPath Dev

This Blog

Syndication

Greg Collins

Create a Repeating Cascading List from a Hierarchical Data Source

It is common to need two or more list boxes where the choices in one are dependant on the value of another. This is known as a cascading list. A cascade can occur with as few as two levels up to any number of levels. Some common scenarios which require cascading lists are:

  • Location: State / City
  • Vehicle: Year / Make / Model
  • School: Year / Semester / Class / Instructor

As the number of levels in the cascade increases, so does the complexity of the process and work involved. Also, if the cascading list is presented inside of a repeating table or repeating section you must account for another issue: data crossover. Data crossover means that the options presented in a cascade on a given row will be affected by earlier selected values from other rows—thus destroying the true cascading effect.

In this task we will create a hierarchical secondary data source containing the values used to populate our lists. We will then create a three-level repeating cascading list using drop-down list boxes. We create three levels of cascade to reveal the compounding complexity involved for level three and beyond. We will resolve the issue regarding data crossover using the current function. And finally, we ensure valid selection combinations by clearing selected list values as selections earlier in the cascade are changed.


THE HIERARCHICAL SECONDARY DATA SOURCE

Hierarchical data sources are commonly created for use in XML, and are typically more structured and organized than a flat data source. There are numerous ways to organize the hierarchy; we have arbitrarily chosen a structure that suits this task. The hierarchical structure normally ensures that only unique values appear each list. If, however, you find that filtering for unique values is still needed, refer to Display Only Unique Options in a List. Let's start by creating the hierarchical secondary data source.

Create the hierarchical secondary data source:

Copy the following code into a text editor, and then save the file as Vehicles - Hierarchical Data Source.xml. This file will be used as a secondary data source in our form.

<?xml version="1.0" encoding="UTF-8"?>
<Vehicles>
    <Year value="1963">
        <Make value="Chevrolet">
            <Model value="Corvette"/>
            <Model value="Nova"/>
        </Make>
        <Make value="Porsche">
            <Model value="356"/>
            <Model value="Carrera"/>
        </Make>
    </Year>
    <Year value="1973">
        <Make value="Ford">
            <Model value="Bronco"/>
            <Model value="Mustang"/>
            <Model value="Pinto"/>
        </Make>
        <Make value="Toyota">
            <Model value="Celica"/>
            <Model value="Land Cruiser"/>
        </Make>
    </Year>
    <Year value="1979">
        <Make value="Jaguar">
            <Model value="XJ6"/>
        </Make>
        <Make value="Pontiac">
            <Model value="Trans Am"/>
        </Make>
        <Make value="Volvo">
            <Model value="240"/>
            <Model value="244DL"/>
        </Make>
    </Year>
    <Year value="1987">
        <Make value="Dodge">
            <Model value="Ram Charger"/>
            <Model value="Grand Caravan"/>
        </Make>
        <Make value="Honda">
            <Model value="Civic"/>
            <Model value="Prelude"/>
        </Make>
        <Make value="Nissan">
            <Model value="300ZX"/>
            <Model value="Pulsar"/>
            <Model value="Sentra"/>
        </Make>
    </Year>
    <Year value="1999">
        <Make value="GMC">
            <Model value="Jimmy"/>
            <Model value="Sierra 2500 HD"/>
            <Model value="Yukon"/>
        </Make>
        <Make value="Honda">
            <Model value="Passport"/>
            <Model value="Prelude"/>
            <Model value="CR-V"/>
        </Make>
        <Make value="Jeep">
            <Model value="Cherokee"/>
        </Make>
        <Make value="Mercury">
            <Model value="Grand Marquis"/>
            <Model value="Sable"/>
            <Model value="Villager"/>
        </Make>
        <Make value="Pontiac">
            <Model value="Firebird"/>
            <Model value="Montana"/>
            <Model value="Sunfire"/>
        </Make>
    </Year>
    <Year value="2003">
        <Make value="Dodge">
            <Model value="Dakota"/>
            <Model value="Caravan"/>
            <Model value="Ram Charger"/>
            <Model value="Viper"/>
        </Make>
        <Make value="Mercury">
            <Model value="Sable"/>
        </Make>
    </Year>
    <Year value="2004">
        <Make value="Dodge">
            <Model value="Stratus"/>
            <Model value="Viper"/>
        </Make>
        <Make value="Ford">
            <Model value="Crown Victoria"/>
            <Model value="F250"/>
            <Model value="Thunderbird"/>
        </Make>
        <Make value="GMC">
            <Model value="Sierra 3500"/>
        </Make>
        <Make value="Jeep">
            <Model value="Grand Cherokee"/>
            <Model value="Liberty"/>
        </Make>
    </Year>
</Vehicles>


THE FORM

Now that we have our hierarchical secondary data source we are ready to create our form. First we will design a simple schema to support a three-level repeating cascading list. Then we will design the layout using drop-down list boxes in a repeating table. When the layout is complete we will focus on creating the repeating cascade.

Add the secondary data source to a form:

  1. Design a new blank form.
  2. Choose Data Connections from the Tools menu, and then click Add.
  3. In the Data Connection Wizard, select Receive Data, and then click Next.
  4. Select XML Document, and then click Next.
  5. Click Browse, locate and select the Vehicles - Hierarchical Data Source.xml file, click Open, and then click Next.
  6. Click Finish, click Yes, and then click Close.

Create the schema:

  1. Open the Data Source task pane.
  2. Add a Group named Vehicles.
  3. Select the Vehicles group, and then add a Repeating Group named Vehicle.
  4. Select the Vehicle group, and then add three Text Fields named Year, Make, and Model.

Design the view:

  1. Right-click Vehicle, and then choose Repeating Table.
  2. Select all three header row cells, and then set the font size to 8.
  3. Right-click each of the three text boxes, and then choose Change To | Drop-Down List Box. The results are shown in Figure 1.


Figure 1. Layout of the repeating cascading drop-down list boxes.

  1. Double-click the repeating table label.
  2. On the Data tab of the Repeating Table Properties dialog box, change the insert button hint text to Insert Vehicle, and then click OK.


THE CASCADING LISTS

With the form layout complete we will now turn our attention to the meat of the task: making one repeating list cascade off of another. The process is not very difficult, but can become increasingly complex as the number of cascade levels increases. Creating a cascade requires the use of filters. The options displayed in one list box are filtered based on values selected in earlier list boxes. Because our cascade is in a repeating structure, we must use the current function to ensure the filter does not crossover to values in other rows. Let's populate each drop-down list box using our secondary data source and create the cascade filters.

Populate the Year drop-down list:

  1. Double-click the Year drop-down list box.
  2. In the Drop-Down List Box Properties dialog box select Look Up Values In A Data Connection.
  3. Select Vehicles - Hierarchical Data Source from the Data Connection drop-down list.
  4. Click the Select XPath button to the right of the Entries text box, select Vehicles/Year/value, and then click OK twice to close the open dialog boxes.

Populate the Make drop-down list:

  1. Double-click the Make drop-down list box.
  2. In the Drop-Down List Box Properties dialog box select Look Up Values In A Data Connection.
  3. Select Vehicles - Hierarchical Data Source from the Data Connection drop-down list.
  4. Click the Select XPath button to the right of the Entries text box, and then select Vehicles/Year/Make/value.

Filter the Make drop-down list based on the selected Year in the current row:

  1. Click Filter Data, and then click Add.
  2. In the Specify Filter Conditions dialog box, select Select A Field Or Group from the first drop-down list.
  3. In the Select A Field Or Group dialog box, select Vehicles/Year/value, and then click OK.
  4. In the second drop-down list, select Is Equal To.
  5. In the third drop-down list box, select Use A Formula.
  6. Type the following formula (as shown in Figure 2):

current()/my:Year


Figure 2. Filtering to compare the Year to the value on the current row.

  1. Click OK five times to close all open dialog boxes.

We mentioned you would encounter the issue of data crossover. An example of data crossover is shown in the difference between Figure 3 and 4. Without using the current function the vehicle make will be filtered on all years currently selected in the repeating table, presenting the user with invalid choice combinations. Thus if you have 3 rows with the years 1963, 1979 and 2004 selected, each of the Make drop-down lists will contain a list of makes for all three of those selected years, as shown in Figure 4. By properly using the current function, we avoid data crossover by limiting the choices in the Make drop-down list to only those of the selected Year on the same row, as shown in Figure 3.


Figure 3. Vehicle makes filtered by year using the current function.


Figure 4. Vehicle makes filtered by year without using the current function.

At this point you have created a two-level repeating cascade. The options in the Make drop-down list are filtered based on the selected Year in the current row. You can test this by previewing your form. Now let's create the level-three repeating cascade, which will reveal the compounding complexity involved in creating each additional level of cascade from here.

Populate the Model drop-down list:

  1. Double-click the Model drop-down list box.
  2. In the Drop-Down List Box Properties dialog box select Look Up Values In A Data Connection.
  3. Select Vehicles - Hierarchical Data Source from the Data Connection drop-down list.
  4. Click the Select XPath button to the right of the Entries text box, and then select Vehicles/Year/Make/Model/value.

Filter the Model drop-down list based on the selected Year in the current row:

  1. Click Filter Data, and then click Add.
  2. In the Specify Filter Conditions dialog box, select Select A Field Or Group from the first drop-down list.
  3. In the Select A Field Or Group dialog box, select Vehicles/Year/value, and then click OK.
  4. In the second drop-down list, select Is Equal To.
  5. In the third drop-down list box, select Use A Formula.
  6. In the Insert A Formula dialog box, type the following formula:

current()/my:Year

  1. Click OK.

Filter the Model drop-down list based on the selected Make in the current row:

  1. Click And to create a second filter condition.
  2. In the first drop-down list of the second filter condition, select Select A Field Or Group.
  3. In the Select A Field Or Group dialog box, select Vehicles/Year/Make/value, and then click OK.
  4. In the second drop-down list, select Is Equal To.
  5. In the third drop-down list box, select Use A Formula.
  6. In the Insert A Formula dialog box, type the following formula:

current()/my:Make

  1. Click OK. The two filter conditions are shown in Figure 5.


Figure 5. Filtering the third level of a repeating cascading list.

  1. Click OK four times to close all open dialog boxes.

You have now created a three-level repeating cascade. Preview the form and test the functionality of the drop-down list boxes. You may discover an issue: you can create invalid selection combinations. We will resolve this issue in the next section.


CLEARING SELECTIONS AFTER CHANGES

One issue with cascading lists that needs to be addressed is that when a selection is changed in an earlier level the selections following that change are likely to be invalid. For example, if you select year 1963 and model Chevrolet, and then select year 1973, Chevrolet remains the selected model, even though our data source does not list it as a model for 1973. The old value needs to be cleared so that invalid selection combinations cannot exist. This can be easily corrected with Rules.

With some extra work, you can probably determine whether the following selected values are still valid and clear only invalid ones, but there are advantages to just clearing each following selected value. One advantage is that rules can cascade as well—like a Domino effect. As one value is changed, the next value is cleared; clearing that value triggers the next value to clear, and so on. Rules automatically affect the current row, so there is no need to filter using the current function in the rule.

The rules we create will have no condition so that they will be processed every time the selected value changes. To clear the selected value of a field, we leave the Value text box of the rule blank.

Clear the selected Make when the selected Year changes:

  1. Double-click the Year drop-down list box.
  2. In the Drop-Down List Box Properties dialog box, click Rules, and then click Add.
  3. Name the rule Clear Make, and then click Add Action.
  4. In the Action dialog box, select Set A Field's Value from the Action drop-down list.
  5. Click the Select XPath button to the right of the Field text box, and then select myFields/Vehicles/Vehicle/Make in the Main data source.
  6. Click OK five times to close all open dialog boxes.

Clear the selected Model when the selected Make changes:

  1. Double-click the Make drop-down list box.
  2. In the Drop-Down List Box Properties dialog box, click Rules, and then click Add.
  3. Name the rule Clear Model, and then click Add Action.
  4. In the Action dialog box, select Set A Field's Value from the Action drop-down list.
  5. Click the Select XPath button to the right of the Field text box, and then select myFields/Vehicles/Vehicle/Model in the Main data source.
  6. Click OK five times to close all open dialog boxes.

Try it:

You have now created a three-level repeating cascading list that prevents invalid selection combinations. Preview the form and try various selection combinations.

©2005 Greg Collins. All rights reserved. Licensed to Autonomy Systems, LLC for display on InfoPathDev.com.

Comments

 

Dropdownlist in a repeating section | keyongtech said:

Pingback from  Dropdownlist in a repeating section | keyongtech

January 18, 2009 8:37 AM
 

woodh said:

Hi,

I need to do something like this, but on a browser-enabled form.  So, filtering the data this way isn't at option.  I know how to filter the data using C# code, but can't figure out how to use the current() function in the code to filter differently on each repeating row. Any help would be great.

Thanks!

July 15, 2009 8:22 AM
 

Create XSD for hierarchical data | Zenor said:

Pingback from  Create XSD for hierarchical data | Zenor

November 20, 2014 10:59 PM
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.