Apr 23 2018 03:17 AM - edited Apr 23 2018 10:45 PM
I made a recent discovery about the list view threshold. The currently preferred workaround is to create indices and filter or sort on indices.
In this image Area is a managed metadata column that is not indexed. When I filter on a single value I get the expected error.
However, if I select one more value in the filter pane I get results. Again, the column is not indexed.
Another way to workaround the issue is to use the Metadata Navigation in classic mode.
While investigating I found that in the first case SharePoint will generate a <Eq> element in the CAML query. In the second it generates a <In> element. I can even exploit this in code.
This will result in a list view threshold error. Note that it is using a <Eq> element.
<View>
<Query>
<OrderBy>
<FieldRef Name="FileLeafRef" />
</OrderBy>
<Where>
<Eq>
<FieldRef Name="Area" LookupId="TRUE" />
<Value Type="Counter">3</Value>
</Eq>
</Where>
</Query>
<ViewFields>
<FieldRef Name="DocIcon" />
<FieldRef Name="LinkFilename" />
<FieldRef Name="Modified" />
<FieldRef Name="Editor" />
<FieldRef Name="Area" />
</ViewFields>
<RowLimit>60</RowLimit>
</View>
This query on the other hand will work just fine. Note that is using a <In> element with a single <Value> element.
<View>
<Query>
<OrderBy>
<FieldRef Name="FileLeafRef" />
</OrderBy>
<Where>
<In>
<FieldRef Name="Area" LookupId="TRUE" />
<Values>
<Value Type="Counter">3</Value>
</Values>
</In>
</Where>
</Query>
<ViewFields>
<FieldRef Name="DocIcon" />
<FieldRef Name="LinkFilename" />
<FieldRef Name="Modified" />
<FieldRef Name="Editor" />
<FieldRef Name="Area" />
</ViewFields>
<RowLimit>60</RowLimit>
</View>
Is this a bug or a feature?
Apr 24 2018 12:17 AM
I did some further research and found that there is something called "fallback queries". These are used when there is no index that can be used by the metadata navigation. When this is used there should be a notification indicating this. I can see this in Server 2013 but not Online. https://support.office.com/en-us/article/Configure-metadata-navigation-for-a-list-or-library-a8ddfcd...