Cascading Menus in SharePoint Lists using InfoPath

This is to create cascading menus for a SharePoint List – For instance choose a region and get the countries in that region, choose a country and get the projects that are currently located in that country

First, create your list which contains all of your projects. It should include the project name, region, and country in which the project is located. Mine looked like this:

image

The other fields are fields which we want to include in our data entry list (the “main” list which will have the cascading menus inside it). The idea is to end up with an InfoPath form for our second list which will filter based on the values of Region and Country, making it easier to select the project/Op item from the hundreds that exist.

Create your new form, including all fields that you want to have show in your final records. For the Project field, create this one as a Lookup field, connect it to your project list on the Proj-Op field. Select any additional fields which you wan to have populate in your list (I chose Project Num, Total Area, GF Ownership and Expiry Date). No need to include Regions or Country, since these will come from another connection in your InfoPath form.

Now, open your form in InfoPath from the Ribbon interface in the list. Create a new section above the current table, I used an out of the box table with a header, but it could be whatever you want. The table should be inserted in the new section you just created.

image

Click on "Add Field" in InfoPath and add two fields, one called "Region", and the other called "Country". You can leave them as text fields for the time being.

image

Move Month and Year (and any other initial information you feel is necessary to fill out) to the top in your new section. Move labels around as necessary:

image

KEY CONCEPT:

  • Now we need to wire up a new data connection, so we can pull in the data for Region and country. We cannot use the current connection created when you did the lookup in SharePoint, it has to be a new connection created within InfoPath.
    • Click on Manage Data Connections in lower right-hand side of InfoPath screen
    • Click the "Receive Data" radio button if it isn’t already selected, and click on Next
    • Click on "SharePoint Library or List" in the screen "select the source of your data"
    • Enter the location of your site where your projects list resides (not the list’s URL, that comes next)
    • Choose your list (mine is called "All Projects") and click on Next
    • In the next screen, InfoPath wants you to choose from the fields contained in the Projects list – select Region, Country and Proj-Op. Click on Next. Click on Next again, don’t save your data in the form.
    • Give it a meaningful name, I called mine "ProjectFilterData"
    • Close your connection
  • Now let’s wire up our Region and Country Filters
    • Right click on the Region field, and choose "Change Control", click on "Drop Down List".
    • Right Click on the Country Filter, and "Change Control", click on "Drop Down List".
    • Right Click on Region, and click on Properties
      • Click on "Get data from external data source"
      • Select the data source that we just created, mine is called "ProjectFilterData"
      • Click on Value and select the field "Regions" from your Project list
      • Click on Display Name and select "Regions" from your Project list
      • Click on Display only entries with unique display names
    • Right Click on Country, and click on Properties
      • Click on "Get data from external data source"
      • Select the data source that we just created, mine is called "ProjectFilterData"
      • Click on Value and select the field "Country" from your Project list
      • Click on Display Name and select "Country" from your Project list
      • Click on Display only entries with unique display names
      • Now, on this field, we only want to show the countries for the selected region. So we need to do the following:
        • Click on the icon next to the field on the screen called "Entries"

image

  • Click on Filter data in the next pop up
  • Click on Add
  • Click on "Select a field or group" at the bottom, to insure we’re getting the right field
  • Click on Regions from your data connection ProjectFilterData
  • Is Equal to
  • Click on "select a field or group" in the next field, and click on "Main" at the top of the screen, to insure you get the Region you JUST SELECTED IN THE FORM.
  • Click on datafields, then on mySharePointListItem_RW
  • So what we are filtering is by Regions in the data connections – this needs to be equal to the region in our form. Thus we only get countries where the Region in the form is equal to the regions in the list.
  • Click Okay until you exit the dialogs
  • Click on
  • Click on Display only entries with unique display names
  • Preview your form and test the filter out
  • Create a new section just below the section you created with region and country, and before your table with the rest of the fields.
  • Move the Project/Operation field to this section by copy/pasting it
  • Right click on Project/Operation and click on Properties
  • Change to your new data connection "ProjectFilterData"
  • Click on the same filter icon in the "Entries" area as before to create your filter
  • Click on Filter Data button
  • Click on Add
    • Click on "Select a field or group" at the bottom, to insure we’re getting the right field
    • Click on Country from your data connection ProjectFilterData
    • Is Equal to
    • Click on "select a field or group" in the next field, and click on "Main" at the top of the screen, to insure you get the COUNTRY you JUST SELECTED IN THE FORM.
    • Click on datafields, then on mySharePointListItem_RW
    • Select Country
    • So what we are filtering is by Country in the data connection – this needs to be equal to the country in our form. Thus we only get Projects/Ops where the Country in the form is equal to the Country in the list.
    • Say Okay until you get to the original dialog, and change the VALUE to ID. This is because SharePoint has to look up the item by its UNIQUE ID to do the display.
    • Display Name will stay the same (Project/Op, or in my case Title, because I renamed this field in SharePoint to Project/Op, but InfoPath doesn’t honor that and still sees it as "Title").

Leave a Reply