0

I have a List in a SharePoint Online site collection which has exceeded 5000 items. I have indexed the columns that are needed in the REST API filter query during list creation itself.

Below is the Rest API query I use to fetch the data from the list.

https://sptenant.sharepoint.com/sites/spfxqaw44/_api/web/lists/getByTitle('Announcements')/items?$top=4999
&$filter=(Expires ge '2022-12-06T18:30:00.000Z') and (RecordDate le '2022-12-06T18:30:00.000Z') and IsActive eq 1 and WorkflowCompleted eq 1
&$select=Id,Title,Expires,ShortDescription,Created,RecordDate,Attachments,LangTrans&$expand=AttachmentFiles&$orderby=RecordDate desc

The above query gave me an error as threshold limit exceeded. So I have created a simple list and made a API call as below,

https://sptenant.sharepoint.com/sites/testsite/_api/web/lists/getByTitle('LoadTestList')/items?$filter=Title ne 'Load Test'&$top=4999

Generally the return data count should be 4999 but I am able to get only 4970 items as response data. Also this response count is unpredictable, for some other lists I got response count less than 4000.

Any idea on this inconsistent behavior?

Improve this question
3
  • How many items are in the list when you try this filter: $filter=Title ne 'Load Test'&$top=4999?
    – Ganesh Sanap - MVP
    Commented Dec 8, 2022 at 7:39
  • The total item count in the list is 5001. Number of items that has title 'Load Test' is 6. As per the query I should get response count as 5001 - 6 = 4995 but I am getting threshold limit error if there are 4995 items. If I am increasing the items that has title 'Load Test' as 7, I am getting proper response and the count is 4994. Why am I getting error for 4995 items. I am confused. And this count 4995 is not same for all the lists, I have another list which gives me throttle error for 4950 and getting proper response for 4949 items.
    – Nagarajan Muthukumar
    Commented Dec 9, 2022 at 6:05
  • make sure that column you trying to filter on is Index if not create index
    – Bhavesh
    Commented Apr 24 at 5:07

1 Answer 1

Reset to default
-1

When we try to access more than 5000 items at a time, which is the default threshold limit, we get an error.

You can run your query multiple times, storing each result.

Please refer: Resolve threshold issue in SharePoint REST API

Improve this answer
2
  • This is the general practice whenever threshold limit is reached. We have done these already.
    – Nagarajan Muthukumar
    Commented Dec 9, 2022 at 6:07
  • You can edit your very very helpful post by adding those details.
    – Juan Castellanos
    Commented Dec 27, 2022 at 2:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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