Cascading Dropdown for subsite

Cascading Dropdown for Subsite

  I have a “Parent” list called “States” and a “Child” list called “Cities”. The Cities list has a lookup field “State” which is a lookup to the States.  Finally, I  have a third list with two lookup fields. One field called “State” that is a lookup to the Title field in our States list, and a second field called “City” which is a lookup to the Title field in our Cities list.  Our script will function so that when a user clicks on a State, only the Cities that correspond to that State are populated in the Cities Drop Down list.  Make sense??  Let’s get started.

We’ll take the following simple steps to implement this functionality:

1. Upload the script below to our Site Assets Document Library
2. Create the “Parent” list used as a Lookup field on our form for the cascading drop down list
3. Create the “Child” list used as the Secondary Lookup field on our form that has a lookup to the “Parent” list.
4. Edit the script to point to the correct lists and fields for the Drop Down list
5. Edit the default New Form for a list
6. Add a Content Editor Web Part to the Form
7. Link the Content Editor Web Part to the script we uploaded in Step 1

That’s all there is to it.

The Script for SharePoint 2013 / Office 365

So, here is the script for SharePoint 2013 and Office 365.  The following parameters need to be updated in order for this to work for your specific needs:

• parentFormField – This is the Display Name of the field on your form for the field that is considered the “Parent” Drop Down List field (the field that the user selects first)
• childList – This name of the list where the “Child” entries come from
• childLookupField – This is the INTERNAL FIELD NAME for the field that will be used to populate the “Child” Drop Down List. Use the Internal Field Name from the Child list, NOT the Display name on your form.
• childFormField – This is the Display Name of the field on your form for the “Child” field (the field that is populated based upon the selection of the other Drop Down List field”
• parentListInChildList – This is the INTERNAL FIELD NAME of the “Parent” lookup field as it is in the “Child” list.

<script src="//code.jquery.com/jquery-1.10.1.min.js"></script>

<script type="text/javascript">
    $(document).ready(function() {
    
        HillbillyCascade({
            parentFormField: "State", //Display name on form of field from parent list
            childList: "Cities", //List name of child list
            childLookupField: "Title", //Internal field name in Child List used in lookup
            childFormField: "City", //Display name on form of the child field
            parentFieldInChildList: "State" //Internal field name in Child List of the parent field
        });
    
    });
    
    function HillbillyCascade(params)
    {

        var parent = $("select[Title='"+params.parentFormField+"'], select[Title='"+
            params.parentFormField+" Required Field']");
        
        $(parent).change(function(){
            DoHillbillyCascade(this.value,params);        
        });
        
        var currentParent = $(parent).val();
        if (currentParent != 0)        
        {
            DoHillbillyCascade(currentParent,params);
        }
        
    }
        

    function DoHillbillyCascade(parentID,params)
    {
    
        var child = $("select[Title='"+params.childFormField+"'], select[Title='"+
            params.childFormField+" Required Field']," +
           "select[Title='"+params.childFormField+" possible values']");
        
        $(child).empty();
    
        var options = "";

        var call = $.ajax({
            url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('"+params.childList+
                "')/items?$select=Id,"+params.childLookupField+","+params.parentFieldInChildList+
                "/Id&$expand="+params.parentFieldInChildList+"/Id&$filter="+params.parentFieldInChildList+
                "/Id eq "+ parentID,
            type: "GET",
            dataType: "json",
            headers: {
                Accept: "application/json;odata=verbose"
            }
       
        });
        call.done(function (data,textStatus, jqXHR){
        
            for (index in data.d.results)
            {
                options += "<option value='"+ data.d.results[index].Id +"'>"+
                    data.d.results[index][params.childLookupField]+"</option>";
            }
            $(child).append(options);

        });
        call.fail(function (jqXHR,textStatus,errorThrown){
            alert("Error retrieving information from list: " + params.childList + jqXHR.responseText);
            $(child).append(options);
        });
        
    }
    
</script>

And, as usual, here’s the video showing the script being implemented in Office 365

Wait… what about SharePoint 2010?

The same basic script will also work in 2010 with one minor change.  The REST query for SharePoint 2010 is different. So you will need to change your ajax call in the script above from:

var call = $.ajax({
            url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('"+params.childList+
                "')/items?$select=Id,"+params.childLookupField+","+params.parentFieldInChildList+
                "/Id&$expand="+params.parentFieldInChildList+"/Id&$filter="+params.parentFieldInChildList+
                "/Id eq "+ parentID,
            type: "GET",
            dataType: "json",
            headers: {
                Accept: "application/json;odata=verbose"
            }
       
        });

    to:

     var call = $.ajax({
            url: "http://sp2010dev:1234/_vti_bin/listdata.svc/"+params.childList+
                "?$select=Id,"+params.childLookupField+","+params.parentFieldInChildList+
                "Id&$filter=("+params.parentFieldInChildList+
                "Id eq "+ parentID + ")",
            type: "GET",
            dataType: "json",
            headers: {
                Accept: "application/json;odata=verbose"
            }
       
        });

Okay… well… what about SharePoint 2007?

Well, if you are one of the poor souls still on  SharePoint 2007, then you should probably just upgrade (at least that’s what Microsoft will tell you). Actually, you do have an option, you can use a tool called SPServices which is a jQuery library which has some built in cascading functionality that will work in SharePoint 2007, 2010, and 2013.