Document Library Not Filtering Columns

Question

There is a document library which has over 5000 items, it has passed its item view threshold. The view was reduced to show items that met certain requirements. This view now shows less than 1000 records.

When some users use the the column name to filter they receive a message "Cannot show the value of the filter. The field may not be filterable, or the number of items returned exceeds the list view threshold enforced by the administrator."

The threshold item view limit is in the Central Administration but this is not something that we want to change because of performance reasons, so this would be a last option for this. Some of the columns the view is filtered by are indexed, as this was a suggestion.

The way I see it is that it should be working properly. The view is within the threshold limit so it should not have an issue. All users were able to use it properly before it reached 5000 items. This error shows for some users but not all.

Is there something else that should be done to allow all users to have access to filter the view by using the column name?

UPDATE: The list view currently has 3 filters on one column. The column is a choice and is indexed.

The filters are as follows:

column1(Indexed) is equal to value1

Or

column1(Indexed) is equal to value2

Or

column1(Indexed) is equal to value3

When I remove one of the filters it works fine, it does not matter which of the filters I remove. Filter_1 return 2880 items, Filter_2 return 17 items, and Filter_3 returns 97 items. For a total of 2994 items returned.

Would there be a restriction on having multiple "Or" filter statements on the column?

Answer 1

I've had my woes with the list view threshold being reached. Good call first off on not increasing the threshold. The best advice I have is to review the below article, which toward the bottom has tips on how to handle large lists/libraries while avoiding increasing the threshold. It explains in better detail what I'd like to tell you.

Useful Link

Answer 2

In addition to approach suggested by Jeffrey, if you wish to access list exceeding threshold by code(create a web part with filtered data display), refer to link given below:

Overcome list threshold

Answer 3

The problem stems from the list view web part itself, and the way it tries to retrieve all possible filter values for the column, which causes it to bump up against the list view threshold even when your view is able to circumvent it.

One alternative is to use metadata navigation, which adds specified filtering options to the quick launch. Of course, this only works for certain types of fields.

Another alternative is to provide your own filtering mechanism to the users. You can apply filtering to any list view web part by including some query string parameters in the URL.

To filter on a specific value, append the following to your URL:

?FilterField1=InternalNameOfColumn&FilterValue1=TextToFilterAgainst

You can also perform wildcard filtering against most types of columns using the following format:

?FilterName=InternalNameOfColumn&FilterMultiValue=*TextToFilterAgainst*

You can use this technique to create your own filtering links or search boxes.

Answer 4

There is no restriction on having multiple "Or" filter statements on the column but the results count should not cross the 5000 limits.

My feeling is that when you use the below 3 filters, the results count are crossing the threshold limit (5000). Make sure that the results should not cross 5000 items, try to add more filters to reduce the results count.

column1(Indexed) is equal to value1

Or

column1(Indexed) is equal to value2

Or

column1(Indexed) is equal to value3

Using filtered views

Check this article and also How can I configure a View filter/index to go around the item threshold?

Filters reduce the amount of data returned by selecting items based on operations (such as greater than or less than) and matching data. When you create a filtered view, your first indexed column in the filter expression should reduce the data set the greatest amount. For example, filtering on a state or date field can often reduce a data set considerably. If you've got 15,000 items, you'll want to have the first index return less than 5000 items. If your data set doesn't have over 5000 items, it's still a good practice.

SharePoint selects the first indexed column in a query, and then uses the rest of the columns to select your data. Other columns you specify in the view filter may or may not be indexed. The view does not use those indexes, even if the result of the filtered view returns less than the List View Threshold.

For example, you have a query: size = large AND color = red. In the list, size is not indexed, but color is. As long as there are fewer than 5000 "red" items in the list, the query succeeds in a large list. However, if you have a query size = large OR color = red, though database may find all the red items, it must scan the complete list to find all the large items. If there are more than 5000 items returned, the query is throttled.

If you use two or more columns in the filter expression, the determining index or indexes should use an AND operator. For example, if you want to return Dogs from a large list of animals. You have an unindexed column called Species where you have Dog as a value. If you just query for Species = Dog, your query will be throttled. However, if you have an indexed column called Class, your query becomes Class = Mammals AND Species = Dog. You could also search for cats and dogs with the query Class = Mammals AND (Species = DOG OR Species = Cats). The second query selects all Mammals, and then filters to Dogs and Cats.