Jun 28, 2017

Cascading drop down in SharePoint using REST API

Cascading drop down or filtered Lookup Columns in SharePoint is one of the most used functionality in most of the projects for various business needs.
In one of my old articles, I have explained about creating cascading or filtered lookup columns using JQuery & SPServices in MOSS 2007 version of SharePoint. 
This solution has limitations such as this will work only in List Forms and it may not work after certain limit of values in the drop down.
Previous articles reference 
CAML Query tutorial for SharePoint
Multi value Lookup Columns in SharePoint
In this article, we will learn to implement Cascading or Filtered Lookup Columns in latest versions of SharePoint using REST API (applicable to SharePoint 2013, Office 365 – SharePoint Online, SharePoint 2016). REST API uses OData (Open Data Protocol) services to read/write/update data in SharePoint.
I have created 2 lists with the following information as shown below:
  1. Drinks 
  2. Drinks Type (Drink column is a lookup column to display Title from Drinks List)             
             
Now create a list to test the cascading or filtered lookup functionality. I have created a list with “Drinks Menu” as the name. 
             
Please note, the Drinks and Drink Type columns are created as Choice type and all the values from Drinks and Drink Type are added as choices in these fields. This is implemented in this way so that we can avoid the issue surfaces when we have large number of options in Lookup Column. 
Also, if you add a new value to parent lists (Drink or Drink Type lists in this scenario), add the values as choice in the cascading lookup value implementation list ( Drink Menu list in this scenario).
Now, click on the new item which will open the “NewForm.aspx” -> Edit the page -> Add a Web Part -> Insert -> Categories -> Media and Content -> Script Editor -> Click Add to add it.
In the Script Editor, click Edit Snippet and add the CascadingDropdown.js (download from the below attachment). I have referred “JQuery.1.12.0.min.js” in the code which is also available for download. Make sure JQuery is referenced properly in your code else, the script won’t work.
Following are the scenarios, I have covered in this example:
When “Drinks” drop down is not selected or changed to empty, then “Drink Type” & “Price” fields should be disabled and empty

When “Drinks” is selected, “Drink Type” should cascade (filter the values) and show the types based upon selection
When “Drink Type” is selected, the “Price” should automatically populate in the Price field
The data saved to the list will look as shown below:
JS code for cascading the lookup values (Drinks -> Drink Type) is shown below:
//Function to filter the values of Drink Types
function loadDrinkTypes(selectedDrink) {
    var drinkTypeListName = "Drink Type";
    var drinkTypeListURL = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + drinkTypeListName + "')/items?$select=Title,Drink/Title,Drink/Id&$expand=Drink&$filter=Drink/Title eq '" + selectedDrink + "'";
    getReqData(drinkTypeListURL, function (data) {
        var items = data.d.results;
        if (items.length > 0) {
            var optionsAsString = '<option value=""></option>';
            for (var i = 0; i < items.length; i++) {
                optionsAsString += "<option value='" + items[i].Title + "'>" + items[i].Title + "</option>";
            }
            $('select[title="Drink Type"]').html(optionsAsString);
        }
    },
        function (data) {
            //alert("Some error occurred in getting Drink Types");
        });
}

//JQuery AJAX to access REST API JSON data
function getReqData(reqUrl, success, failure) {
    $.ajax({
        url: reqUrl,
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        success: function (data) {
            success(data);
        },
        error: function (data) {
            failure(data);
        }
    });
}
JS code for setting the Price value automatically on Drink Type selection is shown below:
//Function set the Drink Price 
function setDrinkPrice(drinks, drinkType) {
    var drinkTypeListName = "Drink Type";
    var drinkTypeListURL = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + drinkTypeListName + "')/items?$select=Price&$filter=(Drink/Title eq '" + drinks + "') and (Title eq '" + drinkType + "')";
    getReqData(drinkTypeListURL, function (data) {
        var items = data.d.results;
        if (items.length > 0) {
            var price = "";
            for (var i = 0; i < items.length; i++) {
               $("input[title='Price']").val(items[i].Price);
            }
        }
    },
        function (data) {
            //alert("Some error occurred in getting price");
        });
}
Please download the full code to view how each method are called in document.ready()
Note:
1. The above code is tested in SharePoint Online (all the lists are in Classic Mode and not Modern List views in SP Online)
2. It should work in SharePoint 2013 and 2016 versions as well
Please free to comment. Always, your comments help me to write more.😃 Share this post to others if this helps you!😇
You’re still here? I don’t have anything else for you sorry. It’s straightforward. Go… go make some users happy. 😎😀
Update: 06/07/2017
To know about implementing multi-value lookup column and filtering values, read this article: Multi value Lookup Columns in SharePoint

6 comments:

  1. It was a great post.

    thanks for sharing

    Please update the download link , it was expired.

    ReplyDelete
    Replies
    1. Glad you liked it!
      I have updated the link now. Thanks for pointing it. Keep visiting the site by subscribing to read more new articles. Have a nice day!

      Delete
  2. how to filter null value from second column

    ReplyDelete
  3. does this work for fields in document library

    ReplyDelete
    Replies
    1. I would also like to know if this works for document libraries or whether someone has an alternate solution.

      Delete

Dear Readers,

I LOVE to hear from you! Your feedback is always appreciated. I will try to reply to your query as soon as possible.

1. Make sure to click the "Notify me" check box at the right side to be notified of follow up comments and replies.
2. Please Do Not Spam - Spam comments will be deleted immediately upon review.