Using Select2 and SharePoint 2013 REST API with large lists and infinite scroll

Recently during a project I was involved with creating a custom application with JavaScript inside SharePoint. One of the requirements was to load data from a large list (> 5000 items) into a Select2 dropdown control. Select2 is a very handy dropdown control to use in custom applications. It features out of the box search functionality, infinite scroll and many more nice features. However I struggled a few days with some specific problems in combination with SharePoint. In this article I’ll describe how to use Select2 in combination with the SharePoint REST API and large lists.

Select2 uses out of the box a HTML select or input control to bind itself to. Because we need to specify a custom ajax function, Select2 will only work in combination with an input control which is hidden. Add to your page an input type hidden control, like this:

<input id="selectData" type="hidden" />

We need to fulfill a few requirements which are important:
– Able to select from a very large list
– Support searching on title
– Fast
– Reliable

To meet these requirements we need to overcome a few challenges. The first challenge is large list support. To overcome this issue, you need at least indexed columns in case you want to query on specific columns. Be aware that you need to set the indexes before filling the list with data! Second challenge is how to use Select2 with the SharePoint REST API. Documentation on the internet is very rare on this specific subject and also there are a few problems with OData and SharePoint 2013 which you’ll face. In the following sample I will show how to bind Select2 and make it possible to search and use infinite scroll. It works also very fast and reliable. The sample uses a list named Books and uses the Title field to filter on.

var webAbsoluteUrl = _spPageContextInfo.webAbsoluteUrl;

$('#selectData').select2({
    placeholder: 'Select a book title',
    id: function (data) { return data.Title; },
    ajax: {
        url: webAbsoluteUrl + "/_api/web/lists/GetByTitle('Books')/items",
        dataType: "json",
        data: function (term, page, context) {
            var query = "";
            if (term) {
                query = "substringof('" + term + "', Title)";
            }
            if (!context) {
                context = "";
            }
            return {
                "$filter": query,
                 "$top": 100,
                 "$skiptoken": "Paged=TRUE&amp;p_ID=" + context
            };
        },
        results: function (data, page) {
            var context = "";
            var more = false;
            if (data.d.__next) {
                context = getURLParam("p_ID", data.d.__next);
                more = true;
            }
            return { results: data.d.results, more: more, context: context };
        },
        params: {
            contentType: "application/json;odata=verbose",
            headers: {
                "accept": "application/json;odata=verbose"
            }
        }
    },
    formatResult: function (item) {
        return item.Title;
    },
    formatSelection: function (item) {
        return item.Title;
    },
    width: '100%'
});

// Function to get parameter from url
function getURLParam(name, url) {
    // get query string part of url into its own variable
    url = decodeURIComponent(url);
    var query_string = url.split("?");

    // make array of all name/value pairs in query string
    var params = query_string[1].split("&");

    // loop through the parameters
    var i = 0;
    while (i < params.length) {
        // compare param name against arg passed in
        var param_item = params[i].split("=");
        if (param_item[0] == name) {
            // if they match, return the value
            return param_item[1];
        }
        i++;
    }
    return "";
}

First thing we do is getting the current web URL. This is needed to construct the path to the REST API. Then we bind the Select2 control using JQuery. We define first a placeholder. This is the text that will be shown when nothing is selected. We also specify a function to determine the id. Select2 standard can’t handle the data format which comes back from SharePoint, so we need to set manually that the identifier is the Title field of the book. The same applies for the formatResult and formatSelection which makes sure that the title is shown as field in the dropdow list. The most important part is the ajax parameter. First a URL to the REST API is specified, then we specify dataType JSON, because we want all the data as JSON and not XML. We also need to set this explicitly in the contentType and headers of the HTTP calls we need to make. In the data parameter we specify the logic to handle the queries and make sure that the skiptoken is specified. Because of a bug/designed feature in the SharePoint REST API, the skip parameter doesn’t work. The linked article describes an alternative by using the skiptoken. We store this skiptoken partially in the context variable to make sure Select2 tracks it for each next call. The next important part is the results function. This function parses the result and grabs the p_ID value to store in the context variable. In case the __next value is set, we know that more data is available, so then we also set the more variable to true. Select2 uses the more variable to indicate if a next page with more items is available for infinite scroll.

5 thoughts on “Using Select2 and SharePoint 2013 REST API with large lists and infinite scroll

  1. mario

    Dear Jeroen,

    I followed your article and everything runs smothly (good article by the way).
    However the data is failing to load when I need to filter it using the filter keyword at WS URL. For instance “url: webAbsoluteUrl + “/_api/web/lists/GetByTitle(‘Books’)/items?$filter=Metadonnee eq ‘Type_plan’
    However if I use the orderby instruction, no issues at all. Only when using filter.

    Do you know why or any workaround for this?
    I’m using Select2 v3.5.4

    Kind regards,
    Mario

    Reply
    1. captain007 Post author

      Select2 modifies the filter attribute already itself on line 18, which might give two filters applied at the end which will fail. What you can do is check the HTTP GET request which is performed using browser debugging tooling. You can only apply additional filters by changing the code on line 18 in the sample to add additional filter conditions there. I think you should work with filters like described here (and make sure that the filter you can fill in as end user is also injected into the querystring): http://sharepoint.stackexchange.com/questions/163307/rest-api-filter-multiple-values

      Reply
  2. Mario

    Dear Jeroen,

    indeed I missed the Select2 filter attribute.
    However I could find that the filter attribute only applies over Select2 control and not over the URL query. Let me give you an exemple:
    a) line 7 URL: _api/web/lists/GetByTitle(‘Localites’)/items (without any filter it retrieves 100 records)
    b) line 12: query = “Title eq ‘Amay'”; (from above 100 records, it should retrieve only 1 record)
    So, from above I was expecting Select2 to be populated with just 1 record.
    But that is not the case, what happens is that Select2 is populated with 100 records. Then if the user type say ‘Ama’ it founds the record. If the user type something else say ‘EDD’ it says record not find. However the user still can select any of 100 records from the Select2 dropdownbox.
    The point is that the filter only applies to what the user types at Select2 over the record set initially brought by the WS URL. So _api/web/lists/GetByTitle(‘Localites’)/items?$filter=Title eq ‘Amay’ (which return only 1 record) is not the same as line7 + Line12 (which gets all SP list data into Select2 and the filter applies when and if the user type any norrowing character).

    Can you recognise this behaviour? I’d like to norrow what I’m getting from the WS to populate Select2 with a subset of data.

    Kind regards,
    Mario

    Reply
    1. Mario

      Answer: I need to remove the if at line 14, so the filter does not wait for a user stroke to apply.

      Thank you Jeroen.

      KR,
      Mario

      Reply

Leave a Reply

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