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>
Sorry, have hopefully 1 last question.
I just populated my list and I have exactly 20 items in it, and it stops working.
I deleted one, and it works again.
What did you have to do, to make it work with more then 20 items?
Thanks again
Chris.
Ah, now you’ve got the other control which SharePoint uses for 20+ options. My earlier post gives you the JavaScript for that.
M.
Ya I’ve been working with the first version, trying to adapt it to work with my larger list.
I’ve been replacing parts, and making some progress fixing JS errors as I go.
Had a couple questions
On this line
var Branches = getTagFromIdentifierAndTitle(“input”,””,”Category”);
If I leave this as “input” I get an error of
choices null or not an object.
so I thought since on the newer version, it’s ‘select’ but when I try that I get
savedBranchesChoices is undefined.
If it would help I could post all my code, but don’t want to clutter up your forum.
Unfortunately this page work in IE only. Firefox doesn’t filter the states.
That’s true. Keep in mind that Microsoft doesn’t claim to support browsers other than IE and this primarily applies to the interactive pages, such as forms, and the so-called “active” content (menus, etc.). With SharePoint 2010, Microsoft os saying they will do much better, supporting all major browsers.
Any suggestions on how to make it work with Firefox?
M.
Marc,
In case the item count in the lookup column “States” increases to more than 20 we just would need to change the line to
Column2 = getTagFromIdentifierAndTitle(“input”,””,”State”);
and use choices to loop through the options. Is that all to be done?
Was wondering if the item count increases to 20 then what.
Thanks,
Akshay
Unfortunately, yes, the method needs to change once you hit 20+ options. Probably my next iteration of this should be to generalize it to work regardless of the number of options. It’s been a mystery to me why Microsoft decided to use the different control at 20+ options, but that’s just the way it works.
M.
Thanks Marc.. Will get back and write on your post, if I m finding a way out to bridge the gap.
Cheers,
Akshay
Hello Marc,
I’m hoping you might be able to help me out. I have a custom list with a “choice” column that also has the “add your own value” option on it.
Here’s what I’m looking for: When a user inputs on the “add your own value” option, how do I make it so that this new value is added to the choice dropdown?
I’ve inquired with endusers.com to no avail. I’ve tried creating a SPD workflow – no success there either.
Do you know of a way to make this happen?
Your assistance is greatly appreciated.
Dave M.
David:
This is a tricky one. I think you’ll need to do this in code (Visual Studio), as none of the standard SPD workflow actions will help you with this. You could do this with a workflow or the Lists Web Service might also be useful. It’s possible that you could get it to work in JavaScript using the Lists Web Service if you don’t want to go the Visual Studio route.
Also check this post from Jan Tielens about using jQuery to call the Lists Web Service.
M.
Hi Marc,
What a great post !
I reproduced quite exactly your demo page and after a little work, I got it working.
The thing is, I have more than 20 items in the second dropdown (actually over 300). Is there anyway you could give me a hand with this ? I guess the changes would have to be made in the CascadingDropdowns.js file.
Btw, I’m not a programmer, but I can adapt code.
Thanks a lot
Yvan
Check my first post on this. :)
http://sympmarc.com/2009/07/14/cascading-columns-in-a-sharepoint-form/
M.
Marc,
Thanks for the post!!!
Everything works like charm. Except i am getting the below mentioned error.
“An error occurred processing the data view. The XslText property is empty.”
Thanks,
Anu
Usually you will see this error if you partially inserted a DVWP on your page. Look for a DVWP without any “insides” and delete it.
M.
Marc,
I’m having trouble adapting this newer version of the code to a Lookup list that has more than 20 items..can you post an example of how to do this with two columns that are both Lookup fields with more than 20 items in the Parent List?
BTW, is there an upper limit of how many can be handled? Both items in the Parent list have more than 1400 items.
Thanks a bunch,
-MO
More than 1400 items!?!? I don’t think that this is necessarily the best method for you, then. (It’s absolutely do-able with this method.) How are your users going to manage to use these immense dropdowns? You might want to look at something more sophisticaed, like a custom field.
M.
Well, if you have suggestions of a better method, I’m open to suggestion.
My primary field is “unique” and I was using Workflows, originally to populate the other fields–but the customer wants the fields to be populated in the Form, not after creation (as workflow now handles the updates.)
What kind of Custom form are you suggesting? I had trouble following your first post and the code that would need to be change in Post 2.
Or can I use the Post 2 code as is? Doesn’t seem to be working.
-Mike