Tech News

I faced an interesting question recently in building SharePoint based InfoPath forms. The problem was that a drop down field was being populated from a SharePoint list which could potentially have 50+ values, making the control cumbersome to use. The aim was to filter the values in the drop down based on another drop down field where there was a relationship between the two.

So how do you build these cascading dropdowns?

As an example, we'll use a city/state relationship, both stored in lists, and use these fields on a new record.

Firstly, we'll create a custom List to store the State List items with only one field - State Name.

Next we'll create a list of cities with a lookup field referencing the state field.


Now that we've got the two lists representing the data sorted, create a third list, on which we will add these two fields as lookups.

 

Now, as can be seen the data that's been entered isn't valid in terms of state/city combinations. So, in order to build a form which filters the cities based on the selected state, click on the "Customise Form" button:


This will then open the form in InfoPath. Arrange the form elements appropriately and delete any controls not needed. Now, we want to filter the City control based on the selected State ID. The problem is that the State ID doesn't exist in the generated City data connection, so we have to add a new one. Click on Data-->From SharePoint List, fill in  your SharePoint site URL and click "Next". Select the City list you created from the collection of lists. On the next screen you'll notice that "ID" is selected by default, select both the "Name" field and the state field.

Now comes the magic... Right click on the City drop down list control and click on "Drop-down List Box Properties". Ensure that the new City data source is selected and click on the button next to "Entities". The d:SharePointListItem_RW item node should already be selected, now click on the "Filter Data" button. Click on "Add" to add a new filter. The condition that must be "true" is that the "State" field in the "City" data source must equal the selected "State" in the "main data source". Have a look at the attached image for a better idea...



















Finally ensure that the "Value" field that the drop down is bound to is the "ID" and not the "d:Title" which it probably selected as the default.

Now test your form using the preview function and publish back to SharePoint.
Below is a screen shot of the final SharePoint InfoPath form.

 

This concept can be extended further using text boxes instead of drop downs for a basic "word filter" functionality by using the "contains" match instead of "equals" and can be combined with multiple filters. From a usability perspective, you'll probably have to disable the city text box until a user selects a state.

I've also used a similar "Filtered Data" approach to select single values from a list once a user selects an element from a drop down list. Extending the above example, we could lookup related data from the City SharePoint list and display it in a read-only text box once the user has selected a City.