How to get 15000 items from SP list and bind to Jquery table

pradeep m

How to get more than 5000 items from SharePoint List and bind to the Jquery database.

Please find the below code what i written and also i added all JS files(jquery.1.12.min.js,Jquery.DataTable.js, and Jquery.Datatable.css)

I don't understand where i did the mistake i always getting error like DataTable is not a funtion.

Can Anyone please help me on this ASAP.

Thanks in advance....

</span></li><li class="alt"><span>Threshold more than 5000 items </span></li><li class=""><span>
"CustomerGrid" class="display" cellspacing="0" width="100%">
ID
Title
Requestor
Company

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

pradeep m7y ago
Hello Purna,

am not able to bind the data to the table.

Hello Pathan,

Static it is working fine and below 5000 items also it is working fine but more than 5000 items it now working.
pradeep m7y ago
Hello Purna,

Your mobile number is wrong

Can you please call to this number(08030916400) or give your number
Purna Muvva7y ago
can I know getting data is a issue or binding issue can we have call to fix this 9666130955
Faisal Pathan7y ago
visit https://jsfiddle.net/faisal5170/ev80ws3b/2/
pradeep m7y ago
Hi Pathan,

https://jsfiddle.net/p15ugs8y/

please go through this link i added my code .
pradeep m7y ago
Hello Purna Muvva

Even i try like this but same issue when am using Javascript and Rest api
"CustomerGrid" class="display" cellspacing="0" width="100%">


ID
Title
Requestor
Company
"javascript" type="text/javascript">
var TotalItemCount=0;
var FinalDtata =[];
//var objres={ID:'1',Title:'AA',Requestor:'BB',Company:'CCC'};
$(document).ready(function () { //UPDATED CODE START
//Remove datatable warnings on loading
$.fn.dataTable.ext.errMode = 'none';
var siteurl = _spPageContextInfo.webAbsoluteUrl;
var ItemCount= GetItemCount(siteurl, 'Claims');
createRestUrl(siteurl,ItemCount,'Claims');
resDataTable(objres)
}); //UPDATED CODE END
//Step 1. get total item count of threshold exceed list.
function GetItemCount(siteurl, ListName){
var ItemCount='';
$.ajax({
url: siteurl+"/_api/web/lists/GetByTitle('"+ListName+"')/ItemCount",
method: "GET",
async: false,
headers: { "Accept": "application/json; odata=verbose" },
success: function (data) {
ItemCount = data.d.ItemCount;
},
error: function (data) {
console.log(data);
}
});
return ItemCount;
}
//Step 2. create rest service url if item count is above 5000 then we split calling with 1000 item per call top=1000 for better performance. Otherwise we call as usual 5000 items top=5000
function createRestUrl(siteurl, ItemCount, ListName) {
if(ItemCount<=5000) {
//Item count less than 5000 so we limit it as usual 5000
var listServiceUrl = siteurl+ "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=ID,Company,Requestor,Title&$top=5000";
} else {
//Item count more than 5000 so we split it in 1000 item per call
var listServiceUrl = siteurl+ "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=ID,Company,Requestor,Title&$top=1000";
}
//Step 3: Rest call to procerss each items of list
$.when(processList(listServiceUrl,ItemCount)).done(function () { });
}
//Step 3: Rest call to procerss each items of list
function processList(nextUrl,ItemCount) {
var getdatares;
var dfd = new $.Deferred();
if (nextUrl == undefined) {
dfd.resolve();
return;
}
//Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.
getJSONDataFromUrl(nextUrl).done(function (listItems) {
var FinalDtata1=[];
TotalItemCount = TotalItemCount+listItems.d.results.length;
var items = listItems.d.results;
var next = listItems.d.__next;
$.when(processList(next,ItemCount)).done(function (){
dfd.resolve();
});
var resultObj=[];
var tableHTML='';
//var table = $('#CustomerGrid').DataTable();
//Create datatable object
$.each(items, function(index, obj){
//tableHTML +=''+obj.ID+''+obj.Company+''+obj.Requestor+''+obj.Title+'';
resultObj[index] = {
ID:obj.ID==undefined?'':obj.ID,
Title:obj.Title==undefined?'':obj.Title,
Company:obj.Company==undefined?'':obj.Company,
Requestor:obj.Requestor==undefined?'':obj.Requestor,
};
FinalDtata1.push(resultObj);
});
console.log(FinalDtata1);
resDataTable(FinalDtata1);
});
//console.log(FinalDtata1);
// var myName='Welcome';
// resDataTable(myNmame);
//return dfd.promise();
// resDataTable(FinalDtata1);
}
function resDataTable(FinalDtata1)
{
console.log('invoked...');
try{
console.log("Invoke");
//table.rows.add($(tableHTML)).draw(); //Append each list row to data tabel
$('#CustomerGrid').DataTable({
"aaData": FinalDtata1,
"scrollY":"600px",
"scrollCollapse": true,
"paging": true,
"destroy": true,
"aoColumns":
[
{
"ID":"ID"
},
{
"mData": "Title",
"render": function(mData,type, full) {
return '+mData+'">' + mData + '';
}
},
{
"mData": "Requestor"
},
{
"mData": "Company"
}
]
});
}
catch (e) {
alert(e.message);
}
}
//Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.
function getJSONDataFromUrl(endpoint) {
return jQuery.ajax({
url: endpoint,
method: "GET",
headers: {
"Accept": "application/json; odata=verbose",
"Content-Type": "application/json; odata=verbose"
}
});
}