This is a robust way to implement cascading (sometimes called nested) dropdowns in InfoPath 2010, that also works with InfoPath Forms Services in SharePoint 2010. The approach in this article expands upon the demo that I gave at SharePoint Saturday UK on 12 November 2011.
There are numerous other blog articles out there explaining how to achieve cascading dropdowns in InfoPath, and I have had varying degrees of success with them. Some work okay but are complex, others seem to be a bit buggy. So the approach I take here will (hopefully!) provide a good solution – but comments welcome as always!
Objective: On an InfoPath form I wish to have an arbitrary number of dropdowns. Selecting an item in the first dropdown determines the items available in the second, selecting an item in the second dropdown determines the items in the third, and so on. A user should be able to go back to a previous dropdown and make a change. Users should be forced to select in order, so that until an item in the first dropdown is chosen, the second dropdown is disabled, and so forth.
Preparing the Data Source
For the example below our data source for the dropdown items is SharePoint lists. However the method could be modified to use other data sources such as a database or a web service. In the dropdowns users will select a region, then an office, then a team.
I have created three lists for the example and will create three dropdowns.
The “Regions” list is a custom list with no extra columns added. It is populated as follows:
The “Offices” list is a custom list with one extra column added, which is a lookup to “Regions”. It is populated as follows:
The “Teams” list is a custom list with one extra column added, which is a lookup to “Offices”. It is populated as follows:
Designing the InfoPath 2010 Form
Launch InfoPath 2010 and create a new blank form (or open the form to which you wish to add your dropdowns).
Add three text fields as follows:
Repeat for “Office” and “Team”.
Now drag-and-drop each field onto the form.
Without any formatting your form will look like this:
Right-click each textbox and choose “Change Control > Drop-Down List Box”.
The form will now look like this:
Now we need to create three data sources, one for each dropdown.
Click on the “Data” menu at the top of the window, and then “From SharePoint List”.
We’ll do “Region” first.
Enter the location of the SharePoint site. Note that the lists can be on a different site to the site that our form is going to end up in.
Click “Next”. Select the “Regions” list.
Click “Next”. Select “Title” and change the sort order to “Title”.
Click “Next”. Click “Next” again on the offline option page.
Accept the options on the final page.
Click “Finish”.
Now repeat this process for Offices and Teams, but with the following two changes:
First, include the related column in the fields to select from. So for Offices the wizard will look as follows:
Teams will look similar, but with the “Office” column selected.
Second, uncheck the “Automatically retrieve data when the form is opened” option. For all but the first dropdown, we won’t be getting the data until the previous dropdown is changed.
Ok now we need to hook up our dropdowns to our data sources. Right click the “Region” dropdown and choose “Drop-Down List Box Properties”. In the “List box choices” section choose “Get choices from an external data source”. Make sure the data source is “Regions” and amend the “Display name” field to Title (using the button on to its right) so that the form looks as follows:
Click “OK” and repeat for the “Office” and “Team” dropdowns, choosing the appropriate data source in each case. Make sure the “Value” and “Display name” fields are always correct. So for “Office” the form looks as follows:
If you preview your form at this point then the “Region” dropdown should be populated but the other two should not.
Finally we need some rules to make it all work.
Select the “Region” dropdown, then on the “Home” tab at the top, choose “Manage Rules”.
On the “Region” dropdown we will add a single rule. Choose “New, Action”.
Change the “Details for” to read “Populate Offices”.
Now choose “Add, Set a field’s value”.
The field we will set is the “Region” field on the “Offices” data connection. This will act as a filter on the list of offices, only displaying those that have the correct region.
Click the button to the right of “Field”. Change the dropdown at the top to “Offices (Secondary)” and expand “queryFields” to select “Region”.
Click “OK”. Now click the “fx” button to the right of the “Value” field. Click “Insert Field or Group…”
Choose “Region”.
Click “OK”. The formula shows as follows:
Click “OK”.
Now add another action to the rule, again to “Set a field’s value”. This rule will simply set the “Office” field to blank, to clear out any old values.
Now add an action to “Query for data”.
Set the data connection to “Offices”. Click “OK”.
If you preview your form now you should be able to select a region and see the correct list of offices populated.
Now we set up the rules on the Offices dropdown. Select the dropdown and click on “Manage Rules”.
Add a new formatting rule:
Change the details to “Disable if Region Blank”. Click on “None” under “Condition. Set the condition as follows:
Select “Disable this control”.
Next add a new action rule.
Call this rule “Populate Teams”.
Add an action to set a field’s value.
Pick the Teams/Office query field.
Set the value equal to “Office”.
Add another action to set “Team” to blank.
Add a final action to query the teams data connection.
Finally add a formatting rule to the “Teams” dropdown. This rule will disable the control if “Office” is blank.
Now preview your form and check everything works!
Provided all is okay you can publish your form. If you want to use InfoPath Forms Services within SharePoint 2010, it should work fine.
