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" +
        "&$expand=FIRMANR" +
        "&$filter=FIRMANR/FIRMANR eq " + ArbitraryNumber;
  • 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 '19 at 13:44
  • Try indexing the column used as filter in your query – Akshay Randive Jul 23 '19 at 6:10
  • @Celophysis Editited the post to include my query. – fredd5229 Jul 23 '19 at 7:29
  • @AkshayRandive All the columns are indexed already, in both lists. – fredd5229 Jul 23 '19 at 7:30
  • You missed a $ before your top parameter. Otherwise your query works, at least in my env. – Salah Jul 23 '19 at 8:01

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, privacy policy and cookie policy

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