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

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. 

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.