0

I need to filter a list which has more than 5000 items using SharePoint REST API

One of the columns to filter is a managed metadata column which prevents me from using SharePoint REST API GET Urls

I have to use REST API POST requests with CAML Query in the body to achieve this.

My code looks like below

var viewXml =
{
    ViewXml: "<View>" +
    "<Query>" +
    "<Where><Eq>" +
    "<FieldRef Name='RegionTestHidden'/>" +
    "<Value Type='TaxonomyFieldType'>" + "North" + "</Value>" +
    "</Eq></Where>" +

    "</Query>" +
    "</View>"
}

function CamlQueryRESTCall(listName, viewXml)
{

    var call = jQuery.ajax(
        {
            url: _spPageContextInfo.webAbsoluteUrl+ "/_api/Web/Lists/getByTitle('"+listName+"')/GetItems(query=@v1)?" +
            "@v1=" + JSON.stringify(viewXml),
            type: "POST",

            dataType: "json",
            headers:
            {
                Accept: "application/json;odata=verbose",
                "X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
            }
        }
        );

    return call;

}

The problem I am encountering is if i filter based on region South which has more than 5000 items as result, I am getting HTTP Error 500 Internal Server Error with error message List item threshold exceeded.

With the GET requests I can use $top and data.d.__next to load more than 5000 items. But how to do similar logic in POST requests?

I tried including <RowLimit>1000</RowLimit> in the CAML Query but still same error

The columns used in query are indexed btw.

Abdul Hameed
  • 1,025
  • 12
  • 27
  • I've encountered with this problem, what you need to do is to create an index to a column. The problem of this is that if you have your list with more than 20.000 items you cannot create the index. Take a look to this post https://support.office.com/en-us/article/add-an-index-to-a-sharepoint-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0 – Brank Victoria Nov 21 '18 at 13:01
  • @brank like i mentioned in the last line of the question, the columns used in the query are indexed already – Abdul Hameed Nov 22 '18 at 04:37
  • Hi, have you tried to use the search api instead of GetItems? – Brank Victoria Nov 22 '18 at 08:34
  • Search api doesnt accept filtering on managed metadata type columns – Abdul Hameed Nov 22 '18 at 09:07

1 Answers1

0

This is due to the List view threshold that SharePoint has. See here

It is a threshold and you can change that to fit your needs. This is done in Central Admin like this:

  • Go to your SharePoint Farm's Central Administration
  • Go to the "Manage Web Applications" under the "Application Management" section
  • Select the "Web Application" where you migrate the site
  • Click the "General Settings" dropdown and select "Resources Throttling"
  • Change the "List View Threshold" to 12000 or something like that.
Sizemj
  • 322
  • 2
  • 8
  • 15