Ever since Microsoft came out with SharePoint it has been difficult to implement cascading drop-down lists (more on what they are, in a minute.) If you ask me, this is nuts! Cascading drop-downs are the bread and butter of easy-to-use forms.
What are cascading drop-downs?
The canonical example of a set of cascading drop-downs is a form that requires the user to add a state and a city. The screen shot below illustrates the point. When the user selects the state of New York, she see the following cities in the City drop-down: New York, Buffalo, Rochester, Yonkers, Syracuse. However, when she picks Wisconsin as the state, she sees Milwaukee, Madison, Green Bay as the possible cities to choose from. The term "cascading" implies that the contents of the City drop-down depends upon what has been selected in the State drop-down.
A Little History
Historically, one had to know quite a bit to implement cascading drop-downs. To my knowledge there were two ways to create cascading drop-downs:
1. The traditional "development" approach, which involves back end development and deployment .
Before describing the no code InfoPath solution, let me point out the advantages and disadvantages of the two above mentioned solutions:
For the traditional development approach there are two big disadvantages:
- You must have a .NET developer who understands SharePoint development to create the cascading lists.
- You must deploy the solution through the back end. This means that you need access to Central Administration (CA). IT groups can do this, but typical business users cannot.
For the java script approach, there are two advantages:
- You can implement this on WSS (the free version of SharePoint 2007) as well as SharePoint Foundation (the free version of SharePoint 2010.)
- The other great thing about this approach is it does not require access to the back end (Central Administration). Thus business users who do not have access to the back end can implement this. This is also a good solution if you are renting a SharePoint environment and do not have access to CA, which is quite common.
Cascading Drop-downs using InfoPath 2010
Before you read on, I must tell you that this approach only works if you have the Enterprise version of SharePoint 2010 or the forms server. You will also need to have InfoPath 2010 Designer installed on your client machine. Now that 75% of my readers have dropped off... If you do have the right version installed, then this no code approach is easy to follow.
One great thing about InfoPath 2010 is that you no longer have to create pure InfoPath forms. You can customize the Out-of-the-box native SharePoint forms that are automatically generated when you create any sort of list or document library. So that means we can create two or more cascading drop-downs in a standard New / Edit form for a list or document library.
Let's get started:
Step 1: Create the list of states and cities by creating a custom list. Let's call it States and in addition to the default column called, "Title", let's add City as a new text column. Then let's populate the list with some States and Cities. Below is a screen shot that illustrates the new list. If you are following along, for your convenience I have included an Excel file with some States and Cities ( Download States.)
Step 2: So now that you have created the States list, let's create a new list in which we will give the user a set of cascading drop-down lists. Go ahead and create a custom list and call it List of Accounts, or anything you like. Once you have created the list, you will see that you can launch the InfoPath designer by clicking on the "Customize Form" icon depicted in the screenshot below (marked with a 2 in hand written red ink)
Step 3: Once InfoPath designer launches, add a Receive connection to the States SharePoint list.
- Click on Manage Data Connections...
- Click on Add..
- Click on Receive data then on Next
- Click on SharePoint Library or list then on Next
- Provide the URL of States List then on Next
- Pick the States list then on Next
- Check off Title and City the click on Next
- Then keep going with Next, until you get to Finish.
Click on Manage Data Connections...
Click on Add...
Click on SharePoint Library or list
Provide the URL of States List
Check off Title and City
Now that you have the connection we will create two drop-down lists that leverage this connection.
Step 4: Create two drop-down lists boxes and call the first one State and the other one City. By default InfoPath will call them field 1 and field 2. I rename them to State and City.
NOTE: When you add the drop down list boxes, InfoPath will ask you whether you want to add the choices manually or whether you want to look them up in a list. Pick the Manual Choices. We will set these lists up in a moment.
Step 5: In step 6 we will ensure that the City drop-down lists will work in concert with the State drop-down. In this step we will make sure that the State drop-down gets the list of States.
Right click on the States drop-down, then click on the Drop-Down List Box Properties
On the next dialog, make sure your choices look like the ones I have highlighted below. Note that I have checked off the “Show only entries with unique display names.”
At this point you can preview the form in InfoPath and see that the first list box is fully operational:
Step 6: We are now ready to setup the City drop-down. Bring up the properties dialog for the City drop-down.
We will need to make a few adjustments.
Step 6a) For the List Box choices, pick Get choices from an external data source.
Step 6b) For the Data Source, pick States.
Step 6d) Open the dataFields folder and select the item in the folder. Then click on Filter Data…
Step 6e) Click on Add…
Step 6f) make sure Title is selected on the left, then “is equal to”, then pull down the drop-down on the right
Step 6h) On the top, change the drop down from States (Secondary) to Main
Step 6i) Pick State
Click OK, until you are back to the beginning. Now you can preview the form and it works almost perfectly!
There is one tiny issue! The city drop down does not clear itself. Therefore if you pick another state, the old city will stay the same as the one you had picked in the earlier pick of the city.
Step 7a) Select the City drop-down, and click on the Mange Rules icon in the Home Ribbon
Step 7c) Pick Action
Step 7d) Click on Set a field’s value
Hit OK , and you are done!
You should now have a fully functioning cascading drop-down set of lists.
Many thanks to Marc Anderson and Clayton Cobb for reviewing my document and giving me many good suggestions.