Get Data in JQuery DataTable from SharePoint List using $skip, $top, $inlinecount, $orderby parameters

Sep 21, 2015 at 2:25 AM


In this article we will show how you can load the data from SharePoint List using REST API into JQuery DataTable using the Ajax Pagination.

In my previous article I showed you how to retrieve the data from the SharePoint list using REST API but it retrieves all the data from the list at once which is good for the list having less number of items/records but what if the list has large number of items as SharePoint client object model supports retrieval of 5000 items at once. 


Articles on the Jquery DataTable and SharePoint REST API 

In this case the SharePoint REST API provides the $TOP, $ORDERBY, $SKIP AND $INLINECOUNT parameters to retrieve only the records required to show on one page.

 

Parameter
Example
Description
$skip
$skip=n
Returns entries skipping the first n entries, according to the $orderby parameter
$top
$top=n
Returns only the top n entries, according to the $orderby and $skip parameters
$inlinecount
$inlinecount=allpages
It will add the  __count property to the results which indicates the total number of entries which match the $filter expression 
$orderby
$orderby = CustomerName
Returns the records ordered by the CustomerName field

 

Example: http://server/siteurl/_vti_bin/listdata.svc/Customers?$ $select=Id,CustomerName,Address,HomePhone,MobileNumber,Email,Organization,RolesValue &$inlinecount=allpages&skip=2&$top=2

 

We will use these parameters to retrieve the data required and bind it to our JSON

 

We will use the same customers list we used in my previous article series

Load the Data in JQuery DataTable from SharePoint List using REST API

Custom DropDown Column Filter in JQuery Datatable

Free Text Search on Column in JQuery Datatable

I have created the CustomerJqueryDataTableAjax.js and CustomerJqueryDatatableAjax.txt for this article, which you can download it at the end of this post.

 

The rest query to get the top 10 items from the customers list would look like this,

 

../_vti_bin/listdata.svc/Customers?$select=Id,CustomerName,Address,HomePhone,MobileNumber,Email,Organization,RolesValue&$inlinecount=allpages&$top=10

 

Now in order to support pagination in DataTable the json results should have values of “sEcho”, “iTotalRecords”, “iTotalDisplayRecords in the json results which is not provided by the SharePoint by default so we will manipulate it in the fnServerData function of the JQuery DataTable.

 

We will place the below script in the fnServerData function of the JQuery DataTable

 $.ajax({  
            "dataType": "json",  
            "type": "GET",  
            "url": restQuery,  
            "data": aoData,  
            "success": function (json) {  
              json["sEcho"] = oSettings.iDraw.toString();  
              json["iTotalRecords"] = json.d.__count;  
              json["iTotalDisplayRecords"] = json.d.__count;  
              fnCallback(json);  
            }  
          });  

Found this article by Dhaval Shah valuable? Help by Sharing ...

  • Click on the banners at the top of article or in the right panel to visit my blog's sponsors. They are all hand-picked and are selected based on providing great products and services to the SharePoint community.
  • I’d be very grateful if you’d help it spread by Sharing. Below, you should find links to sharing this article on your favorite social media sites.
Related Posts by Dhaval Shah blog comments powered by Disqus