Saturday, November 28, 2015

Cascading Dropdown in SharePoint List using Infopath Forms

What is Cascading drop downs? Well, Cascading drop downs are linked drop down controls where the content of the second drop down depends on the selection of the first one. E.g. When you Choose Country in the first drop down, the second drop down State is automatically filtered to the list of states actually in that county. Cascading drop downs are quite a common requirement and unfortunately, SharePoint doesn't support it Out of the box.

In this article, I'm documenting the implementation of cascading drop down functionality in SharePoint list forms using InfoPath forms step by step (at least for my own reference! even a simple mistake takes hours to resolve).

In short: You create a new data source to retrieve "Region" and "Country" columns from the Countries list and then filter the countries drop down to show only items matching with the "Region" selected in "Regions" drop down.

Cascading Drop down List Setup:
Here is my List setup:
  1. Regions - List of Regions - Parent List for Countries list's Region column.
  2. Countries -List of Countries with Region as lookup column from "Regions" list
  3. Projects List - List to capture project data, Where Region and Country columns are from above lists as lookup columns.

Step 1: Customize SharePoint List in InfoPath Designer

Open the Project SharePoint list in browser, Click on "Customized Form" button under List Tab. This opens your SharePoint list form in InfoPath Designer. Alternatively, You can open InfoPath Designer and Customize SharePoint List form. This establishes the data connections between SharePoint list and InfoPath.
infopath cascading dropdown from sharepoint list
BTW, To utilize InfoPath browser farms you need to have SharePoint Server Enterprise edition and "SharePoint Server Enterprise Site Collection features" feature activated at site collection level.

Step 2: Create New Data Connection:

Add new data connection in InfoPath to retrieve Country & its respective Region. 
  • In InfoPath Designer, Click on Data Tab, Click on Data Connections, Click on Add button
    infopath cascading drop down boxes
  • In Data Connection Wizard, Click on Receive data then on Next
    infopath cascading dropdown filter
  • Click on SharePoint Library or list and then on Next button
    sharepoint 2010 infopath forms cascading dropdown
  • Provide the URL of Countries List then on Next
    infopath cascading dropdowns in browser forms
  • Select the Countries list then on Next
    cascading dropdown in sharepoint 2010 infopath
  • Select the Country and Region fields, and click on Next
    cascading dropdown in sharepoint 2013 infopath
  • Provide a name to your data connection (say: Country) and click on Finish.
    sharepoint infopath cascading drop down lists

Step 3: Change the Data Source of the "Country" Drop down and Apply Filter:

Once we created the data source for Country,
  • Select the "Country" Drop Down in InfoPath form designer, Right Click and choose "Drop down List box Properties" to get the Control Properties window. 
  • In Data source dropdown, Select the new data source we created in Step 1 (In my case its: "Country"). Now, proceed to step.
    infopath cascading dropdown sharepoint list
Apply Filter to Country Drop down:
The Next step is to filter country values in the Country drop down based on the selected Region. 
  • In Entries section, click on the tree button. This brings Select Field or Group window.
    infopath 2010 cascading dropdown sharepoint list
  • Select the d:SharePointListItem_RW node under "DataFields" and click on Filter Data button.
  • In Filter Data window, click on Add button
  • In specify Filter Conditions, 
    • In the Country drop down, Add filter for Region. That is: Region from Countries data connection = Region from main data connection. since Region is a Lookup column – The matching rows will be retrieved. Here is how: In the first drop-down list choose "Region" field, Set the condition to: is equal to, in the next drop down choose: Select a field or group - This brings an another window to select the field.
    • Under "Fields" drop down, Change the drop down value from "Country(secondary)" to "Main".
    • Once you select "Main" in fields drop down, you'll get list of fields of the main data connection (In my case, its "Project:" list fields). Select the Region field from the list, and click on OK.
      Click on OK again to go back to the Country drop down properties window
Set Value and display Name Fields of Country Drop down:
Finally in the Country drop down properties, Set the Value and Display name fields to ID and Title respectively by selecting it through the tree button. This is because Country drop down is a lookup field and it stores lookup ID value internally. If you leave it as d:Title you will get an error when you try to save the list item.
sharepoint infopath cascading dropdown

Step 4: Add a Rule to "Regions" Drop down to Clear Countries Drop down on Re-selection:

This is important, because You'll have to clear the secondary cascading drop downs upon re-selection of primary drop down.
  • Select the Region Drop down in InfoPath designer, From the ribbon click on Add Rule, Select This Field Changes, Set a Field's value.
    infopath cascading dropdown from sharepoint list
  • Choose the field as "Country", leave the Value as blank and click OK.
    sharepoint 2010 infopath cascading dropdown
That's it! Now, from InfoPath Designer, Save and Publish the form!

There is a way to achieve cascading drop down functionality with SharePoint OOTB list forms and SPServices-Webservice-Javascript! Refer: Cascading Drop down In SharePoint Lists using jQuery



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

No comments :

Post a Comment

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...