SharePoint 2010: Creating a Filtered Dropdown Using InfoPath 2010 !

Tuesday, June 22, 2010

SharePoint 2010: Creating a Filtered Dropdown Using InfoPath 2010 !

I have heard a lot of request on the steps required to create a filtered dropdown within SharePoint 2010. I wanted to just create a quick post that will walk you through the steps. The example is pretty simple, but should be enough to get you started.

In our example we are entering data into a timesheet. We want to be able to select a client and a project. Once the client is selected then we would want to filter the list of projects to show only projects related to that client.

To get started we need 3 lists:

Clients
Projects
Timesheet

At a high level, here are the steps we are taking:

Create the Client Lists in SharePoint.
Create the Projects List in SharePoint and add a lookup column to link with the Client.
In the Timesheet list add two lookup columns, one for client and one for project.
Edit the Timesheet list form in InfoPath 2010 :
1. Apply and desired style changes
2. Add a Data Connection that we can use for the Filtering
3. Add a Rule to Reset the Projects Field whenever the Client Field is updated

Since I am assuming you are familiar with creating lists and adding lookups, I will spend the rest of the blog focused on step 4, editing the form in InfoPath.

From the library Ribbon, select the Customize Form option.

The form will open in InfoPath and you can then make any desired design changes to the form. In my example I have changed the background style, inserted an image, added some header text and removed the attachments row. My changes to the form are shown below:

Next up, we will want to create a data source that we can use for the filtering. We already have 2 data sources that have been added from our lookup columns, but those only retrieve the title and we are looking for additional information from the list. To add a new data source select the Manage Data Connections link on the toolbar.

From this menu, select to add a new data source connection to receive data from a SharePoint list. As you work through the menu, you will be prompted to enter the particular list data that you want to reference in your lookup. We will first enter the URL for the site we are referencing, then the specific list and then finally the fields that we want to use in our data source. As a final step you will need to give this connection a unique name, in my example we are using Project_Details.

Now that we have the data source in place we can create the filter on our projects field. We will need to right click on the field we want to filter and select the option for the Drop-Down List Box Properties.

We first need to update the Data Source to point to the data source we added earlier. Once we do this, we can filter the entries (to enter in the option to see the entries, select the icon next to the entries field).

The filter we want to set up can be explained like this: Only show the Project Options when the Client field on the form is equal to the Client field in the Projects list. Below are the screenshots for the configuration.

For the filter we will first select the client field on the form:

Our operator will be "is equal to" and our final selection will be the client field in the projects list:

Now that we have the filter configured, the final step is to add a rule that resets the project field whenever the client field is modified. This way we can be sure that if they change the client, they will also need to update the project. The ribbon makes creating rules in InfoPath very easy. Simply select the field the rule will apply to and then select the rule you want to apply. In our case we want to set the rule to occur whenever the field changes and we want to reset another field's value.

The menu screen is displayed and we can quickly use the icons to pick the field we want to set and then enter the value. In our case we leave value blank because we want the value to reset to a blank entry.