The goal here is to create a SharePoint form that contains several fields that will be filtered based off previous selections in the form. In the example that follows starting with a Category, only specifc Vendors will be available, likewise for Topics available for selected Vendor, and so on for Duration, Description, and Instructor. What we want to do is filter the available options in the form as selections are being made.
Here is what the final result will look like:
Setup SharePoint Lists
So lets get to it. Start with 2 sharePoint lists, my example will use the following:
-
Training Catalog
-
Training Request
The Training Catalog list contains the raw list of available trainings and uses the following columns:
-
Category
-
Vendor
-
Topic (Rename the title field to Topic)
-
Duration
-
Description
-
Instructor
Note: these columns are all single line of text. Lookups or other column types may work, but are untested.
The Training Request list contains the exact same columns as above. Other fields, such as "When" can be included as well, but have no impact on this tutorial either way.
Note: These columns MUST be created as single line of text.
Open SharePoint Form in InfoPath
Open the Training Request list form:
-
List Tools > List > Customize Form, or
-
List Settings > Form Settings > Modify the existing InfoPath form > OK.
After the form loads in InfoPath, select the first colum (Category) and right click it > Change Control > Drop-Down List Box.
Do the same for Vendor, and Topic. Filtered data can only be applied to specific Control Types. Drop down fields are ideal for our use case here.
And we will only need to filter data on these first 3 fields, the rest can remain as Text Box controls.
Create InfoPath Data Source
Field: Category
Right Click Category again and select Drop-Down List Box Properties.
Select Get Choice from an external data source, and to the right of Data Source select Add…
-
In the Data Connection Wizard Create a new connection to: Receive data > Next.
-
Select the source of your data: SharePoint library or list > Next.
-
SharePoint site details: the location of the SharePoint site should be prepopulated > Next.
-
Select a list of library: Training Catalog > Next.
-
Select Fields: Category, Vendor, Topic, Description, Duration, Instructor, and any other fields you might have included in your Training Catalog list.
-
Sort by: Categoy > Next > Next > Name your new Data Connection and select > Finish.
Back in the Drop-Down List Box Properties dialog box, the Data source field should now point to your new data connection, in my case named Training Catalog.
-
To the right of the the Entries field, click on the Select XPath button.
-
In the Select a Field or Group dialoag box, select d:SharePointListItem_RW and press OK.
Note: The Category column does not need to be filtered as it is the first field in our form.
-
Back in the Drop-Down List Box Properties dialog box set the Value and Display name fields both to d:Category.
-
And for good measure, check the Show only entries with unique display names and click OK.
Setup Some InfoPath Filters
Field: Vendor
-
Right click Vendor field and select Get choices from an external data source and set the Data source to Training Catalog.
-
Click the Select XPath button to right of the Entries field.
-
In the Select a Field or Group dialoag box, select d:SharePointListItem_RW.
Select the Filter Data… button in the Select a Field or Group dialog box and In the Filter Data dialog box click the Add… button:
-
In the first drop down field choose select a field or group.
-
The Data source should be set to Training Catalog (Secondary). Choose "Show Advanced view" if you cant see the Data source.
-
Under d:SharePointListItem_RW select Category and then press OK.
-
The middle drop down qualifier should be set to "is equal to".
-
In the third drop down field choose select a field or group, select the Show advanced view, and set the Data Source to Main.
-
Then, under the my:SharePointListItem_RW group select Category and click OK four times to close out the various dialog boxes.
-
Finally, set the Value and Display name fields both to d:Vendor and check the Show only entries with unique display names.
Field: Topic
-
Right click Topic field and select Get choices from an external data source and set the Data source to Training Catalog.
-
Click the Select XPath button to right of the Entries field.
-
In the Select a Field or Group dialoag box, select d:SharePointListItem_RW.
Select the Filter Data… button in the Select a Field or Group dialog box and In the Filter Data dialog box click the Add… button:
First Condition
-
In the first drop down field choose select a field or group.
-
The Data source should be set to Training Catalog (Secondary). Choose "Show Advanced view" if you cant see the Data source.
-
Under d:SharePointListItem_RW select Category and then press OK.
-
The middle drop down qualifier should be set to "is equal to".
-
In the third drop down field choose select a field or group, select the Show advanced view, and set the Data Source to Main.
-
Then, under the my:SharePointListItem_RW group select Category and then select the And button to add another condition.
Second Condition
-
In the first drop down field choose select a field or group.
-
The Data source should be set to Training Catalog (Secondary).
-
Under d:SharePointListItem_RW select Vendor and then press OK.
-
The middle drop down qualifier should be set to "is equal to".
-
In the third drop down field choose select a field or group, select the Show advanced view, and set the Data Source to Main.
-
Then, under the my:SharePointListItem_RW group select Vendor and click OK four times to close out the various dialog boxes.
-
Finally, set the Value and Display name fields both to d:Topic and check the Show only entries with unique display names.
If you preview the form in InfoPath, the first 2nd and 3rd fields should now only display relevant data based on the 1st and 2nd field filtering. Close the preview when you're finished.
For the final 3 fields, there are no "choices" to make. And thus there will be no more fields to setup filters on.
We simply want to populate the Duration, Description, and Instructor fields.
Set Field Values using InfoPath Actions
-
With the Topic field selected, choose Home from the top menu and select Manage Rules. In the Rules Pane, name your new Rule.
-
Under Condition: a default rule should be created "None – Rule runs when field changes" – leave it at that.
-
Select the Add button under Rule Type: Action and choose "Set a fields value".
We will need to define 3 actions, one for each of the remaining fields (Duration, Description, Instructor).
In the Rule Details dialog box:
-
Set the Field to (Main) Duration by clicking the XPath button (remember to "Show Advanced View" to select Main data source).
-
Set the Value by clicking the fx button, select Insert Field or Group… and choose (Training Catalog (Secondary)) Duration field.
-
Before you hit OK, additionally select Filter Data > Add to setup some filter conditions.
In the Specify Filter Conditions dialog box, do as we did before and set your conditions for Domain, Vendor, and this time include a Topic condition as well.
The Specify Filter Conditions should be as follows:
-
(Training Catalog (Secondary)) Category "is equal to" (Main) Category "and"
-
(Training Catalog (Secondary)) Vendor "is equal to" (Main) Vendor "and"
-
(Training Catalog (Secondary)) Topic "is equal to" (Main) Topic
Select OK > OK > OK > OK to close out all the dialog boxes.
And finally, the Value box should look like this: Duration[Category = Category and Vendor = Vendor and Topic = Topic]
Click OK.
Repeat these steps for Description and Instructor by adding 2 additional actions to the Rule created on the Topic field. If you preview the form in InfoPath now, the first 3 fields will be filtered, and the final 3 fields will be populated based on the preceeding 3 fields.
And that's it!!!
