Here's the situation. I have two lists on a SharePoint 2010 site – let's call them Product Categories and Products. The Products list includes a lookup column that points to the Product Categories list, so users can associate a category with a product. I need to use these lists to provide choices that users can select from within an InfoPath 2010 form. This is how I want the form to work:
- The user selects a product category from a dropdown list.
- The form filters the list of products based on the selected category.
- The user selects a product from the filtered list of products.
This might sound trivial, but it took me a while to work out the nuances and it doesn't seem to be particularly well documented anywhere, so I figured I'd share it. Essentially, InfoPath 2010 includes a new feature that allows you to specify query fields when you connect to a SharePoint list. This allows you to create cascading dropdowns without resorting to custom code, custom data sources or Web services.
Here's a walkthrough of the process. Remember that Product Categories is our "master" list and Products is our "details" list. I'll assume a rudimentary knowledge of InfoPath in that you're familiar with data connections, binding controls to fields and so on.
First, create a secondary data connection to the Product Categories list. This is straightforward, the list only contains one field. Ensure that you leave the Automatically retrieve data when form is opened option selected.
First, create a secondary data connection to the Product Categories list. This is straightforward, the list only contains one field. Ensure that you leave the Automatically retrieve data when form is opened option selected.
Next, create a data connection to the Products list. When you select the fields you want to include, ensure you select the Category (lookup) field as well as the Product field.
On the last page of the wizard, ensure you clear the Automatically retrieve data when form is opened option, and then click Finish. We don't want the form to retrieve a list of products until we've specified the category value we want to use as a filter.
Build your form template. I've used dropdown lists to represent the product category and the product. Both controls are bound to simple text fields in the main data source.
In the properties for the Product Category control, configure the dropdown to retrieve choices from the Product Categories data source that you created in step 1.
Ensure that you select the ID column as the Value field. (Lookup columns only store the ID field from the related list, so we'll need to match these ID values to the category lookup in the Products list).
In the properties for the Products control, configure the dropdown to retrieve choices from the Products data source that you created in step 2. (Note that the data source is actually called PLC Products in my screen captures.)
At this point, the controls are set up to:
- Retrieve choices from our SharePoint lists.
- Store the user selections in the main data source.
We can now use InfoPath rules to set up the cascade filtering we're looking for. Select the Category control. On the Home tab, on the Add Rule dropdown, click This Field Changes, and then click Set a Field's Value. This launches the Rule Details dialog.
Click the button to the right of the Field text box. In the Select a Field or Group dialog, select the Products data connection, expand queryFields, select the Category field, and then click OK.
Click the button to the right of the Field text box. In the Select a Field or Group dialog, select the Products data connection, expand queryFields, select the Category field, and then click OK.
By setting the value of this field, we are configuring the Products data connection to only return product records where the product category matches our specified value.
Click the function button to the right of the Value text box, and then click Insert Field or Group. Ensure the Main data connection is selected, select the field that stores the product category value selected by the user, and then click OK.
We have now set the value of our query field to the ID of the category selected by the user. The Rule Details dialog should resemble the following.
Click OK to close the Rule Details dialog. Now that we've set our query field, we can call on the Products data connection to populate the Products dropdown list. In the Rules pane, on the Add dropdown, click Query for data.
Under Data connection, select Products, and then click OK.
Now, when the user selects a category from the Product Category dropdown, the products list is automatically restricted to those products with a matching category value. It's easy once you know how…
If I could emphasise one key point, it's this... ensure you set the value of your query field before you retrieve the data :-)
No comments:
Post a Comment