1

I'm developing a custom sharepoint solution (SP 2016) where I'm retrieving data from a list with 5000+ items in it. In one of the API calls I'm trying to filter on a look up field where the list in which the look up is made in also has 5000+ items. When the list I'm querying has less than 5000 items it works without errors but as soon as it has more I get a Microsoft.SharePoint.SPQueryThrottledException.

I know there is limit preventing people from making queries that return more than 5000 items but in my case the query would only return about 10.

Filtering on any other field works fine even when the list is 5000+ in size.

Are there further restrictions for filtering on look up fields compared to other field types?

The query:

"/_api/Web/Lists/GetByTitle('FIRMAMAPPER')/Items?top=100" +
        "&$select=id,FIRMANR/FIRMANR,FIRMANR/FIRMANAVN" +
        "&$expand=FIRMANR" +
        "&$filter=FIRMANR/FIRMANR eq " + ArbitraryNumber;
8
  • it would help to have a look at the query. If you're using something like <contains> you'd run into such a gap...
    – Celophysis
    Jul 22, 2019 at 13:44
  • Try indexing the column used as filter in your query Jul 23, 2019 at 6:10
  • @Celophysis Editited the post to include my query.
    – fredd5229
    Jul 23, 2019 at 7:29
  • @AkshayRandive All the columns are indexed already, in both lists.
    – fredd5229
    Jul 23, 2019 at 7:30
  • You missed a $ before your top parameter. Otherwise your query works, at least in my env.
    – Salah
    Jul 23, 2019 at 8:01

1 Answer 1

0

As stated in the comments I ended up solving this with a work around where I make sure to only query 5000 items or less at a time and then filter those items on the values I need. The process goes likes this:

  1. Find the lowest Id and the highest Id in the desired list and calculate how many calls the request must be split into: Math.ceil((highest-lowest)/5000);

  2. Create a loop with that many iterations and use a filter like this to get items recursively: "$filter=Id ge " + lowestId+i*5000 + " and Id lt " + lowestId+(i+1)*5000 + " and (other filters)"

  3. Save the results of each iteration so they can be accessed afterwards.

Your Answer

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

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