A few months ago, one of our SharePoint Consultants was asked to create a form field that incorporated some logic into the drop down menus. In this case, the desire was once a particular US State was selected in one field, the following drop down menu would then show only the cities found in that particular state.
We thought this concept could apply to a variety of use cases beyond geography – like selecting an appliance model number and then dynamically populating the next field with parts specific to that model – so let’s go through a simple walkthrough on how to create this functionality with a look up field.
Caution: SharePoint developers should exercise caution in regard to the number of look up fields accessed per list. The best practice is eight in SharePoint 2010 and SharePoint 2013. There are ways around this, but that is something we’ll have to cover in another blog post. In a nutshell, if you need to have much more than eight look up fields, that is best managed in the content type.
Below is a simple form being used for this example – we want to expand it to add City and States, but the client wants to limit the cities listed by the value of the state selected as this will make for a better user experience.
The goal is to add a set of fields to have cities and states for each of the business locations in this example. If the goal was to nest even more choice fields based on a single entry, then the same process can be repeated, but in this case only two more fields will be added.
Now let’s create a new list containing the cities and states that are available for reference, as seen in the image below:
With the list created, let’s open up the InfoPath. Here we will create a drop-down list box for the City and State fields as seen below.
Now, go to the “Data” Menu at the top of the window, and then select “From SharePoint List”. Next, enter the location of the SharePoint site where the cities list was created, select the “Cities” list, and then make sure you select the fields needed. Please ensure that you Sort By ID.
Now then, finish clicking through the wizard.
With the wizard aspect completed, now select the “State” dropdown box and go to the “Dropdown List box Properties.” The following image shows an example of the way the properties for the Dropdown box should be configured.
The box is acquiring the data from an external source. In this case, it’s the source that was just created by the data connection, with the only field being gathered being the state field. The Entries with unique display names is utilized in order to limit any redundant entries.
Now we’ll seek to bind the “City” dropdown so that the value displayed will be based upon the value in the “State” dropdown.
Select the “City” dropdown and go to the properties. In City’s properties, select the external data source. Now, expand the “entries” section and select the “State” node. Be sure to click on the check box next to “show only entries with unique display names”.
Note: You cannot do a filter like this in InfoPath 2007, but in SharePoint 2010 & 2013 we can filter the data.
Finally, for the next dropdown list where you filter the series of data of States, create a filter for the States that match the above field, setting the necessary data connections as needed.
Congratulations you have created an external data filter.
With a bit of Googling, you can find other tutorials and blog posts on this activity, so we encourage you to compare and combine methods to make your form the best it can be. As mentioned earlier the city/state scenario is one our SharePoint Consultant dealt with first hand, but the concept can be applied to other situations with relative ease. Let us know what you’re working on and if this was useful in the comments section below, and please share this across your social networks.