Cascading Dropdown Columns in a SharePoint Form – Part 1

UPDATE 2009-08-26: I’ve translated this logic into my 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!

UPDATE 2009-07-19: I’ve added a second post on this with a simpler example. It also contains a link to a demo page which shows how it all fits together.

Over in the MSDN SharePoint – Design and Customization Forum there are frequent questions about “cascading” dropdowns.  This means having two (or more) dropdowns “connected” based on the choices the user makes.  I dug out an example from a client project that demonstrates this technique fairly well so that I could refer folks to it.

In this example, the client had Divisions, each with a set of Branches.  We stored the Division / Branch pairs in a simple SharePoint Custom List in the root site of the Site Collection which had just those two columns.  (We actually used the Title column in the list for the Branch just to keep it really simple.  This list wasn’t one that anyone outside of the administrators was going to be looking at.)

We wanted to have the cascading work in the forms for a Document Library where each document would be categorized as belonging to a Division and Branch.  (The Division and Branch columns were both Lookup columns into lists at the Site Collection root.)  To make the Division and Branch columns cascade in the forms, we needed to have these pieces in place:

  • A hidden Data View Web Part (DVWP) which put the Division / Branch pairs into a table on the form for use by the
  • JavaScript to change the available values for Branch once the Division was selected.

We didn’t actually need to customize the form itself; we left the default List Form Web Part (LFWP).  The major reason for this was that we had upward of 30 Content Types that could be stored in the Document Library, and we wanted the Content Type selection to work the clean, default way.  All of the changes we made to the form were done with the client-side JavaScript.

Here are the main XSL templates from the hidden DVWP.  Note that there is nothing fancy about this DVWP except that I gave the table an id=”BranchesTable” so that I could locate it in the DOM with the JavaScript:

<xsl:template name="dvt_1">
    <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/>
   
<table id="DivisionTable">
    <xsl:for-each select="$Rows">
        <xsl:sort select="@Branch" order="ascending"/>
        <xsl:call-template name="dvt_1.rowview" />
    </xsl:for-each>
   </table>
</xsl:template>

<xsl:template name="dvt_1.rowview">
<tr>
<td>
      <xsl:value-of select="@Division"/></td>
<td>
      <xsl:value-of select="@Title"/>
    </td>
     
<td>
      <xsl:value-of select="@ID"/>
    </td>
  </tr>
</xsl:template>

Next was the JavaScript.  As I always do, I stored the JavaScript in a Document Library in the root site of the Site Collection called JavaScript.  This way I could reuse it anywhere I needed to.  There were a lot of other things I was doing in the JavaScript to make the form work more richly, but I’ve snipped out the pieces that are relevant to this specific task (hopefully not breaking anything in so doing!).

First, I needed to attach an onchange event to the Division column’s dropdown.  This code snippet was called as part of the form load with _spBodyOnLoadFunctionNames.push();

// Find the Branch column
var Branches = getTagFromIdentifierAndTitle("input","","Branch");
// Save the Branch choices so that we can rebuild as needed
var savedBranchesChoices = Branches.choices + "|";
// Find the Division column
Division = getTagFromIdentifierAndTitle("select","","Division");
// Attach the onchange event
Division.attachEvent('onchange', DivisionChanged);

Second, here’s the JavaScript to handle the onchange event.  A few notes that are important here:

  • This was an environment where Internet Explorer 6+ was the supported browser.  We didn’t need to worry about any other browsers working well with the JavaScript (though I don’t think I was doing anything that wouldn’t work in other browsers).
  • There were more than 20 Branches.  When this is the case, SharePoint generates the dropdown control for the column differently than if there are fewer than 20 values.  I won’t got into the details of this; suffice it to say that some of the peculiarities below are there to manage this difference.
function DivisionChanged() {
    var chosenDivision = Division.options[Division.selectedIndex].text;

    // Find the hidden table with the Division / Branch / ID information
    var DivisionTable = document.getElementById("DivisionTable");
    var DivisionsTableRows = DivisionsTable.getElementsByTagName("TR");
    BranchesChoices = savedBranchesChoices;
    // See which choices ought to remain for this Division
    var newChoicesList = '';
    while(BranchesChoices.length > 0) {
        // Grab the first choice in the list
        var thisBranch = BranchesChoices.substring(0, BranchesChoices.indexOf("|"));
        // Trim the first choice out of the choices
        BranchesChoices = BranchesChoices.substring(thisBranch.length + 1);
        // Grab this choice's index
        var thisBranchIndex = BranchesChoices.substring(0, BranchesChoices.indexOf("|"));
        // Trim the first index out of the choices
        BranchesChoices = BranchesChoices.substring(thisBranchIndex.length + 1);
        // See if this Branch is in the table for this Division
        for (var j=0; j < DivisionsTableRows.length; j++) {
            var DivisionsTableRowsDetails = DivisionsTableRows[j].getElementsByTagName("TD");
            if(DivisionsTableRowsDetails[0].innerHTML == chosenDivision &&
                    DivisionsTableRowsDetails[2].innerHTML == thisBranchIndex)
                newChoicesList = newChoicesList + thisBranch + "|" + thisBranchIndex + "|";
        }
    }
    // The choices now become the filtered list, allowing for no selection: (None)
    if(newChoicesList.length > 0)
        Branches.choices = "(None)|0|" + newChoicesList.substring(0, newChoicesList.length - 1);
    else
        Branches.choices = "(None)|0";        
    Branches.value = "";
}

Similar Posts

47 Comments

  1. Marc,

    It seems that everytime I try to modify the XSL and add the Table ID for my DVWP I keep corrupting the DVWP. What is the bare minimum I need to insert for the code to function?

    Is it just hte Table ID and sort statement?

  2. I think I’m having the same issue as Chris above. I was able to get the other list working fine but when I implement the above code I get ‘Branches.choices’ is null or not an object.

  3. My problem, I think, is partly from the fact that I have 2 drop-down Lookup fields and BOTH are more than 20 items.

    Marc,

    If my first Lookup is >20 items, do I need to code it differently if I want to sync the second Drop-Down Lookup field to it? Please advise.

    Thanks,

    -MO

  4. Michael:

    Yes, you’ll need to code things differently. Since both columns have 20+ options, you’ll need to use the hybrid control method for both.

    I’m working on a jQuery library for SharePoint Web Services which may make this whole exercise easier (or at least different) as it will allow you to look up the allowable values from the lists in real time. Any interest?

    M.

  5. Marc,

    I’ve been reading up on JQuery, from other posts, as I have been researching how to tackle this problem–but I have a concern that I might still hit a wall if I need to install anything “server-side” to make it work.

    My SharePoint support group will not allow me to install anything in their farm, hence why I was attracted to your “non-intrusive” method, “client-side” solution. Please advise if the JQuery approach can work without server-side installation of any “controls” or “libraries.”

    Heck, they won’t even allow “database connectivity” of any kind–which would have been the prefered method I would have leveraged to handle these customizations.

    -MO

    1. jQuery doesn’t require anything server-side. It’s basically a nice abstraction on top of JavaScript that lets you do things in just a few lines of code rather than dozens. It also lets you do AJAX calls, which would give you some of the the “database connectivity” you’re looking for.

      What I’m working on is a jQuery library that will let you do things like get list items, update list items, etc., all from jQuery!

      M.

    2. Marc,

      As much as I like to say I’m not a web designer, I broke down and started reading a book on Javascript, DOM and AJAX. Yes, if it’s a workaround for SQL type connectivity, I’m definitely interested! Problem is this group has no “sheckles” so I need to build it myself, if I’m to implement anything.

      Had to rewrite my Workflow in Designer due to a Service Pack 2 installation just recently, but I’m back on this problem again now.

  6. @Chris,

    Not sure if you’re still having your issue but I figured I would add that I resolved my issue by moving the script to the bottom of the content area that contained my form and DVWP. I guess the issue was that it was trying to run before the lookup field had populated with values.

    1. Mikey:

      You should run your script with the _spBodyOnLoadFunctionNames.push(); function, which ensures that the page is loaded first.

      M.

  7. Thanks for the great article.

    I tried something similar to the above on a cascading dropdown and it works fine. But when i try to save the data , the data does not get saved. I added the _designbind
    __designer:bind=”{ddwrt:DataBind(‘i’,’ff3′,’SelectedValue’,’TextChanged’,’ID’,ddwrt:EscapeDelims(string(@ID)),
    ‘@Work_x0020_Stream’)}”.

    Can you please let me know what the issue could be??

    Thanks a lot.

    Kevin

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.