How to get data from SharePoint List when threshold value is crossed ?

Scenario

• Suppose you have List Employees which has more than 100000 rows(1 lakh)
• Threshold limit set by administrator is 5000(default).
• Employees are categorized in 3 department so we have one column Department which hold employee’s department. Each department has around 33000 employees.
• Department column is indexed.

Requirement

• Create a page which displays Employees in Jquery datatable(custom view) by calling GetListItems using spservices.js
• Page will have a dropdown  for department which user can select and data will be filtered accordingly.
• All the data needs to be displayed at once.

Issue

• As each department has more than 33000 items, CAML query will fail with Threshold limit error.

Alternate solution approach.

• Get the data in batch by querying multiple times. Though this is not effective but still it might be required when you need to display all data at once on filter.

Logic

• Get data in batches by using SharePoint CAML query paging technique and specifying row limit.
• We will get data in batch of 4999 rows at a time and merge data each time till all the rows are returned.
• Same Caml query will be used with CAMLQueryOptions and sending ListItemCollectionPositionNext

//Glboal variables in your js file which will be used in below functions.

var isThreshold = false;

var dtEmployees;

var tempEmployeeData;



function GetDataFromList(strlistName)

{

var queryString = "";

queryString = "<Query>"+

"<Where>"+

"<Eq><FieldRef Name='Company' /><Value Type='Text'><![CDATA[" + "XYZ" + "]]></Value></Eq>" +

"</Where>"+

/*"<OrderBy>"+

"<FieldRef Name='Modified' Ascending='TRUE'/>"+

"</OrderBy>"+*/

"</Query>";

$().SPServices({

operation: "GetListItems",

async: false,

listName: strlistName,

CAMLRowLimit: 4999,

CAMLQueryOptions: "<QueryOptions><Paging ListItemCollectionPositionNext='"+position+"' /></QueryOptions>",

CAMLQuery: queryString,  

CAMLViewFields: "<ViewFields>" +

"<FieldRef Name='Title'/><FieldRef Name='EmployeeName' /><FieldRef Name='Department' />" +

"</ViewFields>",

completefunc: function (xData, Status) {

if(Status.toLowerCase() == "success")

{

// below xml node will return us next position, save it for next query

position = $(xData.responseXML).SPFilterNode("rs:data").attr("ListItemCollectionPositionNext");

//alert(position);

dtEmployees = $(xData.responseXML).SPFilterNode("z:row").SPXmlToJson({

mapping: {

ows_ID: {mappedName: "ID", objectType: "Counter"},

ows_Title: {mappedName: "Title", objectType: "Text"},

ows_EmployeeName: {mappedName: "EmployeeName", objectType: "Text"},

ows_Department: {mappedName: "Department", objectType: "Text"}

},

});

// Threshold exceed.. loop and fetch more than 5000 records.

if(position != undefined && position != "undefined" && position != "")

{

position = position.replace("&","&amp;");

if(tempEmployeeData == undefined || tempEmployeeData == "undefined" || tempEmployeeData == "")

{

tempEmployeeData = dtEmployees;

}

else

{

tempEmployeeData = tempEmployeeData.concat(dtEmployees);

}

isThreshold = true;

GetDataFromList("EmployeesInfo"); // calling same function again for further iteration.

}

else

{

if(tempEmployeeData != undefined && tempEmployeeData != "undefined" && tempEmployeeData != "")

{

tempEmployeeData = tempEmployeeData.concat(dtEmployees);

dtEmployees = tempEmployeeData;

tempEmployeeData = "";

}

isThreshold = false;

ProcessData(dtEmployees); // this can be your custom function which will be use final dtEmployees json object to processing.

}

}

else{

alert("Error ---\n" + $(xData.responseXML).find("errorstring").text());

}

}

});

}

Hope this helps…Happy Coding..!!!