Getting Around SharePoint’s Threshold Limits for Large Reference Lists

In SharePoint 2007, we could build lists that contained as many items as we wanted and access them in any way we wanted. We may have done stupid things that were bad for server performance, but if we knew what we were doing there were few limits. We had the mythical “2000 item” rule of thumb (which had little basis in reality in many, many cases) but otherwise it was up to us. (See: Plan for software boundaries (Office SharePoint Server))

In SharePoint 2010, Microsoft introduced threshold limits to protect us from ourselves. This seemed counter intuitive to me, since with the upgrade to SharePoint 2010, one also had to go to 64 bit architecture and put a heck of a lot more iron behind the SharePoint farm. While we could potentially store 30,000,000 items in a list (30 million!), we had a list view threshold of 5000 items. (See: SharePoint Server 2010 capacity management: Software boundaries and limits)

SharePoint 2013 maintains similar limits to 2010, with 5000 items the limit for a list view. (See: Software boundaries and limits for SharePoint 2013)

List View ThrottlingSomehow, as technology has moved forward – significantly – we’ve got more limits.

5000 items is way too many to ever show in a list view, but it may not be if you want to do some client side processing. It doesn’t matter if you’re trying to build a truly bloated, Bad Idea list view or request data using REST, you’re stuck with that 5000 item limit.

But what if you know what you are doing and the 5000 item limit doesn’t work for you? I’ve written in the past about why we shouldn’t change some of the threshold limits (e.g.,  The SharePoint 2010 “List View Lookup Threshold” and Why We Don’t Change It).

If we’re working on Office365, we simply can’t change the limits – Microsoft gets to decide what they are. Sometimes those limits change without notice, too, so it’s truly out of our hands.

Well, what does all this add up to? Let’s take a specific example.

I needed to provide auto complete functionality in an InfoPath form. It was the classic scenario: we had a list of [in this case] 19,000+ cities and towns and we needed a good UX for entering selections into the form. We run into this sort of thing with data like cities and towns all the time. There are too many choices for a dropdown to make any sense, and SharePoint doesn’t offer up any better alternatives. jQueryUI’s autocomplete function is a perfect solution for this sort of thing.

The data was in a spreadsheet, and it’s easy to upload data from a spreadsheet to a SharePoint list. Bingo, bango, done, right? Well, no. It’s impossible to upload data from a spreadsheet *and* index the column. We need to index the column so that we can make type of requests requests that we need for autocomplete – basically, give me all of the items that begin with or contain this string – or else we run into the 5000 item threshold error on our requests.

No problem! We’ll just upload the data and *then* index the column. Not so fast, mister. When you try to index the column you run into – you guessed it – the 5000 item limit. No indexee, no laundry.

So seemingly we’re stuck. But wait – we can just create the list in the UI, add the index, and paste all 19,000 items into the Brave New World of SharePoint 2013’s “data sheet view” aka “Quick Edit”. Sadly, when I tried to paste in more than a few hundred items, the browser hung on me. This was on Office365, and I tried several different browsers. SharePoint Online seems to prefer nibbles to big bites of data. Unless I spent days just doing the pasting, this was never going to happen.

In this case, I decided to simply store the data in a text file containing JSON rather than trying to force it into a list. Thanks to @bpmccullough for the suggestion on Twitter.

The data I originally had received for the sities and towns was in a CSV file. I found a nice little site that would convert the CSV format to JSON (bookmark this one; you’ll need it in this JSON-oriented world).

Once I had the JSON file uploaded to a Document Library, the code below is what I used to set up the autocomplete. We wanted the autocomplete to allow multiple choices, so there’s a little monkeying around to enable that. It wasn’t tremendously peppy (19,000 cities, after all), but it worked. Remember, kids: this is an example. Don’t expect to drop it into your page and have it work.

var citiesAndStates = [];

// Get the City and State data from the file with JSON data
$.getJSON("/Shared%20Documents/CityState.txt", function(data) {
  var results = $(data);
  // Push the data into an array which is appropriate for jQueryUI autocomplete
  citiesAndStates = ($.map(results, function(item) {
    return {
      label: item.State + " - " + item.City, // Show "State - City" [e.g., MA - Boston] for selection
      value: item.City + ", " + item.State // Show "City, State" [e.g., Boston, MA] upon selection

// When the field is available and gets focus, set up the autocomplete behavior
$(document).on("focus", "input[id$='FormControl0_V1_I1_S15_I4_T5']", function() {
  if ($(this).data("autocompleteSet") === undefined) {
    $(this).data("autocompleteSet", true);

    // Don't navigate away from the field on tab when selecting an item
    .bind("keydown", function(event) {
      if (event.keyCode === $.ui.keyCode.TAB && $(this).data("ui-autocomplete") {
      source: function(request, response) {
        // Delegate back to autocomplete, but extract the last term
        response($.ui.autocomplete.filter(citiesAndStates, extractLast(request.term)));
      minLength: 3,
      focus: function() {
        // Prevent value inserted on focus
        return false;
      select: function(event, ui) {
        var terms = split(this.value);
        // Remove the current input
        // Add the selected item
        // Add placeholder to get the semicolon-and-space at the end
        this.value = terms.join("; ");
        return false;

function split(val) {
  return val.split(/;\s*/);

function extractLast(term) {
  return split(term).pop();

One note here: This approach basically violates my “put all data into a list so that end users can maintain it” rule. However, the approach I came up with was the best one for the situation. It’s still possible for a reasonably savvy (meaning “non-developer”) user to manage the data by downloading the file and using a JSON-aware editor.

The main thing was that we were able to set up something that worked, regardless what the Office365 limits may change to.

Finding the SharePoint 2007 / 2010 Thesaurus Files


If you go to TechNet to find out where the thesaurus files for search are so that you can add in your own synonyms, you may be as confused as I was earlier today. It only took me about 20 minutes to figure out, but if three or four people find this post, we’ll have saved enough time for lunch.

The TechNet article you want is Manage thesaurus files (SharePoint Server 2010), though the one for SharePoint 2007 (Edit a thesaurus file (Office SharePoint Server)) is pretty much identical.

In the article, it says

By default, SharePoint Server 2010 installs the thesaurus files for all supported languages at %ProgramFiles%\Microsoft Office Servers\14.0\Data\Office Server\Config. When a search administrator creates a Search service application, the search system automatically copies the thesaurus files from the installation location (including any thesaurus files there that an administrator has edited) to %ProgramFiles%\Microsoft Office Servers\14.0\Data\Office Server\Applications\GUID-query-0\Config, where GUID is the GUID of the new Search service application. The search system performs the same operation on every query server that is running the new Search service application. Thus there is a copy of each thesaurus file on each query server that is running that Search service application.

When I looked in %ProgramFiles%\Microsoft Office Servers\14.0\Data\Office Server\Config, well, there was no %ProgramFiles%\Microsoft Office Servers\14.0\Data\Office Server\Config. Instead, because my client had decided to change the location of the index to another drive, I have to figure out where that actually was. Here’s the trick.

In complex farms, you may have multiple Search Service Application, multiple indices, etc. but these steps should work in most cases.

  • In Central Administration, go to the Search Application -> Central Administration/ Manage service applications / Search Service Application (or whatever you called it)
  • At the bottom of the page, you’ll see a section called ‘Search Application Topology’
  • Click the Modify button and on the next screen look for the ‘Index Partition’ (you may have more than one)
  • Click on the ‘Query Component 0′ link and Edit Properties
  • The field called ‘Location of Index’ contains the root location for the thesaurus files


Looking in that folder, you should find folders that look something like this:


As noted above, the thesaurus files you want to work with are in the GUID-query-0\Config folder. In my case above, it’s E:\Data\SearchIndex\Office Server\Applications\0f78bae4-05b9-417f-b533-43326409dfcc-query-0\Config

Happy equivalency!

One side note: it boggles my mind that there is no UI to manage synonyms in the thesaurus, but there you go.

Setting a Rich Text Column in a SharePoint Form with jQuery – SharePoint 2010

I have gotten several questions on an older post of mine titled Setting a Rich Text Column in a SharePoint Form with jQuery from a guy named Travis. He’s been struggling to try to use the script in the post to get at the text in a Rich Text Editor (RTE) in SharePoint 2007.

I just took a look at an RTE in SharePoint 2010 in both IE and Firefox. The markup for it is considerably different than it was in SharePoint 2007, and looking at my older post, I can tell from the screenshots that I was in 2007. The good thing is that it looks identical in both browsers in 2010, which wasn’t usually the case in 2007.

Here’s what the column looks like in SharePoint 2010 with the same text typed into it as in the previous example:

9-11-2013 12-56-16 PM

And here’s what the markup looks like in IE10:

9-11-2013 1-04-32 PMThe script in the older post shouldn’t work in any browser, since the markup is different. Rather than a textarea, the typed text goes into a div. It was pretty easy to select the textarea in 2007 because it had its title attribute set to the DisplayName of the column. In 2010, there’s no such obvious “hook” to select on.

Instead, we have to fall back to looking for the column name in the comment which is above each column’s control on the page. To recap, each cell in the left column where the names of the columns show has the CSS class ms-formlabel applied to it. In the right column, where the editing controls are, each cell has the class ms-formbody applied to it.

To find the RTE in the page, we have to loop through all of the ms-formbody cells to find the right one. I do this in SPServices for some things as well, because SharePoint is very inconsistent when it comes to providing a good way to select form elements.

Here’s my findFormField function from SPServices:

// Finds the td which contains a form field in default forms using the comment which contains:
//  <!--  FieldName="Title"
//    FieldInternalName="Title"
//    FieldType="SPFieldText"
//  -->
// as the "anchor" to find it. Necessary because SharePoint doesn't give all field types ids or specific classes.
function findFormField(columnName) {
  var thisFormBody;
  // There's no easy way to find one of these columns; we'll look for the comment with the columnName
  var searchText = RegExp("FieldName=\"" + columnName.replace(/[-[\]{}()*+?.,\\^$|#\s]/g, "\\$&") + "\"", "gi");
  // Loop through all of the ms-formbody table cells
  $(",").each(function() {
    // Check for the right comment
    if(searchText.test($(this).html())) {
      thisFormBody = $(this);
      // Found it, so we're done
      return false;
  return thisFormBody;
} // End of function findFormField

So the script to get the HTML value of the RTE – assuming we have the function above available to us – is:

var thisFieldHtml = findFormField("System Description").find(".ms-rtestate-write div").html();

Unfortunately (fortunately?) SharePoint keeps track of where our cursor is at any given time during the editing process by maintaining two spans inside the div.

<span id="ms-rterangecursor-start"></span><span id="ms-rterangecursor-end"></span>

Here’s what it looks like in an alert when I’ve just typed “big “:

9-11-2013 1-33-09 PMIf we highlight a section of text, then that highlighted text is contained within the two spans. Here I’ve highlighted the word “over”:

<span id="ms-rterangecursor-start"></span>over<span id="ms-rterangecursor-end"></span>

So if we want to get the HTML value of the RTE without the editing spans, we need one more line of code to remove them:

var thisFieldHtml = findFormField("System Description").find(".ms-rtestate-write div");
thisFieldHtml.find("#ms-rterangecursor-start, #ms-rterangecursor-end").remove();

If we just want the text value of the RTE, then we don’t need to bother stripping out the spans:

var thisFieldText = findFormField("System Description").find(".ms-rtestate-write div").text();

Note: I’ve also tried this in SharePoint 2013. There, the artificial surrounding div which SharePoint wrapped the Rich Text in with the earlier version is not present, so we don’t need to look inside it:

var thisFieldText = findFormField("Reason for Nomination").find(".ms-rtestate-write").text();

Travis, I hope this helps!

Comparing SPServices 2013.01 Calls with Async vs. Promises Methods

SPServices 2013.01 is almost ready for release and I’m hoping that when you see the coolness here that you’ll want to get your hands on the beta to do a little testing with it.

I’ve put together a pretty simple demo page to show how great it can be to use jQuery .Deferred objects (aka promises) to retrieve data with SPServices versus the asynchronous way we’ve tended to use until now. The demo page is running on my old WSS 3.0 site hosted at FPWeb. Unfortunately, I don’t have a public-facing SharePoint 2013 instance anywhere yet.

The idea here is pretty simple. I wanted to keep the complexity of the code pretty low and also to use as much common code as possible between the two methods so that the real apples to oranges comparison is the async method versus the promises method.

I’ve set up three Announcements lists in a single site: Corporate Announcements, Finance Announcements, and HR Announcements. Each list has three announcements in it. I even got to use Bacon Ipsum to fill in some dummy content.

The demo page has two buttons on it: one for async and one for promises. When you push one of the buttons, the script loops through the array containing information about the three lists, retrieves the three items from each of the lists using the chosen method, and displays them on the page. There’s a slightly different version of the code in each of the functions (getRecentAnnouncementsAsync and getRecentAnnouncementsPromises) that run in each case.

The getRecentAnnouncementsAsync function runs the way we’re all used to using SPServices. The async option is set to false and processing of the returned data happens inside the completefunc.

In the promises version, the async option isn’t set, so it has the default of true (we don’t wait for the returned data, but continue processing). The promise returned from each call to GetListItems is put into an array, and the line $.when.apply($, announcementsPromises).done(...) is where the check for all three promises to be complete happens. When that occurs, the data for the three calls is processed.

Here’s the code:

// Show the most recent Annoucements from three lists in the current site
var announcementsPerList = 3;
var lists = [{
    name : "Corporate Announcements"
  }, {
    name : "HR Announcements"
  }, {
    name : "Finance Announcements"
var camlQuery = "<Query><OrderBy><FieldRef Name='Created' Ascending='FALSE'/></OrderBy></Query>";
var camlViewFields = "<ViewFields>" +
  "<FieldRef Name='ID' />" +
  "<FieldRef Name='Title' />" +
  "<FieldRef Name='Body' />" +
  "<FieldRef Name='Author' />" +
  "<FieldRef Name='Created' />" +
  "<FieldRef Name='FileDirRef' />" +
var out;

$(document).ready(function () {

  $("input[value='async']").click(function () {



  $("input[value='promises']").click(function () {





function getRecentAnnouncementsAsync() {

  var recentAnnouncementsContainer = $("#demo-recent-announcements");

  out = "<table>";

  for (var i = 0; i < lists.length; i++) {

      operation : "GetListItems",
      async : false,
      listName : lists[i].name,
      CAMLQuery : camlQuery,
      CAMLViewFields : camlViewFields,
      CAMLRowLimit : announcementsPerList,
      completefunc : function (xData) {

        // List header
        out += "<tr><td class='demo-section-header' colspan='99'>" + lists[i].name + "</td></tr>";

        $(xData.responseXML).SPFilterNode("z:row").each(function (itemNum) {

          processAnnouncement(i, $(this));




  out += "</table>";

function getRecentAnnouncementsPromises() {

  var recentAnnouncementsContainer = $("#demo-recent-announcements");
  var announcementsPromises = [];

  out = "<table>";

  for (var i = 0; i < lists.length; i++) {

    announcementsPromises[i] = $().SPServices({
        operation : "GetListItems",
        listName : lists[i].name,
        CAMLQuery : camlQuery,
        CAMLViewFields : camlViewFields,
        CAMLRowLimit : announcementsPerList


  // When all of the promises are fulfilled...
  $.when.apply($, announcementsPromises).done(function () {
    for (var i = 0; i < lists.length; i++) {

      // List header
      out += "<tr><td class='demo-section-header' colspan='99'>" + lists[i].name + "</td></tr>";

      $(announcementsPromises[i].responseXML).SPFilterNode("z:row").each(function (itemNum) {

        processAnnouncement(i, $(this));



    out += "</table>";



function getNow() {

  return new Date();


function announcementHeader() {

  out += "<tr class='ms-WPHeader'><td colspan='99'><h3 class='ms-standardheader ms-WPTitle'>Recent Announcements as of " + getNow() + "</h3></td></tr>";
  out += "<tr>" +
  "<th class='ms-vh2'>Title</th>" +
  "<th class='ms-vh2'>Body</th>" +
  "<th class='ms-vh2'>Created By</th>" +
  "<th class='ms-vh2'>Created</th>" +


function processAnnouncement(i, item) {

  out += "<tr>";

  // Title as a link to the announcement
  var thisLink = item.attr("ows_FileDirRef").split(";#");
  out += "<td class='ms-vb demo-note-details' style='width:40%;'>" +
  "<a href='/" + thisLink[1] + "/DispForm.aspx?ID=" + thisLink[0] + "&Source=" + location.href + "' data-announcement-id='" + item.attr("ows_ID") +
  "' data-list='" + lists[i].name + "' >" +
  item.attr("ows_Title") +
  "</a>" +

  // Body
  var thisBody = item.attr("ows_Body");
  out += "<td class='ms-vb'>" + ((typeof thisBody !== "undefined") ? thisBody : "NA") + "</td>";

  // Author as a link to the User Profile
  var thisAuthor = item.attr("ows_Author").split(";#");
  out += "<td class='ms-vb' style='width:15%;'>" +
  "<a href='/_layouts/userdisp.aspx?ID=" + thisAuthor[0] + "' onclick='GoToLink(this);return false;'>" + thisAuthor[1] + "</a>" +

  // Created date/time
  out += "<td class='ms-vb' style='width:15%;'>" + item.attr("ows_Created") + "</td>";

  out += "</tr>";


Note that I’m using the same version of SPServices here. SPServices 2013.01 can be used as you’re used to using it and everything will work as you are used to. If you are making multiple calls and the promises method would make more sense (mainly in terms of efficiency), then you can take advantage of it.

There are two big benefits of using the promises method:

  • If there are multiple calls to the Web Services that can logically happen concurrently (there’s less benefit if there is only one call), the promises method allows that to happen.
  • In the promises method, the browser doesn’t get locked up like it does with the async method. Setting async to true means that the browser is going to wait for the results to come back from the call before it will allow anything else to happen. With promises, the processing continues even when the results aren’t available. (This can take some getting used to, and I’ll undoubtedly do more posts about it.)

I’ve tested the code in SharePoint 2013 on Office365 and WSS 3.0 at FPWeb, which are about the most opposite ends of the spectrum that we can try these days. The code that I tested is *exactly* the same in the two environments. I simply copied and pasted the file from SharePoint 2013 where I started into the WSS 3.0 environment. How’s that for consistency, even on a downgrade? Oh, if Microsoft’s code only did that!

Using Firebug, I’ve captured information from the two methods on the Net tab for the XHR traffic.

Here are the results in SharePoint 2013:





As you can see the total elapsed time decreased from about 503ms to 107ms. These results are fairly consistent. I haven’t tried to build any fancy instrumentation around it, but if I click the buttons repeatedly, I get consistent results in both cases.

Here are the results from WSS 3.0:





Here, the total elapsed time decreased from about 409ms to 130ms.

In both versions of SharePoint, you can quite obviously see the difference. With the async method, the three calls happen in order, whereas with the promises method, they happen pretty much concurrently. (Even with promises, there’s a small lag due to the processing to set up the calls.)

But don’t take my word for it. go and grab your own copy of the

SPServices Stories #13: Durandal SP3: Developing SharePoint SPAs Made Easy

This entry is part 13 of 21 in the series SPServices Stories


Rainer Wittman (@RainerAtSpirit) and I have been emailing back and forth for a few weeks now. He’s been trying out the new alpha of SPServices 2013.01 which includes jQuery promises aka Deferred objects capabilities (soon to be a real release, I promise!) with the Durandal JavaScript framework. Durandal is a framework for building SPAs, or Single Page Applications.

This is something that SharePoint 2013 does to some degree with the Minimal Download Strategy (MDS). If you’re interested in how MDS works, here are some posts that go into more detail about it:

Those of you who are purists will tell me that I’m comparing apples and oranges, and that’s sort of true. However, the goals are similar: send as little of the page transitions down the wire as possible to make those transitions as fluid and responsive as possible. We see this sort of approach more and more, especially in mobile interfaces.

I’ve done SPA-like things even with SharePoint 2007 using AJAX. By maintaining the overall chrome of the page (top nav, Quick Launch, footer) as a constant, we can do something as simple as AJAXing in a new copy of the same page, parsing out the main panel, and replacing it in the user’s current copy of the page in the DOM. You also see this in SharePoint 2010 with the asynchronous refresh capability available with some Web Parts (See: Asynchronous
Update in SharePoint 2010
from @wonderlaura).

If you haven’t heard of Durandal (I hadn’t), here’s some information swiped from the Durandal home page:


We didn’t try to re-invent the wheel. Durandal is built on libs you know and love like jQuery, Knockout and RequireJS. There’s little to learn and building apps feels comfortable and familiar. Dive in and enjoy.


MVC? MVP? MVVM? Yes. Messaging, navigation, modals? Check. Durandal has the features you need to build whatever apps you can imagine; the apps of today and of tomorrow. Let your creativity soar.


Building an Android phone app? An enterprise LOB targeted at Windows? A web gaming platform? No matter how large or small the app, it’s effortless with Durandal….and we give you the tools to develop on any platform, for any platform.

I expect we’ll hear more from Rainer about this cool way of building SharePoint interfaces. As you read through his post below, you’ll probably be very impressed to see that his approach works for SharePoint 2007, 2010, or 2013. If you’re still on 2007 and woulds like to make your installation fell a heck of a lot more “modern”, this is an approach that has legs going forward. You’ll also see some links in his post to live, working demo pages so that you can get a feel for what he is driving at.

This article was originally posted on Rainer’s blog at Durandal SP3: Developing SharePoint SPAs made easy.

Durandal SP3: Developing SharePoint SPAs Made Easy

Hi there,

In the last post I introduced you to Durandal a SPA Framework, which can be used to create SPAs on top of the SharePoint infrastructure. This time we’re going to leverage Marc Anderson’s SPServices as a data service for our SPA.

The obvious advantage is that web services are around for a long time, so one SPA (same code base) runs in SharePoint (2003), 2007, 2010 and 2013.

The obvious disadvantage is that web services are deprecated in 2013, so they might go away with the next major release. If you are already running on 2010/2013 and don’t have to support older browser versions you might want looking into using REST services with JayData or Breeze instead… but that’s worth another story.

Seeing is believing so here a couple of screen shots using IE versions that roughly matches the SP release time.

SharePoint 2013 | IE 10: 2013-04-08-SP2013Demo

SharePoint 2010 | IE 8:


SharePoint 2007 | IE 7:

In SP2007 you’ll notice some issues with the CSS that is used in the SPA otherwise it’s fully functional. That is less an issue with IE7, but more with SP2007 that is running pages in Quirks mode. A quick workaround would be to apply a DOCTYPE to the pages that host your SPA.


SharePoint 2003 | IE 6:

Sorry, I’m out of historic SharePoint VMs. Please give me a shot if somebody has still access to a SP2003 environment. I’d love to add the missing screen shot.

Now, after hopefully getting you excited, here’s the bad news. The demo is using the alpha release 2013.01ALPHA5 of SPServices, so I won’t make the demo code available through Github as usual. I’ll update this post once the next official version of SPServices has been released and tested with Durandal SP3.

The good news is that there are two public available pages that allows you to go hands-on. Both are best viewed with your favorite console open.

  1. development version:
  2. optimized build:

Without going into the details let see what the Promises demo app is doing when you activate the list route (/#lists).

  1. Retrieve information about the lists in the current site via SPServices GetListCollection method (with caching)
  2. Retrieve detail information about the selected list via SPServices GetList method and build a mapping that can be used in SPServices SPXmlToJson method (with caching)
  3. Retrieve item information via SPServices GetListItems (NO caching)

Update 2013/04/11: The life demo was updated based on some feedback I got. The list overview now produces a configurable row view of lists with meta information like ItemCount and Last Modified. Step 2 and 3 are performed once you select a list.

By opening up the optimized build and filtering the network tab for XHR you can see the three POST requests to lists.asmx nicely lined up. Once you start selecting other lists, you’ll noticed that the number of XHR requests decrease as more and more cached information becomes available.


The development version on the other side shows far more details via the console. That allows you to get familiar with Durandal’s application life cycle and to inspect the Json result of some spdata methods.


After reading so far and hopefully seen the Promises demo in action by yourself, I hope that you share my excitement of the upcoming promise support in SPServices. Once it’s part of an official release SPServices will become a perfect fit for Durandal’s life cycle methods.