Cascading Dropdown Columns in a SharePoint Form – Part 2
UPDATE 2009-08-26: We’ve translated this logic into our jQuery Library for SharePoint Web Services. I *strongly* suggest that you look at that as an option, as it is far more robust. And free!
In my post last week entitled Cascading Dropdown Columns in a SharePoint Form – Part 1, I showed how to create cascading dropdowns, meaning that a choice in the first dropdown would change the options in the second dropdown. The example I gave was from a client project which had over 20 options in the second dropdown. Because of this, the required JavaScript was a bit complicated because of the way that SharePoint renders the control for more than 20 options.
I got some great questions about that post and I promised to post again with a simpler example with a working demo. I’ve created a new Demo site with a CascadingDropdowns demo page on our Sympraxis Consulting Web site (all WSS!).
Notes to the critics: Sure, you could read the lists directly from the JavaScript. But this method seems like it will be easier to follow for the average “middle tier” developer (or non-developer). It’s also possible to simply embed the table of value relationships directly in the page (hard-wired), though I wouldn’t recommend it. (Use lists for what they are good for!)
Hopefully the page will be self-explanatory, but since this is a new way to demo my code, I appreciate any feedback you might have about how to make it more useful.
Looking at the page at the above link should give you all of the details, but the JavaScript looks like this:
_spBodyOnLoadFunctionNames.push("SetUpCascading");
var Column1 = new Object();
var Column2 = new Object();
var savedColumn2Options = new Object();
function SetUpCascading() {
// Find Column1 in the DOM (in this demo, the column is named "Region")
Column1 = getTagFromIdentifierAndTitle("select","","Region");
// Find Column2 in the DOM (in this demo, the column is named "State")
Column2 = getTagFromIdentifierAndTitle("select","","State");
// Attach the onchange event to Column1
Column1.attachEvent('onchange', Column1Changed);
// Call the onchange event to set the initial options for Column2
Column1Changed();
}
function Column1Changed() {
// Find the table with the Column1 / Column2 / ID information
var Column1Table = document.getElementById("Column1Table");
// Find all of the table rows
var Column1TableRows = Column1Table.getElementsByTagName("TR");
// See which Column2 options are allowed for the chosen Column1 option
Column2Count = 0;
// For each of the table rows...
for (var i=0; i < Column1TableRows.length; i++) {
// Get the table detail cells
var Column1TableRowsDetails = Column1TableRows[i].getElementsByTagName("TD");
// If the Region value in the table row matches the currently chosen Region
if(Column1TableRowsDetails[1].innerHTML == Column1.options[Column1.selectedIndex].text) {
// Add the option to the Column2 dropdown
Column2.options[Column2Count] = new Option(Column1TableRowsDetails[0].innerHTML,
Column1TableRowsDetails[2].innerHTML);
// Increase the count of available options for Column2
Column2Count++;
// For this demo, set the background color of the Column2 cell to green
Column1TableRowsDetails[0].style.backgroundColor = "green";
// If the Region value in the table row doesn't match the currently chosen Region
} else {
// For this demo, set the background color of the Column2 cell to red
Column1TableRowsDetails[0].style.backgroundColor = "red";
}
}
// Set the length of the options array
Column2.options.length = Column2Count;
// If there aren't any available choices, then disable the Column2 dropdown
if(Column2Count == 0) Column2.disabled = true;
else Column2.disabled = false;
}
and the key templates in the DVWP look like this. Note that the table must have a unique id, which is used by the JavaScript to find it in the page: table id="Column1Table".
<xsl:template name="dvt_1"> <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/> <table id="Column1Table" border="0" width="100%" cellpadding="2" cellspacing="0"> <xsl:for-each select="$Rows"> <xsl:sort select="@Title" order="ascending"/> <xsl:call-template name="dvt_1.rowview"/> </xsl:for-each> </table> </xsl:template> <xsl:template name="dvt_1.rowview"> <tr> <td class="ms-vb"> <xsl:value-of select="@Title"/> </td> <td class="ms-vb"> <xsl:value-of select="@Region"/> </td> <td class="ms-vb"> <xsl:value-of select="@ID"/> </td> </tr> </xsl:template>
Hi – i’m trying to implement the jQuery library for SharePoint on Codeplex(http://spservices.codeplex.com/documentation) but it isn’t working for me at all.
A little digging and I see that jQuery isn’t able to find the dropdowns by their title. I checked the page source and the drop downs do have the proper titles. I’m not terribly familiar with jQuery yet so I’m not sure what is going on. Is this something you have seen before?
Thanks.
I haven’t seen issues with the selectors before, but that doesn’t mean that it can’t happen! Post the details in the Discussions on the Codeplex site, and let’s see if we can’t get this working for you.
Note that versions up to and including v0.4.8 of the library WILL NOT work with jQuery versions 1.4 or later.
M.
HI Marc
if we have the same city name in two different states, will the same code work? or do we need to tweak the code?
I’m hoping you’re asking about the SPCascadeDropdowns function in SPServices rather than the code in this old post. Yes, it will work, as long as your list contains the data for the two cities in separate items.
M.
Hi, I have just began to explore the the wonders of Sharepoint and what it can do. My department is relocating and as part of the relocation I will be monitoring the progress, communication and queries that arise between my region and the shared service centre. Instead of having multplie list for each statkeholder I wanted one that can inorporate all the needs of the stakeholder that will enhance the relationships. To do this I wanted to cascading dropdowns. I got as far as creating the basic lists (Region;Category; Query) as you done. Now I am stuck as to what to do next. Grateful if you can advise on 1) Where the javascript data should be writen 2) What type of list used to create the form. 3) Can anyone write the javascript (I am only the administrator for my departments which is part of a group sharepoint) 4) can you recommend turtorials for sharepoint
Thank you for forthcoming advice
Kris
Kris:
This is an old post, and as I point out in the update at the top, I’ve built a function called SPCascadeDropdowns in my jQuery SPServices library. There’s lots of documentation on the Codeplex site for SPServices, and the code is far more robust and reliable than what is in this post.
M.
I have seen few requests on how to make the third dropdown values filtered not just based on the second dropdown but also the first dropdown that is no more duplicate records in third dropdown. So I have posted the solution in my blog taking your js files as reference.
you can find the blog here:
http://tipsinsharepoint.wordpress.com/2010/06/24/3-level-cascading-dropdowns-in-sharepoint-remove-duplicate-records/
or
http://tips-in-sharepoint.blogspot.com/2010/06/3-level-cascading-dropdowns-in.html
Thanks Marc for this wonderful post
Venkat:
I took a look at your post. You shouldn’t make modifications to the SPServices library bacause then your change will apply to all calls of SPCascadeDropdowns. What you probably should do is clone SPCascadeDropdowns for your own customization. It’ll be much easier to work with the unminified version of the script as well.
M.
Oh forgot to update about one other requirement. Lookup column with 20 or more items doesn’t get selected on single click, we have to either double click it or click else where on the screen to make the selection happen.
I have created a post which explains how to get the items selected with just one single click even for lookup columns which have 20 or more items in it.
you can find the blog here:
http://tips-in-sharepoint.blogspot.com/2010/06/sharepoint-lookup-fields-with-20-or.html
or
http://tipsinsharepoint.wordpress.com/2010/06/22/sharepoint-lookup-fields-with-20-or-more-items-single-click/
I hope this answers many questions.
Venkat:
What you’ve done here is to clone the JavaScript function from core.js, just altering one line out of many. It probably makes more sense for maintainability to bind your own jQuery function to override the click behavior and just fire the dblclick event on a click.
M.
Hi Marc,
thanks for the posts and SPServices utility. Regarding this bug (byDesign as of Microsoft), how can you do this in your own .js file (bind your own jQuery function), without to clone the JavaScript funtion from te core.js?
PS. I’m not a developer
Rob:
It’s definitely not a bug, but a feature, assuming you’re referring to the different control for 20+ items. It’s actually really useful if you know it is there and how it works. Most users have no idea that they can start typing in the input box and filter the options that way. I certainly never did.
That said, it’s simply a different construction of markup and totally possible to work with, as I do in SPServices. I never touch any of the out-of-the-box scripts, though I do call some of their functions. The trick is to pick apart the DOM with a DOM inspector so that you can understand what’s going on and change the behavior.
M.
i am using the cascading functionality in new item form. Is it possible to use this functionality in two forms (with cascading dropdowns) in the same webpart page?
I’m hoping that you mean the SPServices cascading functionality and not the crufty old stuff in this post.
I haven’t tried to use SPCascadeDropdowns with two forms on the same page, but thinking about it, I think it *may* work. You wouldn’t be able to submit the two forms at the same time, though.
M.
Thanks for the quick response Marc.
yes, i am using the SPCascade Dropdowns functionality and i just need to submit only one form.
Here is the scanario: i have two forms for two areas (area1, area2) on the same page and are being displayed on selecting the respective radio button. (radio button are named A1, A2).
i tried to change the function name in CascadingDropdowns.js file and repsective changes in the SPservices file (inorder for the form to recognize which file to refer), but as i edited the function name in SPservices file, it wont apply the cascading functionality. am i on the right track?
can you suggest me?
one more question: what if i want to use the different function name i.e. can i use “SPServices.SPCascadeDropdowns1” instead of “SPServices.SPCascadeDropdowns” in the code file?
thanks,
HK
I don’t think this is going to work. It sounds like you will have duplicate column names on the page, and that will break it. You seem to be thinking of the script like a class you can reuse, and that’s not the case.
M.
Hi Marc,
As i said you : i need two new item forms with cascading functionality in the same page, i tried it by placing the two form webparts in different zones BUT the cascading functionality is getting applied to the second form (whose script loads after the first one’s script). can you please provide me your suggestion?
can’t we get the multiple cascading functionality(using jquery spwebservices) in the same page?
also the link for cascading demo pages is broken….
thanks in advance
If you have the same column on the page twice, then SPCascadeDropdowns is going to apply to the first one. There’s no logic there to distinguish between multiple occurences of the same column, and therefore multiple forms for the same list. You probably will need ot customize SPCascadeDropdowns in some way to meet your needs on this one.
And yes, I broke the cascading dropdowns demo page and I need to fix it…
M.
just to let you know…i am using the different column names and list names
It’s tough to really discuss this here because you can’t easily post your code. If you’d like more help, use the Discussions on the Codeplex site instead.
M.