Without grouping, a view that is sorted by an indexed column will display (paged) just fine even when the list has past 5000 items.
However, as you've noticed, trying to filter on any of the column headings will give you an error message... when SharePoint tries to retrieve the values to show in the filter drop-down, it bumps up against its own list view throttling limit.
Metadata Navigation
One way around this problem is to use Metadata Navigation. You'll find this in the list or library settings. Metadata navigation lets you display a filtering sidebar in the quick launch, with drop-downs or hierarchies for certain types of fields. Unfortunately, this won't allow you to filter on normal text fields (whether single line or multiline).
Provide an alternative filtering interface
Another way around this problem is to imitate how the filtering works when you click on column headings. When you filter on a column heading, the page URL gets updated with query string parameters indicating the column and value to filter against. If you plug in valid query string parameters yourself, this functionality will still work (against indexed columns) even if SharePoint itself complains when you click a column heading to see the filter options. It's not the filtering that's busted, it's the interface for filtering!
You can provide an alternative filtering interface with a bit of JavaScript/HTML in a content editor web part.
Try putting the following code into a text file and uploading it to a library on your site. Then, on a view with the list you want to filter, add a content editor web part above the list view. Edit the properties of the content editor web part and plug in the URL of the text file for the Content Link property.
<style>
.hoverbutton{display:inline-block; min-width:139px; padding:3px; text-align:center; line-height:1.2; font-family:Verdana,helvetica,sans-serif;font-size:8pt;font-weight:bold;text-decoration:none;color:white;background-color:rgb(120, 120, 120);cursor:pointer;border:3px solid #f7f7f7;}
.hoverbuttonSelected{display:inline-block; min-width:139px; padding:3px; text-align:center; line-height:1.2; font-family:Verdana,helvetica,sans-serif;font-size:8pt;font-weight:bold;text-decoration:none;background-color:rgb(120,120,120);color:white;border:3px solid black;}
.hoverbutton:hover{opacity:0.5;filter:alpha(opacity=50);cursor:pointer;}
.searchbutton{display:inline-block; padding:3px; text-align:center; line-height:1.2; font-family:Verdana,helvetica,sans-serif;font-size:8pt;font-weight:bold;text-decoration:none;color:white;background-color:rgb(120, 120, 120);cursor:pointer;margin:3px;}
.searchbutton:hover{opacity:0.5;filter:alpha(opacity=50);cursor:pointer;}
._filter_dropdown{margin:3px;min-width:139px;margin-top:3px;margin-bottom:3px;margin-left:0px;margin-right:6px;line-height:1.2; font-family:Verdana,helvetica,sans-serif;font-size:8pt;}
.s4-wpActive .s4-wpTopTable {
BORDER-BOTTOM: transparent 0px solid; BORDER-LEFT: transparent 0px solid; BORDER-TOP: transparent 0px solid; BORDER-RIGHT: transparent 0px solid
}
</style>
<div id="_list_view_filter" onmouseover="return false;" style="display:table;">
<img src="/_layouts/images/loading16.gif"/> Loading options...
</div>
<div id="_list_view_filter_link" style="display:none;float:right;text-align:right;">
<div unselectable="on" style="display:inline-block; user-select:none; cursor:pointer; padding: 3px; background-color:#9b9b9b; color:white; border:1px solid #888;" onclick="javascript:var filt = document.getElementById('_list_view_filter_manage'); if(filt.style.display == 'none'){filt.style.display = 'inherit'}else{filt.style.display = 'none'}">
Manage Filters
</div>
<div id="_list_view_filter_manage" style="text-align:left;display:none;background-color:#dfdfdf; border: 1px solid black; width:95%; max-width:1100px;">
<ol id="ol_list_view_filters">
</ol>
<div style="text-align:right; ">
<div id="_add_list_view_filter" unselectable="on" onclick="javascript: Add_Filter();" style="user-select:none; cursor:pointer; padding: 3px; margin: 3px; display:inline-block; background-color:#9b9b9b; border:1px solid #888; color:white;">Add Filter</div>
</div>
</div>
<script>
function filterDetails(){var title; var type; var field; var label; var choices; var background; var foreground; var id; var guid;}
var listViewFilterList = "List View Filters";
function getFilters(){
while(document.getElementById("ol_list_view_filters").children.length > 0){ /*clear currently displayed list of filters*/
document.getElementById("ol_list_view_filters").removeChild(document.getElementById("ol_list_view_filters").children[0]);
}
this.clientContext = new SP.ClientContext();
var list = clientContext.get_web().get_lists().getByTitle(listViewFilterList);
var camlQuery = new SP.CamlQuery();
var folder = list.get_rootFolder();
camlQuery.set_viewXml('<View><Query><Where><Eq><FieldRef Name=\'URL\' /><Value Type=\'Text\'>'+document.location.pathname+'</Value></Eq></Where><OrderBy><FieldRef Name=\'Priority\' /><FieldRef Name=\'Name\' /></OrderBy></Query></View>');
this.items = list.getItems(camlQuery);
clientContext.load(list, 'EffectiveBasePermissions');
clientContext.load(items);
clientContext.load(folder);
this.clientContext.executeQueryAsync(
Function.createDelegate(this,
function(){
/*Get the current user name from the drop-down box*/
var Me = document.querySelector("#RibbonContainer-TabRowRight span[title='Open Menu'] [accesskey='W'] span").innerHTML;
var baseFormUrl = folder.get_serverRelativeUrl() + "/EditForm.aspx?ID=";
/* Figure out if the current user has access to create or edit items on the Conditional Formatting list */
var perms = list.get_effectiveBasePermissions();
this.hasEdit = perms.has(SP.PermissionKind.editListItems);
this.hasCreate = perms.has(SP.PermissionKind.addListItems);
if(!this.hasEdit){document.getElementById("_list_view_filter_link").style.display = "none";}
if(!this.hasCreate){document.getElementById("_add_list_view_filter").style.display= "none";}
/* Fill an array with returned item properties */
var filterIterator = 99;
var itemEnumerator = this.items.getEnumerator();
var htmlToWrite = "";
while(itemEnumerator.moveNext()){
var item = itemEnumerator.get_current();
var fd = new filterDetails();
fd.title = item.get_item("Title");
fd.type = item.get_item("Display");
fd.field = item.get_item("Field");
fd.label = item.get_item("Label");
fd.choices = item.get_item("Choices");
fd.background = item.get_item("Background");
fd.foreground = item.get_item("Foreground");
fd.id = item.get_item("ID");
fd.guid = item.get_item("ViewGuid");
filterIterator+=1;
if(this.hasEdit){
var listedFilters = document.getElementById("ol_list_view_filters");
var filterToAdd = document.createElement("li");
var editLink = this.hasEdit ? "<div style='display:inline-block;cursor:pointer;' onclick='SP.UI.ModalDialog.commonModalDialogOpen("+'"'+ baseFormUrl + fd.id +'&Source='+document.location.pathname+'"' +",{},refreshPageConditions); '>" + "<img src='/_layouts/images/EDITITEM.GIF' style='vertical-align:middle;' title='Customize' alt='Customize'/>"+" </div>" : "";
filterToAdd.innerHTML = "<b>" + fd.title + "</b> "+editLink;
listedFilters.appendChild(filterToAdd);
}
var isAlreadyApplied = (GetUrlKeyValue("FilterValue"+filterIterator).length > 0)
var filterValues = [];
if(fd.choices != null){filterValues = fd.choices.split("\r\n");}
if(fd.label!=null){if(fd.label.length>0){htmlToWrite += "<h3 class='ms-WPTitle'>"+fd.label+"</h3>"}}
var currPath = getUrlWithoutHashtag();
if(isAlreadyApplied){ /* Strip out the current query string parameters for this field */
currPath = getUrlWithoutParam("FilterValue"+filterIterator, currPath);
currPath = getUrlWithoutParam("FilterField"+filterIterator,currPath);
currPath = getUrlWithoutParam("View",currPath);
}
if(GetUrlKeyValue("FilterName") == fd.field){
currPath = getUrlWithoutParam("FilterMultiValue",currPath);
currPath = getUrlWithoutParam("FilterName",currPath);
}
if(fd.type.indexOf("Buttons") > -1){
for(var i = 0; i<filterValues.length; i++){ /* Generate the buttons */
var thisValueSelected = false;
if(isAlreadyApplied) thisValueSelected = (GetUrlKeyValue("FilterValue"+filterIterator)==filterValues[i]);
htmlToWrite = htmlToWrite + "<a class='hoverbutton"+(thisValueSelected ? "Selected":"")+"' style='text-decoration:none!important;background-color:"+fd.background+"; color:"+fd.foreground+";"
+(fd.type=="Buttons (Vertical)" ? "display:block;":"")+"' href='"
+currPath + (currPath.indexOf("?")>0?"&":"?") + "FilterField"+filterIterator + "="+fd.field
+"&FilterValue"+filterIterator+"="+escapeProperly(filterValues[i])
+(fd.guid != null ? (fd.guid.length > 0 ? ("&View="+fd.guid) : "" ) : "")
+"'>"+(filterValues[i].length>0?filterValues[i]:"(Blank)")+"</a>";
}if(currPath != window.location.href){htmlToWrite += "<a href='"+currPath+"'/>Clear Filter</a>";}
}else if(fd.type == "Dropdown"){
htmlToWrite += "<select class='_filter_dropdown' id='FilterDropdown"+filterIterator+"'onchange='javascript: var newurl = document.getElementById(\"FilterDropdown"+filterIterator+"\").value; if(newurl != window.location.href)window.location.replace(newurl);' ><options>"
htmlToWrite += "<option value='"+currPath+"'>(Choose...)</option>";
for(var i = 0; i<filterValues.length; i++){ /* Generate the options */
var link = currPath + (currPath.indexOf("?")>0?"&":"?") + "FilterField"+filterIterator + "="+fd.field+"&FilterValue"+filterIterator+"="+escapeProperly(filterValues[i]) +(fd.guid != null ? (fd.guid.length > 0 ? ("&View="+fd.guid) : "" ) : "");
htmlToWrite += "<option style='font-weight:bold!important;' value='"+link+"' "+((window.location.href == link)?"selected='selected'":"")+">"+(filterValues[i].length>0?filterValues[i]:"(Blank)")+"</option>";
}
htmlToWrite += "</options></select>";
}else if(fd.type == "Search Box"){
if(GetUrlKeyValue("FilterName").length>0){
currPath = getUrlWithoutParam("FilterMultiValue",currPath);
currPath = getUrlWithoutParam("FilterName",currPath);
}
var searchValue = (GetUrlKeyValue("FilterName") == fd.field) ? GetUrlKeyValue("FilterMultiValue") : "";
if(searchValue[0] == "*"){searchValue = searchValue.substring(1);}
if(searchValue).endsWith("*")){searchValue = searchValue.substring(0,searchValue.length-1);}
htmlToWrite+="<input type='text' value='"+searchValue+"' id='FilterSearch"+filterIterator
+"' onkeydown='javascript:if(event.keyCode==13){window.location.replace(\""
+currPath + (currPath.indexOf("?")>0?"&":"?")
+"FilterName="+fd.field+"&FilterMultiValue=\"+\"*\"+document.getElementById(\"FilterSearch"
+filterIterator+"\").value+\"*\")}'/><a class='searchbutton' style='cursor:pointer;text-decoration:none!important;background-color:"+fd.background+"; color:"+fd.foreground+";' onclick='javascript:window.location.replace(\""
+currPath + (currPath.indexOf("?")>0?"&":"?")
+"FilterName="+fd.field+"&FilterMultiValue=\"+\"*\"+document.getElementById(\"FilterSearch"
+filterIterator+"\").value+\"*\")'>Search</a>";
if(GetUrlKeyValue("FilterName") == fd.field){htmlToWrite += "<a href='"+getUrlWithoutParam("FilterName",getUrlWithoutParam("FilterMultiValue",window.location.href))+"'/>Clear Search</a>";}
}
}
document.getElementById("_list_view_filter").innerHTML = htmlToWrite;
if(this.hasEdit){document.getElementById("_list_view_filter_link").style.display = "inline-block";}
}
),
Function.createDelegate(this,
function(sender, args){
var lci = new SP.ListCreationInformation();
lci.set_title(listViewFilterList);
lci.set_templateType(SP.ListTemplateType.genericList);
var filter_list = clientContext.get_web().get_lists().add(lci);
clientContext.load(filter_list);
var colType = filter_list.get_fields().addFieldAsXml('<Field Description=\'How the filter will be displayed\' DisplayName=\'Display\' Type=\'Choice\' Format=\'Dropdown\' FillInChoice=\'FALSE\'><Default>Equal To</Default><CHOICES><CHOICE>Buttons (Horizontal)</CHOICE><CHOICE>Buttons (Vertical)</CHOICE><CHOICE>Dropdown</CHOICE><CHOICE>Search Box</CHOICE></CHOICES></Field>',true,SP.AddFieldOptions.defaultValue);
var colField = filter_list.get_fields().addFieldAsXml('<Field Description=\'The internal name of the field against which to filter\' DisplayName=\'Field\' Type=\'Text\'/>',true,SP.AddFieldOptions.defaultValue);
var colLabel = filter_list.get_fields().addFieldAsXml('<Field Description=\'The heading to display above the filter\' DisplayName=\'Label\' Type=\'Text\'/>',true,SP.AddFieldOptions.defaultValue);
var colChoices = filter_list.get_fields().addFieldAsXml('<Field NumLines=\'4\' Description=\'Choices to display. Add each choice as a separate line.\' DisplayName=\'Choices\' Type=\'Note\' />',true,SP.AddFieldOptions.defaultValue);
var colBackground = filter_list.get_fields().addFieldAsXml('<Field Description=\'The background color of the filter if it is a button\' DisplayName=\'Background\' Type=\'Text\'/>',true,SP.AddFieldOptions.defaultValue);
var colForeground = filter_list.get_fields().addFieldAsXml('<Field Description=\'The text color of the filter if it is a button\' DisplayName=\'Foreground\' Type=\'Text\'/>',true,SP.AddFieldOptions.defaultValue);
var colPriority = filter_list.get_fields().addFieldAsXml('<Field Description=\'Priority determines the order in which filters are displayed. Higher numbers are displayed lower.\' DisplayName=\'Priority\' Type=\'Number\' />',true,SP.AddFieldOptions.defaultValue);
var colUrl = filter_list.get_fields().addFieldAsXml('<Field Description=\'Page where this filter should be applied\' DisplayName=\'URL\' Type=\'Text\'/>',true,SP.AddFieldOptions.defaultValue);
var colViewGuid = filter_list.get_fields().addFieldAsXml('<Field Description=\'Optionally specify the GUID of a list view to which this filter should apply, in the format {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}\' DisplayName=\'ViewGuid\' Type=\'Text\'/>',true,SP.AddFieldOptions.defaultValue);
clientContext.executeQueryAsync(
Function.createDelegate(this,function(){getFilters();}),
Function.createDelegate(this,function(sender,args){document.getElementById("_list_view_filter").innerHTML = "Something went wrong while we were trying to generate the filter options for you. <br/> Error details: "+args.get_message()+"<br/> "+args.get_stackTrace();}));
}
));
}
function Add_Filter(){
/* Create a new item with only the URL and Priority fields filled out */
var currUrl = document.location.pathname;
var clientContext = new SP.ClientContext();
var itemCreateInfo = new SP.ListItemCreationInformation();
var newItem = clientContext.get_web().get_lists().getByTitle(listViewFilterList).addItem(itemCreateInfo);
newItem.set_item('URL',currUrl);
/* Give the new item a priority that will put it at the end of the list. This is kind of a hack since the highest priority is not necessarily the filtercount. */
newItem.set_item('Priority',(document.getElementById("ol_list_view_filters").children.length+1));
newItem.set_item('Foreground',"White");
newItem.set_item('Title',"Filter "+(document.getElementById("ol_list_view_filters").children.length+1));
newItem.update();
clientContext.load(newItem);
clientContext.executeQueryAsync(Function.createDelegate(this,function(){
getFilters(); /* Reload to refresh the list after adding the item */
}),Function.createDelegate(this,function(sender, args){alert(args.get_message());}));
}
/* This method is called when the Edit Item dialog box is closed. It refreshes the page if the item was saved. */
function refreshPageConditions(result){if(result != SP.UI.DialogResult.cancel){window.location.replace(document.location.href)}}
function getUrlWithoutHashtag(url){
if(typeof(url)=="undefined"){url = window.location.href}
var cleanUrl = url;
var hashtag ="";
if(url.indexOf("#") > -1){
hashtag = url.substring(url.indexOf("#"));
if(hashtag.indexOf("?")>-1){hashtag = hashtag.substring(0,hashtag.indexOf("?"));}
cleanUrl = url.replace(hashtag,"");
}
return cleanUrl;
}
function getUrlWithoutParam(param, url){
if(typeof(url)=="undefined"){url = window.location.href}
var keyValue = GetUrlKeyValue(param);
var cleanUrl = url;
var hashtag ="";
if(url.indexOf("#") > -1){
hashtag = url.substring(url.indexOf("#"));
if(hashtag.indexOf("?")>-1){hashtag = hashtag.substring(0,hashtag.indexOf("?"));}
cleanUrl = url.replace(hashtag,"");
}
if(keyValue.length>0){
var secondhalf = cleanUrl.substring(cleanUrl.indexOf(param));
if(secondhalf.indexOf("&") > -1){secondhalf = secondhalf.substring(0,secondhalf.indexOf("&"));}
cleanUrl = cleanUrl.replace("&"+secondhalf,"").replace(secondhalf,"");
if((cleanUrl).endsWith("?"))cleanUrl = cleanUrl.substring(0,cleanUrl.length-1);
}
return cleanUrl;
}
function clearQueryStringParam(param){
var goodUrl = getUrlWithoutParam(param);
if(goodUrl != window.location.href){
window.location.replace(goodUrl);
}
}
ExecuteOrDelayUntilScriptLoaded(function(){getFilters();},"SP.JS");
</script>
This will give you a way to add filter drop-downs and search boxes to your page.
Maintenance after the limit is surpassed
As you've no doubt noticed, it's tough to "fix" a list after it's passed the 5000 item threshold since you can't add column indexing at that point.
You can temporarily get around the threshold by:
a. Increasing the threshold for the web application (via Central Admin)
b. Adding a window during which large queries are allowed (via Central Admin)
or c. Disabling throttling for a single list (via Powershell run from a web front end server)
I'm a fan of temporarily disabling throttling on a list-by-list basis since it's more surgical, but not everybody has access to execute cmdlets via the Powershell-based SharePoint Management Console.
If you are one of the lucky few with Powershell access, here's the secret sauce:
$web = get-spweb http://yoursharepoint/weburl
$list = $web.lists["Your List Title"]
$list.EnableThrottling = $false
$web.Dispose()
When you're ready to turn throttling back on, just repeat the process but set $list.EnableThrottling to $true.
