SharePoint: Extending the DVWP – Part 20: Cascading Dropdowns – Applying the jQuery
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.
- Add a CEWP to the page
- Click the Source Editor… button
- Paste the below jQuery script into the Text Entry – Webpage Dialog
- Make the necessary changes to represent your list and column names
- Click Save
<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>
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]).
- SharePoint: Extending the DVWP - Part 1: Layout Enhancement - Rearranging Columns - Default and Edit Templates
- SharePoint: Extending the DVWP - Part 2: Layout Enhancement - Rearranging Columns - Insert Template
- SharePoint: Extending the DVWP – Part 3: Getting it All on One Line - DVWP Function Action Links
- SharePoint: Extending the DVWP – Part 4: Turning DVWP Action Links into Buttons
- SharePoint: Extending the DVWP – Part 5: Doing Stuff Before Save on Submit - PreSaveAction()
- SharePoint: Extending the DVWP – Part 6: Examining the Form Action Links
- SharePoint: Extending the DVWP – Part 7: Creating a Form Action Workflow
- SharePoint: Extending the DVWP – Part 8: Creating a Form Action Workflow - The After Math
- SharePoint: Extending the DVWP – Part 9: Oops! Failed Setting Processor Stylesheet
- SharePoint: Extending the DVWP – Part 10: Passing Workflow Variables to a Form Action Workflow
- SharePoint: Extending the DVWP – Part 11: Getting More Form Fields to the Workflow
- SharePoint: Extending the DVWP – Part 12: Adding More Form Fields from the Data
- SharePoint: Extending the DVWP – Part 13: Putting PreSaveAction() to Work – Creating Variables
- SharePoint: Extending the DVWP – Part 14: Putting PreSaveAction() to Work with jQuery
- SharePoint: Extending the DVWP – Part 15: User-Managed Dropdowns with Site Columns
- SharePoint: Extending the DVWP – Part 16: User-Managed Dropdowns - Loading Data
- SharePoint: Extending the DVWP – Part 17: User-Managed Dropdowns – Creating a Relationship list
- SharePoint: Extending the DVWP – Part 18: User-Managed Dropdowns – Loading the Relationship list – Part 1
- SharePoint: Extending the DVWP – Part 19: User-Managed Dropdowns – Loading the Relationship list – Part 2
- SharePoint: Extending the DVWP – Part 20: Cascading Dropdowns - Applying the jQuery
- SharePoint: Extending the DVWP – Part 21: Cascading Dropdowns - Three-tier Cascade
- SharePoint: Extending the DVWP – Part 22: Creating Title Based on Other Fields with jQuery
- SharePoint: Extending the DVWP – Part 23: Creating Title Based on Other Fields with a Workflow
- SharePoint: Extending the DVWP – Part 24: A Note to Readers
- SharePoint: Extending the DVWP – Part 25: Using an Audit Trail by Creating List Items with SPServices
- SharePoint: Extending the DVWP – Part 26: Modifying the Edit Template
- SharePoint: Extending the DVWP – Part 27: Adding an Alternate Edit Template to a DVWP
- SharePoint: Extending the DVWP – Part 28: Massage the Remove Template
- SharePoint: Extending the DVWP – Part 29: Modifying Form Action Workflows on the remove Template
- SharePoint: Extending the DVWP – Part 30: Using EasyTabs with Filtered DVWPs to Make Data Manageable
- SharePoint: Extending the DVWP – Part 31: Filling in Default Data on the insert Template with jQuery
- SharePoint: Extending the DVWP – Part 32: Filling in Default Data on the insert Template with Multiple DVWPs
- SharePoint: Extending the DVWP – Part 33: Modifying Total and Subtotal Row Layouts in DVWP
- SharePoint: Extending the DVWP – Part 34: Using Icons for Form Action Links
- SharePoint: Extending the DVWP – Part 35: Putting it All Together
- SharePoint: Extending the DVWP – Bonus: Fixing the Insert Form Action When "No Matching Items"
- SharePoint: Extending the DVWP – Bonus: Creating a Title Based on Dropdowns with jQuery
Jim,
Have to say I had some hrd figuring out where you where going with the 3-4 previous posts. I will have to go back at them b/c it seemed to me there may be easier way to create and populate the 2 or multiple parent/ children lists.
The CascadeDropdown operation in SPServices http://spservices.codeplex.com/wikipage?title=%24%28%29.SPServices.SPCascadeDropdowns&referringTitle=Documentation was initialy – Marc can correct me – to have those cascading dropdowns in custom forms.
I do not see at 1st sight anything different in the jQuery code…does the operation works in a DVWP without further changes?
Since the operation is certified on SP2010 and that ‘inline’ editing is no available in SP2010 in a ‘nicer’ way (got to love Ajax), would it make sense to have it work in SP2010 as well?
I am only assuming you tested it for 2007 lokoing at the header styling (and being posted at EUSP not EUSP2010).
I will have to test it but it seems it *should* work.
Nice work!
Greg
Hi Jim,
Thanks for your postings, they are very insightful. I am however having problems with getting this to work. I’m an experienced SharePoint Admin on the infrastructure and out of the box customisation side, but new to using jQueries.
I am unable to get the cascades working. Your first point notes: 1.Add a CEWP to the page. Can i ask which page you’re referring to. Is it the NewForm.aspx or EditForm.aspx or AllItems.aspx? I suppose you would need to open SharePoint designer to add a CEWP to the NewForm.aspx as it does not allow you to add Web Parts to this page
In addition, which list out of the few that have been created in previous posts should i be adding this code to, I’m a little lost as to how many lists i should have by now.
Sorry if I’ve missed a key step that may be obvious.
Many Thanks,
Jon
Hi Jon,
You would add the CEWP to whichever page you want the dropdowns to cascade. And you wouldn’t need SPD to do that. To make the standard pages you mentioned editable, add the following to the URL when you browse to the page:
&PageView=Shared&ToolPaneView=2
(For AllItems.aspx, you’ll need to replace the first & with a ?. ex: http:///Lists//AllItems.aspx?PageView=Shared&ToolPaneView=2
Your lists will consist of:
1. The list where you will displaying the cascading dropdowns
2. One list for each dropdown (represented in your list as a site column)
3. One relationship list for each cascade (this sits “between” the two and describes the parent-child relationship [ one to many ] between the columns.
For example, if you were collecting address information for orders and wanted cascading dropdowns for country, state, and city, you would have:
1. The Orders list, with site columns: Country, State and City
2. Three lists to feed the site columns: Country, State and City
3. Two relationships list: States-in-Countries, and Cities-in-States
All of the CEWP/jQuery stuff goes on pages in the Orders list.
Does that help?
Blessings,
Jim Bob
Firstly, thanks for your reply and extra advice Jim.
I have the task of putting together Product-Family and Product-Names together in a cascading drop down called Products, so this is only two levels that are required.
1. I have my Site Columns: Product-Family and Product-Name, which are linked to two lists with the same names. Both have been populated with the correct data.
2. I have a relationship list called Family-Name, with columns Title, Product-Family and Product-Name and populated with the correct matching Product-Family/Product-Name data.
3. I have the “working list” called Products where I wish the cascades to work.
I wonder if the problem is the Jscript, perhaps I’ve not got the correct entries. I have downloaded the appropriate jscript files and placed them into a central document library on the site I can reference http://sitename/jscripts.
Would you have any other advice? I didn’t read anything about installing addtional software onto my SharePoint Farm, so i assume this is all client side customisation.
Many Thanks,
Jon
Yes, it’s all client-side.
Try adding a debug: true to see if SPServices will tell you what’s wrong.
So, in my code above, I would add a comma after “State” and add debug: true on the next line:
.
childColumn: “State”, // ‘Display Name’ of column in working list
debug: true
}
Also, check out the Codeplex discussion where I worked this out with Marc:
http://spservices.codeplex.com/Thread/View.aspx?ThreadId=81739
Great stuff. I’ve got it to work by adding the code to NewForm.aspx via SharePoint Designer, directly in the code rather than as a CEWP which i couldn’t attach to that page.
Now i have the same problem as SV.
Perhaps its the updated file?
Thanks :-)
Hopefully the last time I post with a question :-)
The error I’m receiving now is:
Error in function
SPServices.SPCascadeDropdowns
Parameter
relationshipListParentColumn: Product-Family or relationshipListChildColumn: Product-Name
Message
Not found in relationshipList Family-Product
Any thoughts?
Looks like you’re getting close! :) (And ask as many questions as you need to get your project done!)
Remember to make sure that for the relationshipListXColumns are the static name in the relationship list.
Blessings,
Jim Bob
Wahey! Got it to work, the full URL wasn’t correct for the relationship list. I also created my site columns at the Site Collection level rather than the sub-site level.
Next issue i have..is slightly unrelated, but still valid i think. The two columns I’ve replaced with the cascading drop-downs (originally simple ‘choice’ field) were managed metadata properties which were mapped to documents within my document library. I had configured an Advanced Search to pick up these values using the Advance Search web part by adding some xml in the properties for each of the managed meta data properties.
Of course now, the meta-data properties that were mapped now point to Site Columns and not my document library columns. This means that any search performed on those specific columns now only bring back items in the relationship list and not my document library…
Tricky one..eh :-)
Tricky, yes… and a bog I’ve not waded into. :) Hopefully, someone else can help with this… My first contact would be Marc Anderson, if I were you.
Glad you got the cascading dropdowns working, though!
Blessings,
Jim Bob
Jim,
I think i did every steps right, but, at the end the debugger keeps telling me:
Error in function
SPServices.SPCascadeDropdowns
Parameter
parentColumn: Countries
Message
Column not found on page
(I used almost the same example as you did –> Countries/Regions)
Really nice work by the way!
S
Dear SV,
Make sure that you are using the “Display Name” of your parentColumn (as it appears on the page).
Blessings,
Jim Bob
Jim,
Thanks a lot for your quick answer.
Unfortunatly, it is exactly the same name.
Moreover, I am using the latest version of Jquery so I don’t think that could be the problem (in reference to your discussion with Marc).
That is pretty fustrating, it keeps saying that it cannot find the column while I can see it just under the message!
S
Just a quick question, Can the casading fields pull from one list instead of three? For example, have one list
with country, state, city columns.. Also, is there a limit on how many records can be in a list?
Thanks
Tom
Dear Tom,
I suppose it could.
But, the reason for the three is that the two data-only lists have no duplicate data. Whereas the relationship list will have multiple entries for country for each state in that country. If you added the cities you would then have multiple entries for each country/state pair, which might end up producing multiples in the states dropdown.
One way you might mitigate multiple levels of cascading into one relationship table is to have only parent and child columns. So, for each state you would have a country/state entry as parent and child. Then, for each city you would have a state/city entry as parent and child.
You wouldn’t be able to use a site column for though because you need to be able to choose countries and states in the Parent column, and states and cities in the Child column. You could do it with text columns, but you’ll have to work out a mechanism that will make sure typos in the relationship table don’t mess up your cascades.
As for a record limit, only one that SharePoint or your database would impose. I’ve not run into a limit, so others might be able to answer that question better than I could.
Hope that helps.
Blessings,
JIm Bob
Fantastic series! I had a little trouble getting the “Not found in relationshipList ” error that Jon M mentions above. To clarify for others on how I fixed it, I used the QUID instead of the URL, and the ’static name’ is the internal name (The one you get using the copy item Xpath)
relationshipList: “{5e3716cb-d7a1-4c20-ae94-fb422790b123}”, // ‘Display Name’ of relationship list
relationshipListParentColumn: “ABC_x0020_Department”, // ‘Static Name’ of column
relationshipListChildColumn: “ABC_x0020_VP”, // ‘Static Name’ of column
relationshipListSortColumn: “ABC_x0020_VP”, // ‘Static Name’ of column
parentColumn: “ABC Department”, // ‘Display Name’ of column in working list
childColumn: “ABC VP” // ‘Display Name’ of column in working list
Again, fantastic series. I wish I’d had this ages ago!
Thanks, Heather! I’m glad it was helpful for you.
Blessings,
Jim Bob
Nice posts here.. Following the questions.. using the jquery in the above how could it be used to autopopulate metadata properties. For example, I will have a project doc lib, there will be different docs
proposal, sows, design / requirements. We want to classify the docs using COBIT standards + some internal metadata such ProjectName, WBS, Line of Business, PM, etc. Given, the number of docs that is required over the project life cycle it would be a burden on the PM to keep entering the metadata properties for each doc.. Would be possible to autopopulate these ?
Thanks TomW
Dear Tom,
Of course, you can autopopulate just about anything as long as it makes sense.
For example, for Full-Time Employee example I’m using in this series, I autopopulate a few things on insert based on where the user is coming from. Because I want the respective manager for each location to only update their own employees, I created individualized pages for each location. (The managers have access to all of them, which allows them to easily transfer employees among locations.)
Each page filters on its location, so on insert, I autopopulate the location w/ jQuery. Also, there are a couple of different “types” of employees that are grouped separately for reporting purposes: FTE (full-time employees), PRN, Other (PT, Temp, Float, etc.) On each page, I separate these out further into their own DVWPs and then stack them with Christophe Humbert’s EasyTabs. So again, depending on which tab they’re on, I autopopulate for that tab.
So, I could see in your case that you’ll be working within a Project (so that can be autopopulated) on a particular WBS, Line of business, PM, etc. section so you can autopopulate those, too. Additionally, if you’re using content types for the Doc Lib, you can default metadata for that CT…
Does that help?
Blessings,
Jim Bob
Jim Bob
Yes this helps > Will need to experiment .. the auto polpulate will feed off from a sharepoint list that has
X columns that has been extracted from SAP / XRPM. So this classification would be both for
the Project side/ Production support and the Production Acceptance docs. They are looking to be able
to search by the above metadata properties.. And yes we will be using content type for the different project documents. Thanks again for quick repsonses!!!
You’re very welcome!
I look forward to hearing how it works out. If you get stumped, don’t forget to jump over to Stump the Panel (http://www.EndUserSharePoint.com/STP) and let’s talk it out.
Blessings,
Jim Bob
We have Jquery working on our site, but we have some serious issues with load times for NewForm and EditForm. After analysis, it appears that the JQUERY is slowing the pages down by 4 to 5 seconds. We have 2 sets of cascading dropdowns – a 4 tier set and a 2 tier set. Any ideas on why jquery is doing this? Analyzing this using http watch, it appears that the JQUERY is doing postbacks. I’m assuming that Jquery is making webservice calls which is the bottleneck.
Hec:
Jim Bob passed your question over to me. (I answer questions about SPServices as quickly as I can if you post them on http://spservices.codeplex.com, too.)
You’ve guessed right that SPServices is doing Web Services calls to get the info from the related lists. That’s the only way it can work
M.
…and continuing…
There’s a performance penalty for using this logic, but it almost guarantees that you will have good data in your list. For that reason, I see the performance hit as acceptable. Keep in mind that the more related cascades you have and the higher up the chain you make a change, the more Web Services calls SPServices will make.
M.
Thanks for the reply. Is there a way only to load the jquery webservice only if the dropdown is selected and not on the page load?
Thanks again.
Hector
First off, this forum and article are EXTREMELY helpful, so thank you everyone for the good questoins and very helpful replies.
Now onto the question at hand…
I have a top dropdown (the one which is cascading the value) which I want to control multiple dropdowns below, rather than just one. Also, the lists in each of the following dropdowns are the same to allow the user to select, so there will not be a country -> state -> city type association. Just a simple country -> state, country -> state, etc association. I know I can allow for multiple selections, but for each selection I have another associated field which collects some information regarding the “state” selection. How can I get the top dropdown to affect the dropdowns below it?
Thanks!
Dear Reid,
I will have to defer your question to Marc Anderson, the author of the SPServices library, who can more adeptly answer questions about special circumstances when using his library. (Heck, I couldn’t have done it without his indepth help!)
http://spservices.codeplex.com/discussions
Blessings,
Jim Bob