Recently I was tasked to implement cascading Multiple-Selection List boxes in an InfoPath 2010 form by using SharePoint 2010 lists as the source. In this blog I'll demonstrate how to achieve the cascading Multiple-Selection List boxes (check boxes) functionality in InfoPath. I should admit that this post about cascading dropdowns definitely helped me to nail it down! The proposed solution would apply to all versions of SharePoint 2010 and Office 365, and is compatible with BOTH client based and browser-based forms. To get started, let's go ahead and create the following two simple Custom SharePoint lists.
Regions list will only have a single column i.e. ‘'Title" (Data Type: Text) as shown below in Figure 1.
Approvers list will have two columns, they are
- Region (Lookup to the ‘Title' column of "Regions" list)
- Approvers (Person or Group)
NOTE: For the ‘Approvers' column make sure you select Allow multiple selections as NO.
Now add some items to the Approvers list as shown in below. Each Region can have multiple Approvers associated with it!
We have the set up ready in SharePoint. It's time to create an InfoPath 2010 form with the following two form controls/fields.
- Region (Drop-Down List box)
- Approvers (Multiple-Selection List box)
Once you create the two fields in InfoPath, we'll need to bind these controls to SP lists by creating Data Connections to receive the data from SharePoint lists that we've configured before. Region field will receive data fromRegions SP list and Approvers field will receive the data from Approvers SP list. This way we can make sure that the form controls are dynamic, meaning in the future if you ever want to change Regions/Approversdata, all we need to do is update SharePoint list items and the InfoPath form would automatically pick up the new data!
To create a Data Connection, from the InfoPath form ribbon navigate to Data > From SharePoint List
Then enter SharePoint URL > select Regions list > select Title > Next > UNCHECK the box to automatically retrieve the data when the form is opened (This ensures that we are only querying SP lists when it is needed, and not every time the form is opened) > Finish the Wizard by naming the Data Connection as "Regions "as shown below
Next, we'll create one more Data Connection to receive the data from Approvers SP list.
To create a Data Connection, from the InfoPath form ribbon navigate to Data > From SharePoint List then enter SharePoint URL > select Approvers list > select Region, Approvers > > Next > UNCHECK the box to automatically retrieve the data when the form is opened (This ensures that we are only querying SP lists when it is needed, and not every time the form is opened) > Finish the Wizard by naming the Data Connection as "Approvers "as shown below
At this point of time we have the InfoPath form controls and Data Connections ready and your form should look like below in InfoPath Designer 2010!
Now Right Click on the Region drop down control in InfoPath form > Drop-Down List Box Properties and set the Data Source, Value and Display Name as shown below.
For the Approvers Multiple-Selection List box, Right click then properties and set the Data Source, Value and Display Name as shown below
For both Region and Approvers InfoPath form controls, the above settings are really important, so make sure you get them right!
Now the FUN part comes in! Yes, we need to query the Approvers list after a region is selected. So, we need to create a rule on the Region drop-down, which gets triggered right when a value is selected. Double-click the Region form control, and click the Manage Rules button in the ribbon or you can right click Region > Rules > Manage Rules
In the rules pane, click the New > Action. For the first action, next to Run these actions click the Add button and choose Set a Field's Value.
For the FIELD, Set the Query value of the Region field in the Approvers list. Keep an eye on the Fields dropdown it should be Approvers (Secondary) as shown below.
For the Value, select the Main data source, and the Region field as shown below.
One last thing here, for the above rule we need to add one more action by clicking Add > Query for data and then select Approvers from Data connection dropdown > click OK as shown below!
That's it! We're done for the day. Save the form to local disk and open it to verify the functionality (You can also preview the form)
When you open the form, Approvers will be unfiltered as shown below.
Select a Region from the dropdown, voila!!! Approvers are filtered based on the Region you selected as shown below!
Hope you had fun following the post! Stay tuned for more!