11

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.

  • Good job on providing a clear means to replicate the issue. When i get some time I will try it myself, thanks! – Cameron Verhelst Feb 25 '14 at 20:03
  • We have been going through similar situation, even if the list is not having any look up column, as soon as the items go beyond 5000, column filters stop working. Even if the view is getting less number of records. Looks like the column filter is trying to read all items inspite of how much data is contained in the current view. – Sudhir Kesharwani Jun 12 '14 at 22:26
  • @SudhirKesharwani thanks for sharing this. I think this is simply unacceptable! How do they think one should interact with a list larger than 5000 items? ridiculous. – MdMazzotti Jun 13 '14 at 8:20
  • I seen this happen on lists that have well under the 5000 item limit. For those cases, it was intermittent, sometimes the filters would work, sometimes they wouldn't. sharepoint.stackexchange.com/questions/93618 – James Grizzle Dec 30 '14 at 15:20
  • I think you are looking for a chorus of workarounds then. When the quick easy methods will not work, and you are up against a hard threshold, it is time to look at more complex/more performant methods.*What about using web parts with connections. Create a page with a filtered web part and use the column values in a filter web part attached to it. *What about a page with relative javascript links to get the vales to filter by? *If you were writing this query by hand you would not do a select distinct on 5000 rows of values in a linked list, you would just go get the linked list, then query – ghangas Dec 30 '14 at 16:22
2

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
  • 2
    This is a good way to determine if this is the root cause to your issue or not. – Cameron Verhelst Apr 13 '16 at 7:38
0

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.

  • Do you have a source about the threshold limit raise in 2016? I've seen the autoindexing of columns, which doesn't help much. – eirikb Aug 9 '16 at 7:19
0

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.

-1

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

  • 3
    Yeah, I know this, but doesn't really apply to my case. I read almost everything that has been written about the matter. The indexed column on which I apply the WHERE condition never returns more than 100 items. Try yourself if you can and you'll see the behaviour I described in my question. – MdMazzotti Feb 25 '14 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.