Cascading Drop Down Lists in SharePoint / Office 365 using REST

May 20, 2014 Mark Rackley Uncategorized 52

Here’s a quick blog post for you guys. I often demo some of the cool things you can do with Client Side Development in SharePoint that really enhance usability of the default SharePoint forms. One of the features people ask for most is the ability to create Cascading Drop Down Lists (The selection of one drop down list causes a secondary drop down list to be populated with related items).

This handy functionality really improves the user experience by helping ensure users only select valid data.  We’ll be implementing this functionality on a New Item form in SharePoint for a list that has two lookup fields.  These lookup fields are lookups into a “Parent” list (the one responsible for filtering the Secondary drop down”) and a “Child” list (that is filtered by the selection of the “Parent”). 

For my example 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.

You can learn more about SPServices Cascading Dropdown functionality here.

At this point you might be wondering why you should use REST if SPServices will do the same thing? Well, that’s an excellent question. If you are using SPServices today and it’s working fine for you, then there you don’t NEED to change what you are doing. However, keep in mind that SPServices uses SharePoint’s .asmx web services which have been deprecated. This means Microsoft will not be  adding new functionality to the .asmx web services. However, Microsoft WILL be adding REST (and CSOM) functionality so if you are using SharePoint 2013 / Office 365 I suggest you suck it up and learn REST and CSOM so that you can take advantage of the growing Client Side functionality.

Conclusion

You’re still here? I don’t have anything else for you sorry. It’s pretty straightforward. Go.. get… go make some users happy.

Families in Germany who are facing divers soundness problem, such persons can buy drugs from the Web without prescription. With the market flooded with divers web-sites selling sundry medicaments, purchasing medicines from th WEB is no longer a trouble for common man. Certain medications are used to treat infections caused by dental abscesses. Of course it isn’t all. If you’re concerned about erectile health problem, you probably know about Xenical and Hoodia. Probably every adult knows about Garcinia. (Read more PhentermineXenical). The symptoms of sexual health problems in men turn on impossibility to have an erection sufficient for sexual functioning. Certain medications may add to sex drive difficulties, so its substantial to cooperate with your heartiness care professional so that the prescription can be tailored to your needs. Preparatory to grab Levitra or other medicament, speak to your dispenser if you have any allergies. Talk to your health care provider for more details. Preparatory to ordering this remedy, tell your doctor if you are allergic to anything.

49 Comments

  2. Thanks very much for this, Mark!

    I’m battling to get it working on a SharePoint 2010 site, in spite of having duplicated your lists precisely. The error I’m getting is:

    Error retrieving information from list: Citiesundefined

    I wonder if the mistake I’m making is in not editing this line correctly: url: “http://sp2010dev:1234/_vti_bin/listdata.svc/”+params.childList+

    The URL for the site I’m working in is:

    https://companyname.com/bpo/funnel

    Replacing “http://sp2010dev:1234” with the above – with or without the port – doesn’t work. What should I be doing and, how can I find out what port it is likely to be on, if the 1234 is an installation-specific port number?

    Thanks very much! (I’ll be able to use this all over the site, once I’ve figured out how to get it working…)

    [What I’d really like to do is be able to:
    1. Link two multiple item lookups, rather than two single item lookups.
    2. Link one single item Lookup with a multiple item lookup.
    ]

    • Hey Gary, the trick to getting the queries to work is to test them in the URL first. After you can make sure your query is using the correct syntax you can plug it back into your script. seeing an error that says “Citiesundefined” says to me that you are trying to concatenate the string “Cities” with a variable that has not been defined.

    • I know this is late but you need to put the subsite in your URL call:

      “http://sp2010dev:1234/bpo/funnel/_vti_bin/listdata.svc/”+params.childList

      because the list doesn’t exist at the root site.

  4. Hi , I am getting an error saying, the parent colimnID is not exist.

    error retrieving information from list: Categories{
    “error”:{
    “code”:””,”mesaage”:”No property ‘categoryId’ exists in type ‘microsoft.sharepint.linq.dataserviceentity’ at postion 1.”
    }
    }
    }
    Here Categories is the parent list which user first select and it got only title column.
    Any help appriciated.

    • Sounds like you are trying to select a field “categoryId” which does not exist in your list. The easiest thing to do is to execute the REST query in the url of your browser without ANY Select parameter. This will return all the fields for the list and you can see how this field “categoryId” needs to be referenced in your query. Good luck!

  5. Hi,

    I’m having an issue with this: this will work right after I add the web part and am still in edit mode. After I click “stop editing” and then try to add a new item, the script stops working. Any ideas?

    Thank you!

    Jane

  6. Another question. Is there any way to extend this so that it will work for more than 2 fields? keeping with your example, what if I wanted to add a “neighborhood” field after city that only loaded neighborhoods relevant to the chosen city?

    Jane

  8. Hi Mark.

    Thank you very much for the post. Works very fine.
    I wonder if it is possible to get this functionality in the “third list” without going to “Add New” or “Edit Content” but directly in QuickEdit-Mode?

    Ingo

  10. Hi,
    I deployed this according your videoguide but notthing happens.
    No error but selection… I want to use this for a content type as meta data and in the document it self. Dont know of this is possible. How can i debug this on SharePoint online?

  11. thanks a lot for helpfull guide! i have a question about where is it necessary to place js.file and how to create it?
    is it possible to create cascade.js in Sharepoint designer?

  12. My question is the same as Jane. I have this working great for 2 fields. But, I need to go with three fields. My example is very similar to you. I have State and City working. How do I add a 3rd lookup for neighborhood?
    Thanks
    Andy

  13. Very cool.
    Got it working with the spaces in field names by substituting _x0020_ for the spaces. i.e. for childLookupField and parentFieldInChildList.
    I added in some sorting also.
    ……/items?$orderby=”+params.childLookupField+” asc&$select=Id,…..

  14. Great Post!
    This is a game changer for my SP development. I’m with Jane and Andy, I really need to learn how to add more children and/or parent fields. Any chance we will see an update for that in this post? Big thx! Jim

  15. I got your example to work but when I customized the for with InfoPath it doesn’t work now? what do I need to change? Thank you … Tim

  16. Thanks very much for putting this together – and for the Forms7 software. Excited to get started with it.
    Aside from updating the field definitions in the beginning of the script, is there anything else that needs to be changed:
    Do I need to update “Title” in the below string to reflect my field definitions, or is this a generic call:
    ild = $(“select[Title='”+params.childFormField+”‘], select

    Do I need to update anything with the URL?
    Thanks,
    Keith

    • Figured it out, thanks very much!!! Just out of curiosity, is SPServices used more in 2010, and is it still available for 2013?

      • SPServices works in 2007,2010, and 2013 however it uses the asmx web services which have been deprecated, so you will not see any new functionality added there. I’d recommend investing some time learning the REST services if you have the time.

      • So when I try to add the cascade to the document library I get ‘error retrieving information from list’ but it works fine on a list.

        • Any suggestions on getting this to work on a document library? I replicated the steps from the list using the same site asset file but each time I update the webpart I get an error ‘Error retrieving information from list: Groups{“error”:{code”:” -2146232838, MicrosoftSharePoint.SPException”,”message”;{“lang”;”en-US”,”value”:”Column ‘undefined’ does not exist. It may have been deleted by another user.”}}}

          • It does work on a document library. It sounds like your REST query is malformed. Get it to work in the browser first to figure out exactly why it’s failing. Although it sounds like the value for a field it is querying or filtering on is empty.

          • Hi Tracey… I was getting the same message in my document library until I made my two lookup columns as… Settings > Edit Column > Require that this column contains information: Yes…. and now it works…

  20. Great Example!

    Does it work with a list which is on a different sub site but within the same site collection? To illustrate: the Cities list is on sub site 1 and the Cascade list is on sub site 2. Will it be possible to make the script work?

  21. I have the cascading working, but the results are limited to 100 values and not sorted. Any thoughts how I can ensure all of the values are returned and sorted? (I’m parent is company, and child is employee name)

  22. Thanks so much, especially for the clarity in describing the initial parameters. It’s logical hat they are similar to the array used in SPServices, but I found I could not understand definitively which fields were meant there. I also like the idea of using the REST interface from a going-forward perspective. I have tried about five other approaches to this, and this is the one that worked as clearly described, out of the box.

  23. Love this functionality, and the fact that you are willing to share your expertise is great. I followed it exactly ( allthough strange tat the link to jquery does’t contain “https”) and it works. Thanx a lot.

  24. Literally saved my bacon last night (pun intended)…. Gonna get more into this one I think and see how it works with Stratus. Cheers Mark for another great solution!

  25. Hi Mark – I followed your instructions and it works like a dream…. I added this to my edit form as well but we are noticing that when we edit a list item it defaults the child field to the first one in the cascaded list and does not keep what was originally added (and not to be modified) – do I need to change some parameters or add in another cascade.js relevant to the edit form? Any help would be gratefully received – Thanks

    • all good – worked it out – I created an edit form version and changed some parameters to be “selected” so now when the user is editing the metadata the choices they made are still selected as opposed to defaulting to the item at the top of the child list…..

  26. Hi Mark – this is working great for me, but I’m unsure how to make it work when after I select and upload a document to the document library, the default add new document metadata I’ve chosen for the user to display, won’t allow me to add in my content editor to enable the cascade.js script…. the document library settings don’t have a Edit Form Parts > New Form…. only Edit and Default…. I can modify a List new form, but not a Document new form…. anywhere you can point me to so I can understand how I can make this awesome cascading lookup work?

  27. Mark, thank you. I am in need of a 4-tier cascade — how can the code be scaled up to capture two additional layers?

2 Trackbacks / Pingbacks

  1. How to update a New Item form with photo from lookup column | JJR's Bag of Holding
  2. New & Improved Cascading Dropdowns for SharePoint Classic Forms

Comments are closed.