Implement Cascading Drop Down For Forms in SharePoint 2010

What are Cascading Drop Downs and why do we want them?

Cascading drop downs are where you have linked drop down boxes where the contents of the second drop down depends on the selection of the first one. They are used to target selections and make choosing them much easier. The canonical example is choosing a city where in the first drop down you choose the county/state and the second drop down is then filtered to the list of cities actually in that county/state.

SharePoint Support

SharePoint 2007 didn’t natively support cascading drop downs in list forms. There were a couple of workarounds which either involve adding a solution onto the server or building a JavaScript version using the SharePoint web services.

However, in SharePoint 2010 Enterprise version, you can implement Cascading Drop Downs out of the box with no code. The 2 features which make this possible are:

  1. InfoPath list forms.
  2. Browser based support for the cascading drop downs in InfoPath 2010.
  3. Background

    In this example I’m going to implement a system for listing after school activities. The school is split over 3 sites and as part of creating the activity the teacher needs to choose the site the activity is on, which will then allow them to choose the room the activity is in. In addition to this, we’ll add some finishing touches so that the room value is cleared when the site changes and the room drop down shouldn’t be enabled until a site has been selected.

How to Implement Cascading Drop Downs

 

Before you begin make sure that the site collection feature "SharePoint Server Enterprise Site Collection features" is activated or you won’t have support for InfoPath forms.

enterpriseFeature

 

1. Create the Lists

 

Firstly you need to create 2 lists to store the sites and the room. So create a custom list called Site and add some values to it, in this case I chosen Centre, East and West. No additional columns are required.

sites

Next create a custom list called Room and add a lookup column to the Site list. This column should be required and link to the Title column in the site list.

createSiteColumn

Next add some rooms to the Room list.

Room

Now we need to create the actual after school activities list. This can contain whatever columns you require for its use, but also a site a room column which are lookups to the Site and Room lists. Here’s the list with a couple of entries in.

activities

Now when we Add a new item you can see that the room drop down contains all the possible values, no matter what the site selection is.

newActivity

2. Link the Room Drop Down to the Site Drop Down

 

Now we need to modify the form so the the room drop down depends on the site drop down. To enable this select the list tab in the ribbon and click Customize Form. For this you need InfoPath 2010 installed on your machine to edit the form. Once the form is published your users do not need InfoPath installed locally.

customizeForm

Once you click on Customize Form it will open up in InfoPath.

form

There’s now several steps to go through to enable the cascading drop downs. In a nutshell you create a new data source pulling from the room list, then add a filter on the Room drop down to only show items from the room list with the same site as that in the Site drop down.

Create the Data Source

First of all right click on the Room drop down and select Drop-Down List Properties to bring up the properties for the drop down.

InitialDropDownProperties

First we need to add a new data source so click on Add next to Data Source,AddDropDownProperties

and step through the wizard. You want to create a new connection to receive data i.e. the defaults, and select the source of the data to be SharePoint library of list.

ddwizard1 ddwizard2

Next you select the location of the site which as we opened from within SharePoint should be already correctly prefilled, and in the next step select the Room list.

ddwizard3 ddwizard4

In the next step you need to select the fields you want from the data source. You need to select the ID field as that is the actual value written to the activity room column, the Title column to display and the Site column for the data to filter on. You can sort on any column you want, but in this case only Title makes any sense. You can then choose to store the data in the form template for offline mode, but this generally isn’t required.

ddwizard5 ddwizard6

Finally you need to choose a name for the data connection. As long as it’s descriptive and unique it doesn’t matter what you choose. The default name is the name of the list with a 1 on the end. When you finish you’ll be taken back to the properties form where the name of the data source will have been changed to the new one you have just created.

ddwizard7

Filter the Values

Next we need to filter the values in the Room drop down based on the selected Site. To start this we click on the button next to the Entries value, then on the Select a Field or Group window click on Filter Data at the bottom.

filter1 filter2

To add a filter click the Add button and then select the Filter Conditions.

filter3

For the filter we are looking to create one which limits the data to only those rows which have the Site value equal to the selected Site i.e. the site from the main data in the form. To generate this the filter needs to be "Site" "is equal to" "Select a field or Group".

filter4

This will pop up another window to Select a Field or Group. You need to change the Fields drop down to Main and then select the data field Site. Finally click OK and the result will display Site in the third drop down in the Specify Filter Conditions window.

filter5 filter6

Click OK to go back to the Filter Data window, OK to go back to the Select a Field or Group window and then OK again to go back to the Drop Down Properties Window.

filter7

Finally in the drop down List Box Properties you need to change the Value to d:ID by selecting it via the button to the right. This is because internally the lookup value stored is actually the ID in the lookup table. If you leave it as d:Title you will get an error when you try to save the list item. Once this is done click OK.

ddFinal

 

Final Touches

At this point you can publish the form and you will have a working cascading drop down. When you select a site the room drop down will be populated with the appropriate rooms. However, there are a couple of problems with it. Firstly, the Room value is not cleared when changing the Site. As the room value will not be in the filter for the new site value it will get changed to the id value which will be confusing for your users. Secondly if the Site is not a required field the Room Drop Down is still enabled if there is no Site chosen.

Both of these can be resolved with rules.

Clear Room Value When Site Changes

To clear the room value when the site changes you need to add a rule to the site field. In InfoPath select the site field then, from the ribbon use Add Rule | This Field Changes | Set a field’s Value.

clear1

Then it’s simply a case of selecting Room for the Field and leaving the Value blank.

clear2

Publish the form and test – It’s as simple as that.

Disable Room Drop Down When No Site Selected

To disable the Room Drop Down again add a rule, but this time you need to add a conditional formatting rule. To add this select the Room field and then from the ribbon select Manage Rules. This will open the Rules side bar. From this side bar select New | Formatting. This will then add the formatting options to the side bar.

disable1 disable2

Choose a suitable name for the rule then click on the "None" under condition. This will open the Condition window which is very similar to the Filter Condition one before. This time we choose "Site" "is equal to" and "Use a Formula". The Insert Formula windows pops up and just enter "" for an empty string.

disable3 disable4

Once you click OK to save the condition, just select "Disable this control" in the side bar and we are done. Publish the form and test, this time the room drop down will be disabled when no site has been selected.

Conclusion

SharePoint 2010 Enterprise Edition allows us to easily create powerful InfoPath forms incorporating cascading drop downs without any code or installing any additional solutions onto the server.

26 thoughts on “Implement Cascading Drop Down For Forms in SharePoint 2010

  1. Hi,
    I realy liked this, but I can’t implement this in documents libraries, only in lists. Do you know if there’s a way to do this in documents libraries?

  2. @Richard
    It would be really nice to apply this to a document library, because it would allow us to classify our documents without using folders and subfolders. That way, when someone needs a specific document, he or she could use the filters options to search for it.

  3. Thanks! I’ve read various how – to’s on this subject and I thought yours very clear.

  4. I have been scouring the web trying to find the solution to one problem. I utilized this technique to create the cascading drop-downs. It works great. However, after a form is filled out and saved. The ID# appears in the column on the list associated with the field instead of the Title.How can I get it to display the Title of the field chosen?

  5. spcascade.org uses SPServices to provide best free solution which support multi level dropdown list with no limits.
    It is javascript/jquery based solution with no page refreshes and involves only one look up list for multiple columns.
    Saves data in single line text instead of Ids that are used by typical look up columns.
    Very easy to use syntax too!

    “`
    $().SPCascade({
    destListColTitles: [‘Continent’,’Country’,’State’,’City’],
    srcListColInternalNames: [‘Continent’,’Country’,’State’,’Title’],
    srcListName: ‘CitiesLookupList’,
    srcListFilterCAMLQuery: ”
    America

    });
    “`

    http://www.spcascade.org

  6. I am having the same ID issue as previous comments, I am assuming we are not going to get an answer as to why we are getting only ID #’s in our Room Field.

  7. I can’t duplicate getting only IDs in the room field. Did you create it as a look up field to the room list?

  8. @Amber
    After you set the field for ‘Get information from:’ to the list you want, make sure the ‘In this column:’ choice doesn’t jump to ID. For some reason, I’ve seen it do that without me asking – I simply set it back to Title.

  9. Really clear and complete documentation. Thanks for all the context and explaination in addition to the clicks. Worked great for our company to create some Issue categories and sub-systems for those issues. Thanks!

  10. Hi,

    I have two multiple selection list boxes. I want to implement the same functionality in this situation. Please let me how can i implement it.

  11. Hi

    Great article, thanks!

    Any idea on how to get another level, eg 3rd, 4th and even a 5th level cascading? I have tried the SPCascade option, but cant seem to get it to work in SharePoint 2013…

    Thanks!
    Dave

  12. Hello! Is there a way to add an item to the drop down items/options that only a specific group can see? thank you!

  13. Thank you so much for this tutorial!! I am limited to just InfoPath, and SharePoint itself (corporate is not allowing us to have SP Designer!) I have been searching all over the place for just this very information! Thank you again!

  14. I’m missing something here. When I create this cascading list, how do I then utilize it in the list where I want the cascading drop down to actually exist?

  15. This is great information but could use more help. I have a Procedure Manual that is grouped by Chapter. Now I need to add a Sub-Chapter. Will what is explained on this page work for this?

    EXAMPLE
    Chapter – Application
    Sub-Chapter: New Applications
    Sub-Chapter: In Process

  16. This was very useful, I applied it in SharePoint 2013.
    Thanks for sharing!

  17. The step by step instruction is so helpful. I implemented it with no issue.

    Thanks!

  18. Thank You!!! I had to change my field names to match yours to get it to work, but it does and no javascript needed. Great Job:-)

Leave a Reply

Your email address will not be published. Required fields are marked *