0

Wondering if anyone can help with an issue I am having with the SharePoint REST API. I currently have a Power Automate flow that runs on a daily schedule, this uses the 'SharePoint HTTP' action to get filtered items from a SharePoint list.

Currently when the flow is running I am getting an error as follows:

{ "odata.error": { "code": "-2147024860, Microsoft.SharePoint.SPQueryThrottledException", "message": { "lang": "en-US", "value": "The attempted operation is prohibited because it exceeds the list view threshold." } } }

Using Postman I have run the same REST call and I get the same issue. The REST endpoint I am calling is as follows:

_api/web/GetList('/sites/SiteCollection/Lists/mylist')/items?$select=responsibleUser/EMail,resposibleOwner/EMail,Title,dueDate,customerName,Modified,Id,&$expand=responsibleOwner/EMail,responsibleUser/EMail&$filter=((dueDate le '2021-06-04')  and (status ne 'Closed') and (region eq 'EUR'))&$top=500

The list in question does currently have over 5000 items, however the query above should only return approximately 59 items. The three fields that I am using to filter on the list have all been indexed but I continue to get the above error.

The only way I appear to be able get this working is by removing the 'dueDate' field from the filter expression then I get results returned. The date field is however indexed as I previously stated, I have even tried removing the index and adding it again but this still fails.

Appreciate any help anyone can give on this.

1
  • Add most restrictive filter condition at first in filter query, check below answer. Jun 2 at 10:41
1

If first filter in your query returns more than 5000 items, you will get this error/exception.

So, try adding the filter which return less than 5000 items first in filter query.

For example: If status ne 'Closed' returns less than 5000 items, you should add it first in filter query & then other filters like:

$filter=((status ne 'Closed') and (dueDate le '2021-06-04') and (region eq 'EUR'))
1
  • 1
    Ganesh.... you are my hero!
    – Adam13Hylo
    Jun 2 at 10:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.