Implementing cascading drop down lists in SharePoint

Continuing my last post I will show some JavaScript that can be used to implement cascading drop down lists in SharePoint. Assume you have Products list and Categories list. Products have a lookup column to the Categories list. And we have Order line list which have lookup columns to Products list and to Categories list. When creating new Order line item we want that when Category is select then theProduct combo box must be filtered to show only selected category products:

093009_1154_Implementin1 Again jQuery, SPAPI and CEWP comes to help. The only problem is that SharePoint renders combo box as INPUT or SELECT control depending on how much items it have. The following script has workaround for it (see comments inside):

//Set up jQuery and SPAPI (in my case I have installed jQuery and SPAPI in Layouts directory)
<script src="/_layouts/jquery-1.3.2.js" type="text/javascript"></script>
<script src="/_layouts/SPAPI/SPAPI_Core.js" type="text/javascript"></script>
<script src="/_layouts/SPAPI/SPAPI_Lists.js" type="text/javascript"></script>
<script type="text/javascript"> 

//You may need do modify this function depending where your site collection is located
function GetRootUrl() {
    var pathparts = document.location.pathname.split('/');
    var url = 'http://' + document.location.host + '/' + pathparts[1] + '/';
    return url;
}

lastSelectedCategory = 0;

//filter product by selected catagory
function FilterProducts() {

    //get Product control (it can be INPUT or SELECT)
    var productElement = $("select[title='Product']");
    if (productElement.length == 0) { productElement = $("input[title='Product']"); }
    if (productElement.length == 0) return;

    //get Product Category control (it can be INPUT or SELECT)
    var categoryElement = $("select[title='Product Category']");
    if (categoryElement.length == 0) { categoryElement = $("input[title='Product Category']"); }
    if (categoryElement.length == 0) return;

    //get selected Product Category value
    var category = '';
    if (categoryElement[0].optHid) { category = document.getElementById(categoryElement[0].optHid).value; }
    else { category = categoryElement[0].value; }

    if (lastSelectedCategory != category) {

        var foundedSelectedProductID = '';
        var foundedSelectedProduct = '';

        //get sletected Category Products
        var lists = new SPAPI_Lists(GetRootUrl());
        var items = lists.getListItems(
		'Products',   // listName
		'',         // viewName
		'<Query><Where><Eq><FieldRef Name="Product_x0020_Category" LookupId="TRUE"/><Value Type="Lookup">' + category + '</Value></Eq></Where></Query>',  // query
		'<ViewFields><FieldRef Name="ID"/><FieldRef Name="Title"/></ViewFields>',  // viewFields
		'',  // rowLimit
		'<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>'  // queryOptions
	);

        if (items.status == 200) {
            var rows = items.responseXML.getElementsByTagName('z:row');
            var options = '';
            //format Products values for INPUT control
            if (productElement[0].optHid) {
                var productSelected = document.getElementById(productElement[0].optHid).value;
                for (var i = 0; i < rows.length; i++) {
                    var productID = rows[i].getAttribute('ows_ID');
                    if (productID == productSelected) { //trying to select the same product in new category if it exists
                        foundedSelectedProductID = productID;
                        foundedSelectedProduct = rows[i].getAttribute('ows_Title');
                    }
                    options += rows[i].getAttribute('ows_Title') + '|' + productID + '|';
                }

                productElement[0].choices = options.substring(0, options.length - 1);

                productElement[0].value = foundedSelectedProduct;
                productElement[0].match = foundedSelectedProduct;
                document.getElementById(productElement[0].optHid).value = foundedSelectedProductID;

            } else {
            //format Products for SELECT control
                var productSelected = productElement[0].value;
                for (var i = 0; i < rows.length; i++) {
                    var productID = rows[i].getAttribute('ows_ID');
                    if (productID != productSelected) {//trying to select the same product in new category if it exists
                        options += '<option value="' + productID + '">' + rows[i].getAttribute('ows_Title'); +'</option>';
                    } else {
                        options += '<option selected="selected" value="' + productID + '">' + rows[i].getAttribute('ows_Title'); +'</option>';
                    }
                }
                productElement.html(options);
            }
        }
        lastSelectedCategory = category;
    }
}

$(document).ready(
     function() {

         //FilterProducts(); //uncomment if you want Products be filttered on startup;

         // Filter products on Category change (Category also can be INPUT or SELECT)
         var categoryElement = $("select[title='Product Category']");
         if (categoryElement.length == 0) { categoryElement = $("input[title='Product Category']"); }
         if (categoryElement.length != 0) {
             if (categoryElement[0].optHid) {
                 $("input[id='" + categoryElement[0].optHid + "']").bind("propertychange", function() { FilterProducts(); });
             } else {
                 $("select[title='Product Category']").change(function() { FilterProducts(); });
             }
         }
         // End Change Product Category
     }
    );
Posted in Customization, javascript
4 comments on “Implementing cascading drop down lists in SharePoint
  1. Sorry to be following you around on the MSDN SharePoint - Design and Customization forum says:

    Mindaugus:

    You’ve got a great series of posts going here. Sorry to be following you around on the MSDN SharePoint – Design and Customization forum. I think that our jQuery Library for SharePoint Web Services (http://spservices.codeplex.com) may take things a few steps further. I started out with just JavaScript and a hidden DVWP with the column relationships (http://mdasblog.wordpress.com/2009/07/14/cascading-columns-in-a-sharepoint-form/) and went from there.

    I’d be interested in any of your insights based on what we have so far. We’d like to add more “value-added” functions like $().SPServices.SPCascadeDropdowns based on the SharePoint Web Services.

    Keep up the great posts!

    Thanks,
    M.

  2. Hi Mark, says:

    I’m keeping an eye on you 🙂

    I’ll definitely try SPServices library in near future.

    Mindaugas

  3. mettresspa says:

    “Assume you have Products list and Categories list. Products have a lookup column to the Categories list. And we have Order line list which have lookup columns to Products list and to Categories list.”
    Something I doubt …

  4. Tanya says:

    Hi, Great post!!
    But I have 2 problems:
    1. I cannot implement in this way 2 cascading drop down when the second one is based on the first one, i.e. I have Level 1, Level 2 and Level 3. So I did cascading for Level 2 based on Level 1 and it works. I do exactly the same for level 3 to e chosen based on Level 2 – and Level 2 object has no value in this case….

    2. After I customize the form with Jquery theme for tabs – the values of the Level 2 are moved to the bottom of the form…

    Will really appriciate your help!!!! 🙁