2

The following query works on one site: mysite/_api/lists/getbytitle('Listname')/items?$top=5001&$filter=IndexedCol1 le 'M' and Author/Title eq 'John Doe'&$expand=Author/Title&$select=Author/Title,ID,Title

On a separate site which is identical in every way but the site name and the fact that it has 6,000 items vs the 4,000 in the previous site, I receive the following response:

{"error":{"code":"-2147024809, System.ArgumentException","message":{"lang":"en-US","value":"Value does not fall within the expected range."}}}`

IndexedCol1 represents an indexed column by which I have successfully sorted my views (less than or equal to M and greater than or equal to M). All other fields and filters have been working on this large list when I combined them with the 1IndexedCol1 le 'M' filter. Even substrings and greater than/less than operations work.. How can I resolve this issue with Author/Title that only applies when my list has over 5,000 items?

edit: This seems to apply to ALL user fields. The simplest query still gives me the out of range error: &$expand=Author&$select=Author/Id works just fine, but &$expand=Author&$select=Author/Title,ID,Title and &$expand=Author/Title&$select=Author/Title,ID,Title do not work on one list while they do work on another.

  • 1
    I'm not querying for more than 5000 items. This query would return 5 items at most. I include a top of 5001 so that an actual error is generated when the 5000 limit has been met. This is better than returning 5000 items and making my users believe they've actually identified all items in a list before they've clicked submit to perform actions on them. I did still try your suggestion, but nothing changed :( – Dinerdo May 28 '15 at 20:52
  • That's not applicable to what should happen where more than 5,000 items are returned. Thank you for the suggestion, but the issue still exists without the top parameter (which limits results to 100 or 1000 by default). – Dinerdo May 28 '15 at 20:56
  • Can you confirm Author field is added to indexed columns? – Amal Hashim May 28 '15 at 20:57
  • I did index Created By. But even still, the first field (IndexedCol1) is already indexed, and all of my other queries to non-indexed fields do not have this issue. – Dinerdo May 28 '15 at 21:00
  • 1
    Ok try removing one condition from filter and see if it works. mysite/_api/lists/getbytitle('Listname')/items?$top=4999&$filter=IndexedCol1 le 'M'&$expand=Author/Title &$select=Author/Title,ID,Title – Amal Hashim May 28 '15 at 21:02
1

There are two known ways to by pass List View threshold

  1. By changing resource throttling value in Central Admin - This not recommended approach
  2. By creating indexed columns. Using List Settings add fields you are filtering to Indexed Columns.

A list supports 20 indexed columns, so you can try adding more fields to improve the performance.

  • 3
    This is not an index/list view threshold issue. I am aware of how that works. Please check the last paragraph in my question. The threshold is actually currently removed during this time period, so I can return all 6,000 items in a simple "ID gt 0" query or even querying on non-indexed fields. This is a separate issue. – Dinerdo May 28 '15 at 21:20
0

It looks like this is a SharePoint defect affecting all lookup fields. It's linked to having to unique permissions on a list with over 5,000 items.

Very disappointing.. :( Oh well, I will change my target list to the User Information List to find the user, grab the ID, and then query my my list based on Author/Id instead of Author/Title. I will leave this question up for a little while to see if anyone has other workarounds.

The workaround was successful btw. It looks like all you can do is to query the list that the lookup belongs to (in this case, the User Information List) once you run into this issue.

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.