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&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.