Dear Microsoft: Remove the 5000 Item Limit for SharePoint List Views

It’s 2016. In 1993, I used Microsoft Access to process over 1,000,000 records of data to implement a product profitability model at Staples – on an IBM 486 machine.

SharePoint-List-View-ThresholdWe’ve been saddled with a 5000 item limit for views since SharePoint 2010 launched. Oddly, in SharePoint 2007, there was no such limit. When 2010 came out, with its requirements for heavier and beefier hardware, the limit showed up.

I’ve read (well, skimmed) the white papers about why this needs to be. I’ve had long conversations with people in the SharePoint Product Group about the technical debt involved and why it is a “hard” thing to fix. I’ve read the highly visited KB articles about it, like “The number of items in this list exceeds the list view threshold, which is 5000 items” error when you view a SharePoint Online list in Office 365 Enterprise.

Bill Baer (@williambaer) has posted some promising messaging about the thresholds (See: Navigating List View Thresholds in SharePoint Server 2016 IT Preview), but I still don’t see any outward evidence that the limit is going away anytime in the foreseeable future.

I think there have been mixed messages and misinterpreted messages (no blame to Bill). The auto indexing idea which was recently added to Office 365 and comes in SharePoint 2016 (I believe – but I’m not positive) was misconstrued as lifting the 5000 item limit, which it definitely doesn’t.

The 5000 item limit in the UI for lists and libraries is arguably a good thing. There is absolutely NO reason to show more than 5000 items on a view page. Ever. No one can digest that amount of data or make use of it. Views should show just enough information for people to make good decisions about what to do next. (This is a very common mistake in SharePoint information architecture.) There are times when we simply need to have a view which derives from 5000+ items, though. Remember what year it is; 5000 items is a speck, a mote, an iota of data in this Big Data era.

No 5000 item limit

When it comes to calling SharePoint’s APIs, the 5000 item limit simply has to go. It’s a crazy limit in this day and age. I don’t care about all the technical debt talk about it. The SharePoint Product Group should be able to slide a solution underneath, at least on Office 365 – but ideally in on premises versions of SharePoint as well.

That’s the whole point of APIs: you can fix stuff under the hood without breaking any “contracts”. As more and more processing moves off-SharePoint, the 5000 limit is stifling. It’s been a problem for me for years because I live on the client side. I don’t always need all 5000+ items, but I need to know stuff about them: how many items match some criteria, what the sales total is for 12,834 items, how many items fall into a set of groupings, etc.

Whatever approach the Product Group takes shouldn’t matter, as long as they uphold the API contracts. For years they have told us – very rightly – that touching the underlying SQL tables puts us in an unsupported state. As long as we’re calling the APIs, what happens under the covers doesn’t matter. In fact, most of us should care less; that’s Microsoft’s domain and we should leave it to them.

With the new SharePoint Framework (SPX?) coming along – which I think is a TREMENDOUSLY good thing – the 5000 item limit is going to be on the minds of every developer who works with SharePoint. There will be no more server side tricks to haul out to cheat your way around this stuff. Those tricks have been reduced in degrees anyway. If SharePoint is to truly be treated as a service (as I firmly believe it should) then it needs to behave like a service in 2016+, not a service in the 1990s – or earlier.

I’ve heard many people – my fellow MVPs included – say that Microsoft will never fix this. There’s no cool Marketing moment in it, no splash they can make at a conference, little to show in the UI for it. But think of the consequences if they DON’T fix it. How long can we continue to believe that we can pump millions of items into a list (which is the true capacity) only to be told that we can only retrieve fewer than 5000 of them from the client side?

Unfortunately, if we assume they won’t fix it or can’t do it right, then the SPX may be doomed, IMO. I’d prefer to believe that they know it has to be fixed and are working on it. As my friend Jeff Shuey (@shuey) is always saying “I want to believe!”


If you want Microsoft to hear your vote on this idea, check out any or all of the feedback below ion the SharePoint UserVoice site:

  1. Remove 5000 item limit
  2. Provide a list limit over 5000
  3. Remove the list view threshold (5000 by default)
  4. Default notification for lists and library’s hitting the 5000 items limit
  5. Remove the limit of 5000 items as result in a view
  6. Allow new library look for libraries over 5000 items (provided the structure in managed correctly)<

Getting Up and Running with Power BI and SharePoint Lists

Power BI

A few weeks back when I was at the Digital Workplace Conference in Melbourne – a most excellent conference – I saw Adam Cogan (@adamcogan) demonstrate Power BI in his session Improve Your Business Intelligence with Power BI. I’d seen Power BI before a few times, but the way Adam showed it, it finally “clicked” for me. I may be hooked, and I *know* I’m going to be able to do some amazing things for clients with it.

I had to try it out. I have a client where I think Power BI could be an excellent part of the over all solution, so I tried a few things out in their tenant. The client is a pharma startup and we’ve built a site where they can log all of their experimental data. There’s plenty of data stored across a set of lists and libraries we built to match their scientific processes. The main storage location is a SharePoint Document Library made up of hundreds of Document Sets.

If I tried to connect to the SharePoint Document Library using the “SharePoint Online List” connection, I end up with this as the query:

let
  Source = SharePoint.Tables("https://tenant.sharepoint.com/sites/siteName", [ApiVersion = 15]),
  #"57cb07a8-e7aa-4401-a778-699941bfe12b" = Source{[Id="57cb07a8-e7aa-4401-a778-699941bfe12b"]}[Items]
in
  #"57cb07a8-e7aa-4401-a778-699941bfe12b"

which gave me the following error (DataSource.Error):

DataSource.Error

Trolling the Power BI community forums, I came across the suggestion to switch from [ApiVersion = 15] to [ApiVersion = 14]. When I made that switch, the error changed, but still no joy, as I got this error (Expression.Error):

Expression.Error

I figured maybe since I was working with a Document Library instead of a list that was part of the problem. (It didn’t really make sense to me that it would, but…) I figured I’d try an OData call to the REST endpoint instead.  When I connected to the Document Library as an OData source without specifying anything for the $select, the query ended up looking like this:

let
  Source = OData.Feed("https://tenant.sharepoint.com/sites/siteName/_api/web/lists/getbytitle('List Name')/items")
in
  Source

…and I got the same error (DataSource.Error) as above.

DataSource.Error

The column in question here is a multi-select choice column and it became clear that Power BI doesn’t seem to like multi-select columns. I verified this in a conversation with my buddy John White (@diverdown1964). John is a fellow MVP and one of the best BI experts out there. I figured if he didn’t know how this worked, no one would. (Follow John’s Blog ‘The White Pages‘ for all sorts of BI goodness – and more.)

As John and I were talking, it occurred to me that I could just request the columns I really needed in the REST call. Not only might it solve the problem, it would certainly be more efficient. I’ve learned to only ask for the columns I need when I’m making Web Services calls in general.

When I added the $select clause and just asked for the Title column:

let
  Source = OData.Feed("https://tenant.sharepoint.com/sites/siteName/_api/web/lists/getbytitle('List Name')/items?$select=Title")
in
  Source

Joy! I saw the data in Power BI just fine.

Just the Title

So I think the moral of the story is to build a REST call to only retrieve the data you need, excluding any multi-select columns right up front. As I mentioned, the problem column was a multi-select choice, so there was no option there. I simply couldn’t use that column in my analysis. Luckily that column didn’t really matter to us – yet.

I had better luck with lookup columns – as long as they weren’t multi-select. By using $expand on those columns, I could retrieve the values from the associated lookup list.

Finally, since we only get 100 items back from a REST call by default, I added $top=5000 so that I could retrieve the maximum allowable number of items per request.

Unfortunately, there are some drawbacks to using Power BI right now, and the team will need to solve these for people to really start using it to its full potential in the SharePoint / Office 365 space. Multi-select columns are pretty common in SharePoint lists, and Power BI needs to handle them more gracefully, if not actually use them for analysis. The error messages I got were certainly misleading, and the suggestions about how to fix them were interesting, but not effective in the end.

And it’s very yellow.

SharePoint Framework – Initial Questions and Answers

The Future of SharePoint - May 4It’s been almost a week since the #FutureOfSharePoint event in San Francisco, and as usual, there are a lot of questions flying around. Wictor Wilen has started a post SharePoint Framework – Initial Questions and Answers and I thought I’d start my own. I’m not going to rehash Wictor’s answers, and will instead focus on the questions that I’m getting. Some questions have come to me publicly (perhaps as comments on my previous post Microsoft’s May 4 SharePoint Announcements and Client Side Development Future), some privately, or I’ve seen them in private forums. I’ll keep the latter two anonymous, of course.

As with Wictor’s post, this is entirely unofficial and will contain some of my opinions, as well as some things that may turn out to be rumors. I have no intention of misleading anyone, but a lot of things are in flux as the Product Group is working to finalize the first release of the SharePoint Framework (SPX). I also have no intention of pandering to Microsoft here. Some of the answers may not be exactly what we’d like and I’ll say so if I feel that’s the case. No crying and try to keep the teeth-gnashing to a minimum.

If you have questions, feel free to add them in the comments here (or on Wictor’s post if you’d like his take). I’ll add new FAQs as they seem useful.

Is the SharePoint Framework “Yet Another Development Model”?

I don’t see it as such. The SharePoint Framework is a new development option. Nothing else has been deprecated and the SPX doesn’t replace anything per se. There are times when it will be the right way to build things and other times when the other development models are more appropriate.

Can’t I just do this stuff in a Content Editor Web Part?

Absolutely, and many of us have been doing so for years. (I’ve been building things with KnockoutJS and AngularJS, even in SharePoint 2007!) Each of us has our own way of making this all work.

What the SPX will give us that’s new and good is:

  • Isolation between client side Web Parts. We’ll be able to run different frameworks on the same page without any cross- Web Part “pollution”.
  • We’ll all be using the same approach and we can improve upon it as a community.
  • The SharePoint Product Group is going to use the SPX to build their “experiences”. That means they will feel the same pain we do if it doesn’t work well; no more pulling rabbits out of hats for them by using secret tricks.

What versions of SharePoint will get the SharePoint Framework?

As with most SharePoint improvements and enhancements, we’ll see the SharePoint Framework first in Office 365. The plans don’t seem too firmed up yet, but I would expect that we’ll see the SPX in a SharePoint 2016 feature pack not long after it shows up in Office 365. I’m not sure about the SharePoint 2013 story, but there may well be one.

Won’t this just be replaced by another model in a few years?

Everything is replaced sooner or later. However, the SharePoint Framework will bring SharePoint development into the mainstream, allowing us to use the tools most Web developers have been using for years now. As goes the Web, so shall we go. We can “chase the shiny penny” of new tools and the SPX ought to support us in doing that, within reason. I expect the SPX will evolve rather than being replaced.

What do I need to do to get up to speed?

JavaScript. If you learn JavaScript, you’ll be laying the foundation of using the SharePoint Framework. No, JavaScript is not a baby tool, or a joke, or whatever other disparaging thing you’ve heard. People outside the SharePoint world have know that for years now. Get over it. See my comments in this video a few of us MVPs made up in Montreal at Sharegate: MVP Thoughts: What Should SharePoint Developers Focus on.

What about jsLink?

(See Marcel’s comment below for the question) I don’t recall hearing anything specific about this at the Dev Kitchen, but I may have. To me, jsLink and some of the other client side rendering (CSR) techniques were almost stop-gap measures. They moved people closer to client side development, but did it in often clunky ways. (Try editing JavaScript that has to be in a comment – no fun!) Using jsLink will still be a valid approach with the existing .NET style Web Parts, but I expect many people will decide to go all the way to the client side, calling SharePoint’s Web Services themselves instead. It gives you more control over time, and will become de rigeur, IMO.

SharePoint’s Future?

Ray Ban Shades

Microsoft’s May 4 SharePoint Announcements and Client Side Development Futures

Bill Gates 1981

Image source: http://www.biography.com/news/bill-gates-biography-facts

In the beginning, there was Bill, and Bill was good. Bill revolutionized the concept of computing (along with a handful of others). But somewhere along the way, something happened. It’s not clear exactly what. Maybe it was the disaster of Vista. Maybe it was the reign of Ballmer. What it was exactly doesn’t really matter anymore.

Microsoft had become a monolithic, unresponsive thing, with few ways to penetrate its outer ranks. We felt like the chimps at the monolith; we had little control. We didn’t know what Microsoft was anymore.

Image source: http://www.salon.com/writer/david_mcraney/

Image source: http://www.salon.com/writer/david_mcraney/

 

Instead of listening to its customers, Microsoft responded with reasons why they knew best, why they would take care of us, even if it didn’t feel like they were taking care of us at all. When we asked for things, we didn’t get them.

I'm sorry, Dave. I'm afraid can't do that.

I’m sorry, Dave. I’m afraid can’t do that. Image source: https://i.ytimg.com/vi/qDrDUmuUBTo/maxresdefault.jpg

We no longer felt a connection with Microsoft. They weren’t watching our back; they weren’t listening to us; they didn’t seem to know what we wanted.

Then something changed. It changed very quickly. None of us really understood what was changing, but we knew it was good.

2001 Stargate

Image source: http://www.sci-fi-o-rama.com/2008/12/11/2001-a-space-odyssey-2/

Fast forward to today: May 4, 2016. Today’s announcements about SharePoint feel like the culmination of several years of recovery. It feels like things have come around again. Even an old cynic like me can be a Microsoft fan now without regretting it half the time.

Back to the Future

Image source: http://www.pcadvisor.co.uk/how-to/photo-video/how-watch-back-future-ii-on-21-october-2015-at-cinema-trilogy-anniversary-30th-today-3627401/

Today Jeff Teper and his crew have both turned back time and looked forward to show us a wide vision of the new Microsoft and the #FutureOfSharePoint. It feels fresh; it feels right; it feels like we’re taking a wild ride together.

What were the big stories coming out of today’s event (which fell on a date with a science fiction theme from some other movie)?

Well, to me, the bullet points are:

  • SharePoint is alive and kicking – working out for a long marathon run. More than 200,000 organizations use SharePoint today, and an extraordinary community of more than 50,000 partners and 1 million developers make up a $10 billion solutions ecosystem around SharePoint.
  • The heart of SharePoint is the documents that make our work work. But rather than looking for the files you need, those files will find you. Almost any device – any time.
  • The modern intranet has evolved, and Microsoft is out ahead of it. The new mobile SharePoint app, the new SharePoint home page, and new modern team sites may well be ahead of the market. Many organizations will take years to catch up to what Microsoft has on offer.
  • SharePoint 2016 went to General Availability today, meaning it’s available in most sales channels.  That in itself is news but not unexpected. We heard more today about what SharePoint 2016 really is: a springboard to the future.
  • Security, security, security – This stuff fails to excite me, though I know it is exceedingly important to many.

The SharePoint Framework

It probably won’t surprise you to hear the single most exciting thing to me about today’s announcements is the new SharePoint Framework. One of the best things about this new development option is that it’s additive: it doesn’t replace anything or make anything obsolete. If you’re a server side developer, you’re not losing anything. If you’ve invested in the Add-In model, you can still make hay with that. But if you’re like me and have seen how your clients’ or customers’ faces light up when they see well-built client side functionality, then you can walk with pride now. What the new SharePoint Framework does is elevate JavaScript development on top of SharePoint to a first class citizen.

I was ecstatic to see an early iteration of the SharePoint Framework early this year at a Developer Kitchen (DevKit to insiders) in Redmond. What we put our hands on and built things with was the culmination of a journey I’ve had a lot of fun making. The ability to use JavaScript on top of SharePoint has always been there. What’s happened is that the general view of that approach has gone from ridicule to cautious interest to outright excitement. The SharePoint Framework is the right next step.

At the DevKit, senior members of the SharePoint Product Group – like Vesa Juvonen, Chakkaradeep “Chaks” Chandran, Luca Bandinelli, Dan Kogan, and many more –  worked side by side with us to understand the warts of the new approach, taking away notes on ways to fix it. The DevKit was no holds barred – when they didn’t have an answer, they said so; when they wanted to see if we had better ideas, they asked. They didn’t have all the answers then, and they probably don’t now. I actually think that’s a great thing for a few reasons:

  1. They realize that the Framework will evolve, and they want it to evolve based on our input and feature requests.
  2. Monolithic thinking is gone. The Product Group will be adding new bits and bobs fast.
  3. The Product Group will be using the SharePoint Framework to build out new “experiences” themselves. No more “secret” tools like in the past – they will feel the pain of any issues right along with us.

    In fact, we have built the new experiences for our new mobile app, SharePoint Online and OneDrive for Business, including the new document library and list experiences, using the SharePoint Framework.

  4. The Framework is framework-agnostic. It’s designed to support today’s hot development frameworks like KnockoutJS, AngularJS, and ReactJS. Because it is agnostic, the next shiny new framework that comes along should work just fine, too.

This is going to be the future of SharePoint, without abandoning the past. What it does is legitimize client side development to a level that hasn’t been possible before. We’ll have enterprise-grade tooling to build client side Web Parts and full scale solutions we’ve never had before. Even if you’ve been doing client side development already and have a reliable set of methods (as I do), you’ll appreciate the new focus and will be able to take advantage of it gradually.

The-SharePoint-framework-an-open-and-connected-platform-2

We’ll have a page and part model that allows us to “take over” as much as we need. I expect many of you will simply build new client side Web Parts. Others will build full Single Page Applications using the SharePoint Framework. We’ll have several app “shapes” we can use to build even more robust solutions.

The-SharePoint-framework-an-open-and-connected-platform-3

As I said, we get new tooling, including a new client side SharePoint Workbench to test our work. The SharePoint Framework will come out of the gate with many of the features we need, with more coming all the time. Remember, the SharePoint Product Group has committed to use the SharePoint Framework to build new features into SharePoint. If it doesn’t work for them – and thus us – they will fix it. We’ve never had that level of development parity before.

We get a canvas that is an evolution of the existing Web Part Zone. Within that canvas, we can take over the entire thing, or just parts of it. (You’ll recognize the canvas from the Delve Blog experience.) Microsoft takes care of the chrome and the services we need to make the components on the canvas sing, including the Data Broker, Caching, Authentication, and Telemetry. That’s right, we can even tap into the same telemetry engine that Microsoft uses so that we can understand how our solutions are being used. We even get responsive design right out of the box from the canvas. We need to be sure to build our stuff correctly to take care of that responsive nature, but we don’t need to set up extra stuff for that responsiveness.

We get a new property panel that’s the evolution of the Tool Pane in existing Web Parts. We can add our own properties and functionality to the property panel based on what we are building – all with commonly known client side tools.

Speaking of tools, there are some pieces of this that many current SharePoint client side devs probably aren’t using. There WILL be a learning curve, but it will be worth it. People who already know how to do Web Development – separate from SharePoint – will probably know at least some of these tools; others are new:

  • Yeoman for initial application setup
  • Visual Studio Code (you can probably insert your favorite IDE here at some point) and later Visual Studio
  • NodeJS to give us a local JavaScript development environment
  • NPM to grab existing open source packages
  • TypeScript to bring real typing to the JavaScript experience. We can also use plain old JavaScript if we’re more comfortable there. (I find that C# developers like TypeScript because it gives them the comfort of “true” classes.)
  • Gulp to build our applications, package them, and deploy them to our own CDNs
  • SharePoint Workbench for testing

SharePoint Framework Tool Chain

From my experience at the DevKit, some of this is still a little rough around the edges, but I expect that it’s becoming more polished on a daily basis. Remember, this isn’t that unfriendly Microsoft – they are shipping improvements weekly based on our input and requests. We’re going to be on this ride together, and it’s going to be really fun.

There’s plenty more to read about the SharePoint Framework and more in the official posts from today’s event on the Office Blog:

As Jeff said in one of the posts coming out of today’s event: The future starts today.

Roads? Where we're going, we don't need roads

Image source: http://pixel.nymag.com/imgs/daily/vulture/2014/12/22/back-to-the-future/22-back-to-the-future-001.nocrop.w529.h316.jpg

Making Your REST Calls Simpler by Changing the Metadata Setting

When you talk to SharePoint using REST, you have some options about what the data you get or send looks like. Early on, when REST first arrived on the scene for SharePoint, we HAD to specify odata=verbose. This meant that we had to be very specific about what our data was going to look like, especially when it came to updates.

If you don’t use odata=verbose, then you’re telling the server to accept something different. You’re basically telling the server what “dialect” of odata you are choosing to speak for the current transaction.

If you look at this post, you’ll see the options:

JSON Light support in REST SharePoint API released

Just to recap, the options are:

accept: application/json; odata=verbose

This is probably what you’re used to, and requires the heaviest payload. Using this option, you’ll get the maximum amount of information about the data coming your way, and you’ll also have to send more as well.

accept: application/json; odata=minimalmetadata

This is the middle ground. You’ll get some metdata, and you’ll also need to send some, but it’s less.

accept: application/json; odata=nometadata

This option means we won’t get any metadata and we also don’t have to send any.

accept: application/json

If you don’t specify the odata setting at all, it will default to odata=minimalmetadata.

You can use these settings both on the inbound and outbound part of your REST calls.

Content-Type: "accept: application/json; odata=minimalmetadata"

means that you are sending data that contains minimal metadata.

Likewise,

Accept: "accept: application/json; odata=verbose"

means that you want to receive data with full metadata information.

In many cases, you’ll want to use odata=verbose as you’re debugging and switch to odata=nometadata once you move into production. The metadata tells you a lot about the data coming and going and can be helpful as you build up your calls. If you don’t make that switch, there will be more data going down the wire, though in many cases that doesn’t matter too much. Since I learned to code back when nibbles were expensive storage, I tend to want to reduce data size as much as I can, though.

Unfortunately, I don’t think many SharePoint developers realize what all this can do for you. I hear people say all the time that REST calls are too “chatty”. In many cases, that’s simply not true; you developers are making things too chatty!

This allows you to switch from something like:

var data = {
  "__metadata": {
    "type": "SP.Data.FC_x0020_RunsListItem"
  },
  "Title": run.RunID,
  "RunDate": run.RunDate,
  "OperatorId": run.Operator.Id,
  "DataIDs": dataIds.join(","),
  "FCData": angular.toJson(run.samples)
};

var request = {
  method: 'POST',
  url: url,
  data: JSON.stringify(data),
  headers: {
    "Accept": "application/json; odata=verbose",
    "content-type": "application/json;odata=verbose",
    "X-RequestDigest": document.getElementById("__REQUESTDIGEST").value,
    "X-HTTP-Method": method,
    "IF-MATCH": "*"
  }
};

to:

var data = {
  "Title": run.RunID,
  "RunDate": run.RunDate,
  "OperatorId": run.Operator.Id,
  "DataIDs": dataIds.join(","),
  "FCData": angular.toJson(run.samples)
};

var request = {
  method: 'POST',
  url: url,
  data: JSON.stringify(data),
  headers: {
    "Accept": "application/json",
    "content-type": "application/json;odata=nometadata",
    "X-RequestDigest": document.getElementById("__REQUESTDIGEST").value,
    "X-HTTP-Method": method,
    "IF-MATCH": "*"
  }
};

This is a real example from some of my code in a client project. It may not look like a huge savings, but if you are passing a lot of data either way, it can make a difference.


UPDATE on 3 May: Tip from Mikael Svenson (@mikaelsvenson) – If you’re running on premises SharePoint 2013, you may need to enable the multiple metadata formats for JSON. See: