Filters on a large list exceeding throttle limit

Question

On a SharePoint 2010 Server installation, I have a list with more than 5000 items. I created an indexed column and a view based on this column which is returning 100 items only, so far less than the throttle limit.

All is working properly, except the columns filters. When I click on any of the columns, the modal dropdown doesn't show any value for filtering. The only thing I can do is sorting.

I made another test by using a DataFormWebPart in multiple list view mode: the dropdown reads "This column type cannot be filtered".

If I try to filter by using the URL parameters (with &FilterField1=FieldName&FilterValue1=SomeValue), I can see the filters get applied, but if I then click on any column to show the dropdown I get a popup message saying "Cannot show the value of the filter. The field may not be filterable or the number of items returned exceeds the list view threshold".

Does this mean that once a list has exceeded the throttle limit, the automatic column filters that are showed in the XSLT list view webpart cannot be used anymore?

EDIT:

if you want to try and see it for yourself, just create a custom list with two columns, Title and Code (of type Number) and create an index on the Code column. Then, here is an Excel with some test data that you can use to populate the list (just copy from Excel and paste in datasheet view).

EDIT 2 -important-:

In my attempt to strip down the example to make it easier, I actually left out an important piece of the puzzle, which is the throttled list has a lookup column to another list. So, in order to correctly reproduce the issue, you need to add another list (a default custom one with only the title column will do) and add a lookup column to the first list point to the second. It is enough to leave the field empty, it doesn't really have any impact on the behaviour. What I concluded is that if a list has a lookup column, then the automatic filters don't work anymore, even if the index was correctly set and the view is returning less than the throttle limit.

Answer 1

Hi how about you disabling the list throttle for those list that exceed the limit. You could disable it using following powershell script snippet

$web = Get-SPWeb http://myserver
$list = $web.Lists["MyCustomList"]
$list.EnableThrottling = $false

Answer 2

Its a con of SharePoint Online, You can not perform any filtering either IN SharePoint or OUTSIDE of it (using custom coding) after it reaches its threshold limit, only thing will work is Paging.

And for coders,

In REST => $top query will work

In CSOM/JSOM => RowLimit will work

But this issue is going to be resolved in SharePoint 2016 as they will increase the Threshold limit.

Answer 3

Are you able to filter on the Indexed columns? If the Indexed column unique values are less then the throttle limit then it should show up as filters. Or, if the indexed values are little over than the current throttle limit, then you can increase the limit for that list to get the data for the indexed columns.

Answer 4

When filtering, you need to make sure that it can't return more items than the specified treshold from the start. Then it should work as expected.

You also need to make sure that the first column of the filter does not return more items than the List View Threshold, even if the final result of the filtered view returns less than the List View Threshold.

from SharePoint: Manage lists and libraries with many items