Our team has been working on converting legacy SharePoint applications written in server side code to client side code on a newer version of SharePoint. The old environment lifted the List View Threshold to a non-default range, which the new environment did not have/support. Our goal was to have these applications respect the list view threshold. However, in our Dev/INT environments, we could not figure out why we were not seeing the list view threshold. Turns out, the list view threshold is NOT enforced if you are a LOCAL ADMINISTRATOR on the WFE server.
Any user in the local administrators group will not have the list view threshold enforced. When we used test user accounts, they were getting the threshold error as expected since they were not a developer’s user account in the local admins group. Simly remove the user from local administrators and they will get the threshold error immediately if it shows for regular users.
- Index Columns
- Up to 20 per list
- Do this before you get 5,000 items in your list, or you cannot dynamically change them (unless you are in SPO, I hear they are raising it to 20,000 and allowing you to set indexes when you have items above that, but we will see once its out if its not already)
- Index anything you want to filter, group or sort by, including OOTB columns such as Created/Modified, etc.
- Remove or Fix your existing Sorting/Filter/Grouping on views
- The rule we follow is: Remove all filters. Add your first indexed filter that should return LESS than 5,000 items, or the view wont work. Have over 5,000 items? well, sorry, but find another filter (or use the All Items view, read on)
- Your second and subsequent filters can further narrow down from 5,000 by using AND to join filters, but beware of OR since it immediately negates your first filter, giving you the threshold if that column is not indexed and returns less than 5,000 items. We have also noticed swapping the order in complex filters seems to magically work and we cannot explain it. So if you have 2 filters then an OR and 2 more filters, try reversing the order of the last #3 and #4 columns if you get the threshold error.
- All Items view is your friend
- Ever wonder why OOTB All items shows fine? Well its because if you leave it without Fitlers/Sorting/Grouping it will work and can query over 5,000 items (with paging of course). Microsoft left this view for some magic. And if you ask MS Support, they wont explain it other than share the magic.
- 5,000 folder limit
- This one is our most recent issue, you cannot return more than 5,000 folders in any view, including All Items view. To fix this, apply some type of query to return less than 5,000 folders, OR disable showing items in folders (Show the items without folders and it will work).
So, if you watch out for who you have in local administrators, and follow these 4 rules, you can probably get your views to work (somewhat). However, if you are that person who needs more than 5,000 items on your query returned, try exporting the magical All Items view to Excel or design a custom export solution, or use a 3rd party tool such as Power BI or Tableau to query the list.