Author: Jim Bob Howard

It’s been awhile in this series since we looked at a DVWP. We’ve been building on an idea of using cascading dropdowns—where the selection of one dropdown limits the selections available in the next.

The examples we’ve covered in the last few articles have been a generic Country/State example, where choosing Country=USA would limit the States list to only the 50 United States and the District of Columbia, whereas Country=Canada would limit the States list to the 13 Canadian Provinces. We’ll take that idea just a little further as we look into the jQuery that allows us to cascade these dropdowns.

The Problem

I’ve created a sample list that includes the Country and State site columns. Here’s the problem:

This is perfectly legal and could be stored in the database like this.

Unfortunately, even though both columns are being supplied from site columns, where the "possible" data is controlled, this combination is meaningless. Alabama is not a Canadian Province.

The problem is exacerbated by the fact that the site column will sort the data, so if we have valid countries of USA, Canada, and India, the states, provinces, and territories will all be mixed together:

That’s why we need cascading dropdowns.

The Solution

If you’ve read my jQuery to the Rescue articles, you’ve experienced my affinity for jQuery and especially Marc Anderson’s jQuery Library for SharePoint Web Services (SPServices). So, we’ll jump right into the details.

1. Add a CEWP to the page
2. Click the Source Editor… button
3. Paste the below jQuery script into the Text Entry – Webpage Dialog

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js" type="text/javascript"></script>
<script src="../../../js/jquery.SPServices-0.4.8.min.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
    $(document).ready(function() {
        $().SPServices.SPCascadeDropdowns({
            relationshipWebURL: "/", // URL of site, full or relative; full more reliable
            relationshipList: "Country-State Relationships", // 'Display Name' of relationship list
            relationshipListParentColumn: "Country", // 'Static Name' of column
            relationshipListChildColumn: "State", // 'Static Name' of column
            relationshipListSortColumn: "State", // 'Static Name' of column
            parentColumn: "Country", // 'Display Name' of column in working list
            childColumn: "State" // 'Display Name' of column in working list
        });
    });
</script>

4. Make the necessary changes to represent your list and column names
5. Click Save

Now the State dropdown is completely controlled by the Country selection:

Country = None

Country = India

Country = Canada

Country = USA

Next time, we’ll add another cascading dropdown to make it three-tier, as we continue Extending the DVWP.

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]).