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 Marc,
I have three columns that I need to work with to create cascade columns. Would I just include one more for/if statment in the javascript? Using your code:
for (var i=0; i < Column1TableRows.length; i++) {
var Column1TableRowsDetails = Column1TableRows[i].getElementsByTagName("TD");
if(Column1TableRowsDetails[1].innerHTML == Column1.options[Column1.selectedIndex].text) {
Column2.options[Column2Count] = new Option(Column1TableRowsDetails[0].innerHTML,
Column1TableRowsDetails[2].innerHTML);
Column2Count++;
}
Column2.options.length = Column2Count;
if(Column2Count == 0){
Column2.disabled = true;
}else{ Column2.disabled = false;
}
For (var i=0; i < Column2TableRows.length; i++) {
var Column2TableRowsDetails = Column2TableRows[i].getElementsByTagName("TD");
if(Column2TableRowsDetails[1].innerHTML == Column2.option[Column1.selectedIndex].text) {
// DO THE SAME THING LIKE ABOVE
Hi Marc,
If I have 3 columns to work with in this case, then do I just add one more similar FOR/IF statement lik you did above?
So, I would end up with 4 columns in the Data View web part: Col 1, Col 2, Col 3, ID. I want to check to see if this is right approach.
Xixi
XiXi:
Yes, that would be the basic approach. You’ll need to have separate DVWPs with unique IDs which contain the allowable values and either generalize the JavaScript (preferable) or make copies of it.
M.
I just want to be clear. Do I need to have two separats lists?
One is store relationship between Category 1 and Category 2, and the other list is to store relationship between Category 2 and Category 3.
Or, can I just create one big list that contains the cascading relationship from Category 1 to Category 2 to Category 3?
Thank you for your reply.
Xixi
It doesn’t actually matter how many lists you have, but you need a way to contain the relationships and get them on the page. I would have three lists, as that is the most normalized way to do it. (All of the usual DB rules ought to apply with SharePoint lists.)
M.
Hi Dan
I duplicate the 3 list (Regions/States/Sales Opport)
I modify the default EditForm.aspx and add the XSL
I added also the javascript in the folder “JavaScript”
But I don’t know how to link the JS to the page. Could you explain to me how to do it?
Notice that I’m not a designer. Detail explanation will be appretiate
Thanks and regards,
Mariana
(Argentina)
Marc,
Can’t thank you enough for this, it’s amazing to me that this is omitted from SP. I was able to get your method to work for a few test lists but now that I’m trying to use it on the production list I’m running into a snag. Here’s the rundown on the lists, my fields are different but it’s easier this way:
RefList1:
Region (text)
RefList2:
Region (lookup to RefList1)
State (text)
MainList:
Region (lookup to RefList1)
State (lookup to RefList2)
I receive: Error: ‘Column2.options’ is null or not an object
It’s obviously not seeing the State field as it remains the built-in lookup. Oddly, when I change it to a choice column it displays fine.
Any ideas?
Mark,
I have been trying to adapt your code to accomodate lookups for more than 20 items, but still can’t get it to fire. Would you mind if I post my code here for some addtional feedback?
-MO
Marc, ignore my last, I just remembered you posted that if the list contains more than 20 items you need to do it differently! My 2nd list has 27 items so there’s my problem!
All:
Sorry for the delayed response to your questions. I was on Martha’s Vineyard with (gasp!) no real Internet connection other than my iPhone. As much as I love my iPhone, it’s just not condusive to writing longer stuff, at least for me.
Mariana: You can add the JavaScript into your page by enclosing it in script tags. Probably the easiest recommentdation would be to find some existing script (most pages have at least a little) and put it with that script. This will ensure at least that it is in a place where it will run.
Mikey: Are you still having issues? As I think you noticed, if you have 20+ options, you’ll need to look at my earlier post showing the different method for this: http://sympmarc.com/2009/07/14/cascading-columns-in-a-sharepoint-form
Michael: I’d be glad to try to help if you are still having problems. It’s best to go into the Split or Code view and add the ID directly into the code. It’s pretty easy to ask SharePoint Designer to do things that it doesn’t really understand.
M.
Hi.
After having a lot of pain… I made it work!!! it is really awsome!! thank you very much. This helped me really really a lot.
Nice! :) Thanks for this post. Will this also work on WSS 2.0?
Mikaela:
I don’t have a WSS 2.0 instance to look at, but my guess is that it probably won’t work. I lot of underlying constructs changed from 2.0 to 3.0 and I know that many of the form controls did. If you want to send me your specific code, I can take a look. (You won’t be able to post the code in a comment — use the Contact page above.)
M.
Does it work with a lookup column that allows multiple choices. For example, ‘State’ is a multi-value lookup field. Thanks!
Jeff:
No, this won’t work for multi-selects. That is a different control altogether. You could certainly adapt the JavaScript, though. Let me know if you come up with something which works!
M.
Thanks for your timely response! You have done a great job. Hope it can be extended to multi-choice lookups in the future.