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.

Adding JSON Capability to SPServices

JSON Card -- Front

Image by superfluity via Flickr

For quite a while, I’ve gotten requests to offer some sort of JSON conversion capability in SPServices. The requests have often been fairly non-specific, meaning that they haven’t mentioned a particular Web Service operation, like GetListItems or GetWeb. However, I think that most people want to be able to get convert the XML they get back from GetListItems to JSON. This would be useful because a lot of the other jQuery plugins out there want JSON to work with.

I’ve written the beginnings of a function I will include in the next release of SPServices, and I wanted to offer it up for suggestions and requests. I want this converter function, which I’ve called SPXmlToJson, to be generally useful within an SPServices context. I don’t want to build a general-purpose converter; there are quite a few good ones out there. Generally, the data returned from SharePoint’s SOAP Web Services operations is fairly “flat”. By “flat”, I mean that there isn’t a huge amount of nesting. For example, GetListItems (which I expect to be the main context for using this function) simple passes back a z:row element for each item in the list with all of the column values as attributes.

Here’s an example:

<GetListItemsResponse xmlns="">
    <listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
      <rs:data ItemCount="22">
        <z:row ows_DocIcon="gif" ows_FileSizeDisplay="21530" ows_LinkFilename="cascaded_multiselect.GIF" ows_Title="3" ows_Modified="2011-07-05 12:17:54" ows_Editor="3;#Marc D Anderson" ows_Edit="0" ows_TestColumn="bar" ows__ModerationStatus="3" ows__Level="255" ows_ID="58" ows_owshiddenversion="5" ows_UniqueId="58;#{DF636FD7-2B27-4826-9E44-33E1F68F7EC4}" ows_FSObjType="58;#0" ows_Created_x0020_Date="58;#2011-04-13 00:35:51" ows_ProgId="58;#" ows_FileLeafRef="58;#cascaded_multiselect.GIF" ows_CheckoutUser="3;#Marc D Anderson" ows_FileRef="58;#Intranet/cascaded_multiselect.GIF" ows_MetaInfo="58;#vti_parserversion:SR| vti_lmt:SW|Thu, 22 Oct 2009 03:42:52 GMT Order:DW|5800.00000000000 TestColumn:SW|bar vti_author:SR|SERVER\\username vti_lastwidth:IX|641 vti_winfileattribs:SW|00000000 vti_modifiedby:SR|SERVER\\username ContentTypeId:SW|0x01010077FE74FF93862D419170F4A09DD0BBC5 vti_ct:SW|Thu, 22 Oct 2009 03:42:52 GMT vti_lat:SW|Wed, 13 Apr 2011 04:34:58 GMT ContentType:SW|Document vti_title:SW|3 vti_sourcecontrolmultiuserchkoutby:VR|FSERVER\\\\username vti_lastheight:IX|286 " ows_Last_x0020_Modified="58;#2011-07-05 13:10:22"></z:row>
        <z:row ows_DocIcon="rtf" ows_FileSizeDisplay="1322" ows_LinkFilename="ChatLog.rtf" ows_Title="4" ows_Modified="2011-07-05 12:17:55" ows_Editor="3;#Marc D Anderson" ows_Edit="0" ows_TestColumn="bar" ows__ModerationStatus="3" ows__Level="255" ows_ID="59" ows_owshiddenversion="5" ows_UniqueId="59;#{7C8C1F98-E56C-4B8C-9BFB-50A4948BDC7D}" ows_FSObjType="59;#0" ows_Created_x0020_Date="59;#2011-04-13 00:35:52" ows_ProgId="59;#" ows_FileLeafRef="59;#ChatLog.rtf" ows_CheckoutUser="3;#Marc D Anderson" ows_FileRef="59;#Intranet/ChatLog.rtf" ows_MetaInfo="59;#vti_parserversion:SR| vti_lmt:SW|Mon, 11 Apr 2011 17:09:08 GMT Order:DW|5900.00000000000 ContentTypeId:SW|0x01010077FE74FF93862D419170F4A09DD0BBC5 vti_ct:SW|Mon, 11 Apr 2011 17:09:08 GMT vti_lat:SW|Wed, 13 Apr 2011 04:34:59 GMT ContentType:SW|Document TestColumn:SW|bar vti_sourcecontrolmultiuserchkoutby:VR|SERVER\\\\username vti_title:SW|4 vti_author:SR|SERVER\\username vti_winfileattribs:SW|00000000 vti_modifiedby:SR|SERVER\\username " ows_Last_x0020_Modified="59;#2011-07-05 13:10:22"></z:row>

Once you get past the enclosing “wrapper”, it’s very flat data: just a single element per item. Most of the other operations return similar structures (thought there’s precious little consistency). What I am thinking of is having the SPXmlToJson function accept a flat nodeset, which it will convert and return a JSON object. This ought to provide a very basic conversion capability that I can build on over time, as needed.

Here’s the function as it stands, and I’ve also made it available on the SPServices site in the latest alpha for v0.7.1. Please let me know what you think. Will this be enough? If not, what other options would you like to have available? What other operations are you likely to use SPXmlToJson with? Try it out and let me know your ideas.

Thanks for Paul Tavares and others for the help and nudging to get me to this point.

// This function converts a nodeset to JSON
$.fn.SPServices.SPXmlToJson = function(options) {

  var opt = $.extend({}, {
  ns: ""    // A flat XML nodeset (as from GetListItems)
  }, options);

  var json = [];

  opt.ns.each(function() {
  var row = {};
  var rowAttrs = this.attributes;
  for (var e=0; e < rowAttrs.length; e++) {
    var thisNodeName = rowAttrs[e].name;
    var trimmedNodeName = thisNodeName.indexOf("ows_") !== -1 ? thisNodeName.substring(4) : thisNodeName;
    row[trimmedNodeName] = rowAttrs[e].value;
  return json;
}; // End $.fn.SPServices.SPXmlToJson
Enhanced by Zemanta

SPServices, XML, JSON, and REST–Thoughts?

Over the last few months, I’ve had a couple of requests for SPServices to emit JavaScript Object Notation (JSON) in addition to Extensible Markup Language (XML). This is probably a natural progression, as SharePoint developers are becoming more familiar with the JSON approach, which IMO has been traditionally known far better by Web developers.

In my simplistic view, JSON and XML are simply different notation methods for passing data from one place to another. One doesn’t seem grandly better or worse than the other, though there are some key differences. These are the differences as I see them with no research on the concepts. (I sometimes like to keep my mind free of the conventional wisdom, as it can clutter the simplicities of the topic.)

  • XML has far more contextual padding. Every data value in XML has the familiar <wrapper>around it</wrapper>.
  • JSON is lighter, in that it only has delimiters like {}, commas, and [].
  • JSON is more “modern”, meaning newer. That alone is often enough of a driver for people to switch, though it’s hardly a good reason other than for resume build-out.
  • Both methods must deal with characters which have other meanings in an HTTP request. Those characters are delimited so that they can be passed without being interpreted with their reserved meanings.
  • To use either method, the developer must understand the internal structure of the data. While both have implied structure, it’s no different than the old client-server or EDI days: you must understand what you are getting in those containers in order to make sense of it.

Both methods have their plusses and minuses, but to me it’s simply another religious debate like PCs vs. MACs. Both get the job done, and there are times when one may have an advantage (however slight) over the other. I’m absolutely certain that there are volumes of rants pushing in either direction all over the InterWebs.

When I started working on SPServices, SharePoint 2010 wasn’t yet generally available, though I think that there was probably a CTP or something out there; I just didn’t have access to it. Because of that, and because JSON wasn’t yet as predominant as it is now (boy, things can change fast!), I simply focused on getting the XML out and in.

SharePoint 2007’s Web Services (.asmx) only emit XML. You pass a Simple Object Access Protocol (SOAP) request to the Web Service and the Web Service passes XML back. QED. SharePoint 2010 has added the Client Object Model (Client OM), which allows you to retrieve data in the JSON format via those RESTFul services (.svc).

Adding an XML to JSON translation service to SPServices is certainly an appealing idea. I’ve balked a bit, figuring that anyone who knew enough to ask for it also knew enough to find a plugin to convert XML to JSON or to build one. And then maybe they’d even contribute it to SPServices (wink, wink). Also, SharePoint developers (most of whom are .NET developers) may not be as familiar with JSON as XML, though clearly that is changing.

Another tenet I’ve tried to live by with SPServices is to not have *any* reliance on other plugins besides the jQuery library itself. This makes maintenance easier, if nothing else. That’s why I’ve avoided trying or recommending any other plugins which might emit JSON or building any ties to them.

Remember that I cannot tell the SharePoint Web Services what to pass: they pass XML. What I *could* do is add a translation facility, which would obviously add overhead to the process. This definitely would be a performance hit; there’s no way around that.

So, if you are an SPServices user, what are your thoughts on XML vs. JSON? Can you give any concrete examples of why you *really, *really* need JSON?