JSOM or Rest Api for querying list with more than 5000 entries with query conditions

Question

I am trying to get items from the workflow history list, which has more than 5000 entries using the below code

var ctx = SP.ClientContext.get_current();
var list = ctx.get_web().get_lists().getByTitle("Workflow history");
var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml("<View><Query><Where><Leq><FieldRef Name='Created' /><Value Type='DateTime'><Today OffsetDays='-100' /></Value></Leq></Where><OrderBy><FieldRef Name='Created' Ascending='FALSE' /></OrderBy></Query><RowLimit>10</RowLimit></View>");
var items = list.getItems(camlQuery);
    ctx.load(items);
    ctx.executeQueryAsync(
        function () {

            for (var i = 0; i < items.get_count(); i++) {
                var pageItem = items.getItemAtIndex(i);
                console.log(pageItem.get_fieldValues()['Title']);
            }

        },
        function (msg) {
            console.log(msg);
        });

This works for lists with items less than 5000. Is there a way to make this work for workflow history list?

P.S: I also tried rest api like this

/_api/web/lists/getbytitle('Workflow History')/items?$filter=(Created ge datetime'2017-10-01T07:00:00.000Z')&$top=5000

I got the below error. I was trying to get items created after Oct 2017

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

In case you want to try REST API. Refer this link for querying more than 5000 items from list - https://sharepoint.stackexchange.com/questions/217254/rest-to-read-more-than-5000-items-from-document-library/217451#217451 — Rohit Waghela, Dec 28 '17 at 11:03
will I be able to use filter? like I tried to get items created after August 2017, but it was throwing error — Vignesh Subramanian, Dec 28 '17 at 11:09

score 2 · Answer 1 · answered Dec 28 '17 at 12:14

First, you need to index columns which can be grouped by in REST call(i.e. Filter based on those index columns until you get a result which is less than 5000).

let say you have 10,000 items in a Products list. When you filter it with Product(indexed column) you get 7000, again you need to filter it with Discount(indexcolumn) now the final result will be 3000 items

https://webapp.site.com/_api/web/lists/getbytitle('Products')/items?$filter=Product eq 'iPhone' and Discount_x0020_Type eq 'xMas'

Detailed MSDN documentation for indexing. here.

score 1 · Answer 2 · answered Dec 28 '17 at 12:01

You should be able to query a list if the coloumns you are using are indexed. I don't know if that also work for the filter option.

Here's an example (Post request) on a query on a list (replace #1 with the guid of your list):

/_api/web/lists(guid'#1')/GetItems(query=@v1)?@v1={"ViewXml":"<View><Query><Where><Eq><FieldRef Name="Title" /><Value Type="Text">test</Value></Eq></Where></Query></view>"}

score 1 · Answer 3 · answered Jan 02 '18 at 08:38

1

For the field in Lager list which is more than 5000 items, if you want to filter the field in Rest API, you need to make this field indexing: REST api querying large list

answered Jan 02 '18 at 08:38

Jerry

2,583
1
11
11

JSOM or Rest Api for querying list with more than 5000 entries with query conditions

3 Answers3