Cascading lookups in SharePoint using jQuery

One common request in SharePoint is the ability to cascade lookups when completing a list form. For example, let’s say you have two drop down fields on your form. One is the State and the other is the City. You want the user to be able to select a State and then the City drop down should automatically show the valid cities for that state.

In the past we have used a couple of approaches to solving this problem. Either building custom forms or using a 3rd party utility such as the Bamboo List Selector. Building a custom form just to provide this functionality is overkill and can easily cause more problems than it is worth. The Bamboo List Selector works fine, but requires deploying to the server and a bit of configuration.

Today, with the help of jQuery, we have another option that requires no code deployment and very minimal configuration. SPServices jQuery Library for SharePoint Web Services uses jQuery along with SharePoint’s Web Services to make the solution very easy.

Here is a quick overview of the steps:

  1. Download the JQuery Library. This should be a JavaScript (.js) file that you can save locally.
  2. Download the SPServices jQuery Library for SharePoint Web Services. This should include a couple of files but the one you should be concerned with is the jquery.SPServices-{version}.min.js. It is a compressed version of the JavaScript and is about half the size as the full version.
  3. Take these two JavaScript (.js) files and upload them into a SharePoint Document Library. It is recommended that this be a library on the root site and all users need to have at least read permissions to it.
  4. Now you need to setup your data if it is not already. You should have at least three lists. Here is a quick example.
    1. Create a “States” list with only the Title column. You can add a few states to get started.
    2. Create a “Cities” list with the Title column and an additional lookup column that does a lookup to the States list. Add a few cities for each state.
    3. Create another list that has a lookup to the States list and another lookup column to the Cities list. (The Title field in both)
    4. The next step is to the add the actual jQuery that will make all of this functional. It is recommended that this be added directly to the page using SharePoint Designer or some other means. But a Content Editor web part works as well. Just keep in mind that a Content Editor web part is more accessible to users.
      1. In the main list click on New and you will be taken to the “New Item” form. You will need to add a Content Editor Web Part to this page. To do this through the UI you need to add “ToolPaneView=2” at the very end of the URL in the address bar.
      2. Add the following script to the Content Editor Web Part. The first two lines are references to the JavaScript files you uploaded earlier. The actual configuration is well documented so I won’t try and repeat what it does or how it works. In a nutshell, you’re saying the Cities list defines your relationship and the State Column is the parent and the City (Title) column is the child.

“javascript”

type=

“text/javascript”

src=

“../../Documents/jquery-1.4.2.min.js”

>

“javascript”

type=

“text/javascript”

src=

“../../Documents/jquery.SPServices-0.5.4.min.js”

>

“javascript”

type=

“text/javascript”

>

$(document).ready(

function

() {

$().SPServices.SPCascadeDropdowns({

relationshipList:

“Cities”

,

relationshipListParentColumn:

“State”

,

relationshipListChildColumn:

“Title”

,

parentColumn:

“State”

,

childColumn:

“City”

,

debug:

true

});

});

When you save your changes, your state drop down should filter the city drop down.

LookupExampleLookupExample2

jQuery takes most of the complexity out of writing JavaScript and works great in this type of scenario. The SPServices jQuery library provides some additional functionality as well so be sure to check out the CodePlex site for more information.

2 thoughts on “Cascading lookups in SharePoint using jQuery”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.