I have a custom list that has over 24k items and utilizes lookup columns. We have indexed columns and have been working with filtering to allow different views to manage the list for a few months now. We are currently having issues with a personal view that filters based on an indexed choice column. This is a basic view with the default style, no grouping and no totals. The filter would not return more than 5,000 items, but we receive the good ole 'This view cannot be displayed....' error message. This was working a few weeks ago (before the list hit 20k items). Views that filter based on other indexed columns are still working without issue. I know I am missing something but I cannot put my finger on it, any advice on how to resolve this error and get this view working again would be greatly appreciated. Side Note- I have access to change the list view threshold but this is not an option for the business.
We can add indexes on up to 20 columns on a list or library. While you can add up to 20 indexes per list or library, it's recommended you add indexes to only to the most commonly used columns. Indexes add overhead to the data.
We can normally still add indexes to columns when you have fewer than 20,000 items in your list or library.
Please check the total number of columns indexed and Choice (single value) is Supported Column Types and Choice (multi-valued) is Unsupported Column Type
Supported Column Types
- Single line of text
- Choice (single value)
- Date and Time
- Person or Group (single value)
- Managed Metadata
Unsupported Column Types
Multiple lines of text
Hyperlink or Picture
Person or Group (multi-valued)
please check the Office link Add an index to a SharePoint column for more details