This is a summary of the behavior of the List View Threshold (LVT) with choice, lookup, managed metadata and person and group columns.
- The columns are indexed where possible.
- The List View Threshold is at 5,000.
- For each type of column there is a filtered view named Red that contains around 50 files and there is a filtred view named Green that contains around 6,000 files.
- Tested in SharePoint Online in September and October 2016.
My biggest discovery was that the filtered view displays differently when the metadata navigation is activated.
Below, I’ve highlighted some unexpected behaviours in yellow. I also include some links towards my YouTube videos.
| Column Type | Values | Can be indexed | Number of files | Filtered View | Metadata Navigation can be used | Filtered View with Metadata Navigation | Metadata Navigation |
| Choice | Unique | Yes | Red: 50 | OK | Yes | OK | OK |
| Green: 6000 | LVT Error | Fall Back Query | Fall Back Query | ||||
| Multiple | No | Red: 50 | LVT Error | Yes (key filters only) | Fall Back Query | Fall Back Query | |
| Green: 6000 | LVT Error | Fall Back Query | Fall Back Query | ||||
| Lookup | Unique | Yes | Red: 50 | LVT Error | No | Fall Back Query | Impossible |
| Green: 6000 | LVT Error | Fall Back Query | Impossible | ||||
| Multiple | No | Red: 50 | LVT Error | Fall Back Query | Impossible | ||
| Green: 6000 | LVT Error | Fall Back Query | Impossible | ||||
| Managed Metadata | Unique | Yes | Red: 50 | LVT Error | Yes | Fall Back Query | OK |
| Green: 6000 | LVT Error | Fall Back Query | Fall Back Query | ||||
| Multiple | No | Red: 50 | LVT Error | Fall Back Query | OK | ||
| Green: 6000 | LVT Error | Fall Back Query | Fall Back Query | ||||
| Person or Group | Unique | Yes | Red: 50 | LVT Error | Yes (key filters only) | Fall Back Query | OK |
| Green: 6000 | LVT Error | Fall Back Query | Fall Back Query |
LVT Error: This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.
Fall Back Query: Displaying only the newest results below. To view all results, narrow your query by adding a filter.
On this page you can read about more cases of the strange behaviour of the LVT:
https://ktnnsharepoint.wordpress.com/2016/08/02/big-lists-threshold/
KTNN SharePoint 