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>
Hey Marc,
The cascading dropdowns are precisely what I’m looking for, but I can’t for the life of me get them to work! I’m not getting any js errors but my table is color coding either. I’m using two columns, customer and project, customers have multiple projects that only go with that customer.
What is meant by stripping the templates? Right now I have dvt_1 which contains my form fields and then dvt_2 (don’t know where or what happened, I think it’s from an earlier attempt) and dvt_3 which contains my dataview with the columns.
Any ideas?
Additionally I would like to eventually show just the customers or projects that are “active” as denoted by a third column in the dataview how do you think I could go about this?
Thanks for the helpful articles, they’re greatly enhancing my knowledge!
Continued working with the code, but now I am receiving a error on page: Error: ‘1.innerHTML’ is null or not an object. On debug it resolves to this line of code:
if(Column1TableRowsDetails[1].innerHTML == Column1.options[Column1.selectedIndex].text)
So I think it can’t “find” the id, any suggestions?
Thanks,
Sam
Hey Marc,
Almost there! I stripped the top row with the table headers. Also, because my dataview has additional columns I’ve had to adjust the index. After I have the kinks worked out I’m going to try to add an if statement to filter whether or not to display a customer or project based on them being active as determined by a yes/no column.
Thanks for the examples!
Sam:
I would encourage you to look at our new jQuery library for this (see update above). It takes most of the work out of things.
M.
Thanks Marc, I’ll check it out. All seems to be working out as is except for the minor problem that the first customer in the list doesn’t resolve any choices. I’m thinking it might have something to do with the & (ampersand) in the customer name not being ‘grabbed correctly from the table, but I’m still not sure. I’ll play around with the Jquery tonight.
Sam
Beautiful work!
The only place I am tripping up is how to hide the DVWP on the page …
Thanks! You can hide the DVWP one of these three ways:
* Go to Edit Page / Modify Shared Web Part and in the Tool Pane under Layout, check the Hidden box, or
* In SharePoint Designer, open the page and in the Design or Split View, right click the DVWP, choose Web Part Properties and under Layout, check the Hidden box, or
* Also in SharePoint Designer, in code view, change the IsVisible=”True” property of the DVWP to IsVisible=”False”
However, I would highly suggest that you use our jQuery Library for SharePoint Web Services SPCascadeDropdowns function. It’s much more robust and very simple to call.
M.
Thanks for the tutorial. I have tried the solution for cascading dropdowns at codeplex with no luck. I have read your first post regarding this, as well as this one, and I’m still missing how to setup the custom lists.
For a simple example, let’s say I want to have cascading dropdowns for #1 Make and #2 Model with the options below:
MAKE: Dodge, Ford, or Toyota
MODEL:
DODGE > Ram, or Dakota;
FORD > Explorer, or Ranger;
TOYOTA > Tundra, or Tacoma
How would I structure my custom lists, etc?
The way this ought to work is really just the way relational database tables ought to work. Your two lists would be Makes (Column=Title) and Models (Columns=Title[Model] and Make):
Then the call to $().SPServices.SPCascadeDropdowns would be:
$().SPServices.SPCascadeDropdowns({ relationshipList: "Models", relationshipListParentColumn: "Make", relationshipListChildColumn: "Title", parentColumn: "Make", childColumn: "Model" });Hello,
This artical is amazing! I was hoping some can help me with a requirement I need for a custom new form in Sharepoint. I need a user to have a text box appear when they select “Other” from a dropdown. The drop down is called “Reason for Reversal” and the text field to hide or display is called “Other Reversal Reason”. I’ve looked everywhere on the net for weeks and nothing seems to work. We also have IE7 and I’ve heard that that might be the reason I can’t get java to fire on onchange. Please HELP!
Thank you guys!
here is the code form Sharepoint Designer for the DropDown:
Reason for Reversal *
and here is the Text Field to hide or display:
Other Reversal Reason
Dave:
What you outline is totally do-able with JavaScript or jQuery. Unfortuntely, script or code don’t take in comments on WordPress. If you’d like some help, send me the code through the Contact form above.
M.
Hi,
i am completely new to using Sharepoint designer 2007 and i need to set up a cascading drop down list for two columns. Parent drop down = System Type and Child drop down= Problem.
please could you explain how i can do this?
SK:
Please check out the UPDATE link at the top of this post and take a look at my jQuery Library for SharePoint Web Services. This is the easiest way to create cascading dropdowns. If you have questions once you dig into it, please leave them in the Discussion on the Codeplex site.
Thanks!
M.
Hi Marc,
I’m glad to finally find a very easy solution that works for me …
I’ve got an additional question.
Does the list needs to be in the same site ?
Can I put it on Site Collection level …
Which part do I have to change ?
Gert:
Yes, you can store the relationshipList in the Site Collection root (and that’s probably a better place for it in many cases). You’ll just need to specify the relationshipWebURL to indicate where the list is OR use the list’s GUID instead of its name.
M.
Thx for the info,
It is possible to give me the correct format/syntax because it doesn’t work.
– I can’t find relationshipWebURL
– GUID is it with or without the brackets
Gert:
relationshipWebURL takes the form of the relative path to the site where the list lives, e.g. “/” or “/sites/Accounting” or “/Departments/HR”. If you choose to use the GUID, it should look like: “{E73FEA09-CF8F-4B30-88C7-6FA996EE1706}”. I’ll update the documentation to more clearly spell this out.
M.
M.