6

I've done tons of reading on the list view threshold and ways to work around it but I haven't been able to answer a couple questions or ultimately find a good way to deal with it in my situation.

This is on 2010 Foundation

I was under the impression that by creating a view in a list that has an "item limit" set - to say in batches of 100 or something - that this would not throw the threshold error since it is returning only 100 items at a time. However, I have this in place in all of my libraries and those that are over 5000 do not display and throw the error. That being said, those views are sorted by modified date. When I change the view to not be sorted at all, it displays fine but will not allow any filtering due to the threshold limit! (isn't that the point of filter?...so you don't show all of the items?)

So is this supposed to work or not? Note that modified date, as well as several other notable columns are indexed.

Same question applies to views that use "group by". I read that this bypasses the error but I am not finding that to be true. Still get the error. Should this work or not?

Most of my libraries have items/columns that simply won't support using views to find the documents that someone may be looking for. We need to use filters. For example, we have one that is property files. It contains all the documents applying to each property in the area. The columns used are PIN#, Address#, street, a choice column for the type of document etc. When searching for something, we need to filter the documents to show only those with a certain PIN or address. Obviously, creating a view for the 10,000 or so properties is not feasible.

So how do our users filter an entire list when the only way I can display it for them is if it only shows a subset? The filter would then only filter the subset and not return everything it should.

In short, how do i filter all of a list but not show the whole thing prior to the filter?

I've already dabbled with raising the limit but I want to keep it where it should be for performance and it would be temporary anyway as some of these libraries will continue to grow very far past 5,000.

Thank you for any help/suggestions you can offer!

  • I'm sorry to tell you this, but there isn't a good answer, which is probably why you haven't found one in your reading. The view has to be filtered by an indexed column and return fewer items than your threshold limit. If you have huge amounts of documents to manage, you might want to invest in a document management system intended to work with SharePoint. – Erin L Dec 31 '15 at 19:40
  • Well Sharepoint was "sold" to us as a document management system. That's our main use and why we started using it. We also use it for an intranet portal and other things but storing documents is the main function. Seeing how it supports storing 30,000,000 items per list I would think it was meant for the purpose. Perhaps there are better choices but there must be a way to use it despite the 5000 limitation. – Eric Dec 31 '15 at 20:02
  • I'm really sorry to hear that. Foundation has many limits... if you upgrade t Standard, creating a Document Center might be of use. Or Docuware is a fairly affordable third-party solution that I think could help you. – Erin L Dec 31 '15 at 20:10
4

Simply showing a List/Library in batches or using group by still causes SharePoint to query for all records in that List/Library. Using a column index with a view and a filter based on the index is a requirement to stay under the LVT. And in this case, the filter must return less records than the LVT is set to (5K by default).

If you were using SharePoint 2013, I would suggest using a Content Search Webpart, where you could search for results instead of using a view. But otherwise, you're stuck with index + view + filtering or disabling the LVT on select lists, or finally upping the LVT, which I wouldn't recommend.

0

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'/>"+"&nbsp;</div>" : "";
                        filterToAdd.innerHTML = "<b>" + fd.title + "</b>&nbsp;"+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.

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.