SharePoint: Extending the DVWP – Part 17: User-Managed Dropdowns – Creating a Relationship list

Author: Jim Bob Howard

Whether working with cities and states, sizes and colors, jobs and locations, etc., the ability to cascade—or limit—the choices of one dropdown based on the selection in another is a requirement for usability.

But before we start cascading our dropdown selections, we need to consider the data that will be in each one and the relationship between them. In many cases (sizes/colors; jobs/locations), it’s also imperative that a content owner be able to modify the selections in a list. The standard choice column type isn’t the best option for this because the data is "hidden" within the column definition.

Using a lookup column makes much more sense. And that’s what we need if we’re going to cascade our dropdowns.

Note: All of the content and thought process for this article arose from a discussion with Marc Anderson in January 2010 on the discussion forums for his jQuery Library for SharePoint Web Services (SPServices). You can read the entire conversation here.

Site Columns for Cascading dropdowns

What if the dropdown option choices are managed by one group, but used by another? For example, HR may need a strict definition of job titles, but a manager may be the one who is controlling the list of employees that use that title. So, how do we "reuse" data that might be useful company-wide?

Answer: Use site columns, which I discussed in the last article in this series.

Assuming you followed the advice in the last section, of the Countries and States, you already have the two data lists. You will need the related data to be stored in the respective lists before setting up the relationships.

It’s All in the Relationships

Cascading dropdowns depend on the relationship between the data in the two dropdown lists. So, first we create a list for each of the sets of data (e.g. Countries and States); then we set up a list that describes the relationship between the two.

Let’s now set up the relationship list.

1. Click Site Actions -> Create

From main site, click Site Actions -> Create

2. Under Custom Lists, click Custom List in Datasheet View


3. For this example, I’m going to relate Countries and States, so I’ll name it RELCountryStates

Creating the relationship list with a name that contains no spaces will help us down the road


Notice that I don’t want this to show up in the Quick Launch (your requirements may vary).

Give your list a name without spaces for now; this will set its ’static name.’ We’ll update the ‘display name’ in the next step.

4. You’ll now see that we have a list in Datasheet view. Let’s modify it, then we’ll load the data. Click Settings -> List Settings

RELCountryStates – Click Settings -> List Settings

1. Change the name
1. Under General Settings, click Title, description and navigation


2.  Change the Name to something descriptive (e.g. Country-State Relationships)

Set the ‘display name’ of the List

3. Click the Save button
2. Remove attachments
1. Under General Settings, click Advanced settings


2. Under Attachments, click Disabled

Disable attachments

3. Click the OK button (twice)
3. Add site columns
1. Click Add from existing site columns


2. Click both site columns and click the Add > button

If you assign your relationship site columns to a common group, they’re easy to find

3. Leave Add to default view checked and click OK
5. Click the list name in the breadcrumbs to return to the datasheet view

The breadcrumb will take you back to the datasheet view


Datasheet view with site columns Countries and States

6. You will need the related data to be stored in the respective lists before setting up the relationships. If you haven’t done that already, do it now. (Last time, I shared a method for quickly loading data in the lists used by your site columns.)
7. Now, all you need to do is add a record for every State in your site column list and relate it to its Country.

The site columns now have dropdowns for selecting only from valid values.

8. Loading these one by one will take forever, so we’re going to do something similar to what we did when we loaded the site column lists, by pasting the entire list into this table. But we’re going to have a bit of an issue with the Title column, which is required by default. Here are our options:
1. We can make the Title optional (and hide it from view, if desired)
2. We can make it optional and create a workflow that concatenates the site columns to make up a unique Title. (Not recommended.)
3. We can use Excel to create our entire Country-State Relationships table and paste the entire thing in here.

Next time, I’ll show you a quick way to load the data using the first option; then I’ll walk through it using Excel.

Author: Jim Bob Howard

Jim Bob Howard is a web designer / webmaster in the healthcare industry. He has been working with SharePoint since March 2009 and enjoys sharing what he has learned. He is a moderator and frequent contributor to Stump the Panel, and answers SharePoint questions on Twitter (@jbhoward) and via email ([email protected]).