This article applies to InfoPath 2010 and SharePoint 2010, InfoPath 2013 and SharePoint 2013, and SharePoint Online
I have been working with InfoPath since 2008 and have configured cascading dropdowns countless times. As it happens many times, regardless of how many times you solve simple problems with same technologies, you may came across challenge where you won’t able to figure out what would be an issue. I ran into similar thing last week and hit the wall while configuring cascading dropdowns. Just few weeks ago, I had built very complex InfoPath forms with multiple set of cascading forms. Last week I tried to configure simple InfoPath form with cascading dropdowns for new project. What I felt like trival tasks ended up taking more than few hours due to small mistake before I ended up figuring out the exact issue. In past, I had thought about posting this article more than few times in past but always avoided due to hundreds of resources out there. With this article, I am going to personalize this process for myself and have it as reminder for future.
There are thousands of videos and articles out there but I am yet to came across full step by step guide to build cascading dropdowns in real world which would not only filter the cascading dropdowns but clear the secondary dropdown upon reselection of primary dropdown.
Here are the high level steps to configure Cascading dropdowns in InfoPath forms which will serve self-guide for me in future if needed. Let’s say we would like to filter city dropdown values based on state dropdown selection.
- Create Two SharePoint Lists – State and City. State is parent list and City is child list with State as lookup column
- Create SharePoint Address List – State, City as lookup columns
- Design InfoPath Form for Address Data Entry – Open the “Customized Form” in the Address List ribbon, State and City will be pre-configured as Freeform Textbox control on InfoPath form, change control type to Drop Down List. This step requires InfoPath Designer installed on the desktop.
- Create Data Connections in InfoPath for State and City List
- From the State and City dropdown control on InfoPath form, Change Data Properties to Get choices from an external data source and select appropriate connection, select data source, set the value and display name fields to ID and Title respectively => if you preview form, it should show all the data from the SharePoint list as selectable items in form
- From the City drop down control on InfoPath form – Add filter for state, state from city data connection = state from main data connection, since it’s lookup column – both state values are IDs and it should return matched items
- At this moment, cascading dropdowns are configured and it should work intended with little issues – when user reselects values in cascading dropdown list, it shows 0 as previously selected item which isn’t available in new filtered result set. To fix this – Add rule on State dropdown control to clear city values upon state control value changes.
Hope this helps.