Predictive Indexing Comes to Office 365 Lists and Libraries

The 5000-item view threshold in SharePoint lists and libraries has been a bane for many people for a long time. I’ve been very vocal about it for the community (as well as for my own selfish reasons – I have client work to do!). I have a plethora of posts tagged with 5000 item limit, most of which are aimed to help us work around it, though I admit there is a fair amount of just plain complaining, too.

SharePoint Online is finally coming into the modern age, with Microsoft firmly attacking the 5000 item limit in several new ways. In the Microsoft Ignite session Harness collective knowledge with SharePoint and OneDrive content services (ECM), Chris McNulty (@cmcnulty2000) and Ian Story (@storyid) talked through how predictive indexing will improve our overall SharePoint experience.

We’ve all seen the rhetoric here: for years lists and libraries have been able to contain up to 30 million items, yet in too many cases we see errors when we have 5000 or more items in a view. In the “modern” experience, that may mean a #sadicecreamcone.

Or a #sadflattire.

By using predictive indexing – basically adding indices as the platform sees you will need them in addition to any you add manually – the real limits of list views become much larger.

Predictive indexing rolled out in advance of Ignite, so you may already have noticed some improvements when you work with lists with over 5000 items – as long as you are in the “modern” experience. As you add grouping to views or slice and dice list content using the column headers, SharePoint may be adding indices behind the scenes for you. You can see this happening if you look at the list settings and go to Indexed columns. There, you may see that some indices have been added by the platform, not by you.

One of the side benefits of predictive indexing – which to many will feel like a main benefit – is the the Filters Pane is now “smarter”, too. As those indices are added, the platform realizes that those columns may be things you’d like to see in the Filters Pane.

So are we all set now? Well, predictive indexing isn’t a total panacea, but should reduce the number of throttling events in many real-world situations. We still have to consider the 5000 item limit in several places, unfortunately. And taking proactive steps with our own indices is never a bad idea.

First, predictive indexing doesn’t change the threshold for REST calls. If we want to get more than 5000 items from a list or library via REST, we still need to page through them or come up with come other fancy caching scheme. I’d love to be able to tell a better story than “the 5000 item limit is still in effect for REST calls”. It’s still the case that – as has been the case for a long time now – if we use indexed columns as our first filters, we can get past the first 5000 items. The first filter still needs to bring the count under 5000 items for us to avoid an error, and we can’t retrieve more than 5000 items.

If you create a list or library and immediately dump more than 20,000 items into it, you’ll run into some of the same issues we used to have at 5000 items. We can create indices on lists with up to 20,000 items, but after that we run into the same issue we used to have with 5000 items: we can’t add a new index without getting below the 20,000 item number.

This also means Microsoft Flows or PowerApps may run into the 5000 item limit, depending on what you are doing (making REST calls yourself, for sure), as they use REST endpoints in the Microsoft Graph to retrieve data. The average power user building a Powerapp will see batches of 100 (but may never realize it). Someone taking things further (like reading from another list via a connector) might see batches of 500. In other words, if you are working with large lists, you need to understand the concept of paging on some level.

So what’s the upshot here? Well, primarily that Microsoft is working hard to make the constraints on large lists go away, or at least set the limitations to much higher levels. From an end user standpoint, this is Very Good News. From a developer standpoint, I think we should view it as a work in progress. Overall, though, it shows that Microsoft knows this is a weakness in the product and they are working hard to fix it.

I’m VERY happy to see this progress, and based on conversations with people like Chris and others in the Product Group I expect to see continuous improvement. Don’t worry – I’ll keep pushing them on it mercilessly, though!

Advertisements

Column Formatting in SharePoint Online Rolling Out!

Every once in a while, something comes to Office 365 where no one has anything negative to say. (Is that a back-handed complement?)

The fact that this tweet got 22 retweets and 34 likes (as of this writing) and lots of eyeballs means this is one of those things.

Over the years we’ve had several ways to change the look of a column in a list view. Early on – say in the SharePoint 2007 and 2010 days – we often used jQuery in the page to do what we needed. This was WAY before it was fashionable to write client side code for SharePoint. It worked just fine, but had pretty decent barriers to entry. You had to know how to write JavaScript, get it into the page, use CSS3-like selectors in jQuery, etc.

In SharePoint 2013, we got something called jsLink, which was part of what most people refer to as Client Side Rendering, or CSR. This was a pretty good mechanism, and also used JavaScript, but with some fairly arcane functions and structure. However, it allowed us to customize both individual fields and entire rows.

Announced at Ignite, we now are getting a new capability to format columns in “modern” list views. It doesn’t seem to have a snazzy name other than “Column Formatting“, but that doesn’t matter.

There’s a great page with documentation and examples which we can tweak to get what we need: Use column formatting to customize SharePoint. This new mechanisms use what’s called declarative customization, meaning we don’t actually write code – though it may feel like code to many of you. Rather than code, we write JSON, which stands for JavaScript Object Notation. JSON is basically a way to store data.

Let’s look at one of the examples from that page.

Say we want to color a column’s value if that value is less than 70. (The text on the page says color it red, but the little image is yellow, so I’m not sure they have it right at the moment – especially since the number is exactly 70.)

In any case, the JSON data below says the following:

If @currentField < 70 Then add the CSS class = sp-field-severity–warning.

which gives us something like this (with the above caveat):

{
   "elmType": "div",
   "txtContent": "@currentField",
   "attributes": {
      "class": {
         "operator": "?",
         "operands": [
            {
               "operator": "<",
               "operands": [
                  "@currentField",
                  70
               ]
            },
            "sp-field-severity--warning",
            ""
         ]
      }
   }
}

While the techies will tell you this isn’t code, it really sort of is. The condition we want to apply is stored as JSON – which is data – but tells SharePoint how to render the field. The fact that we use operators, operands, etc. to make this happen means that we are effectively writing code. That said, I expect we will see a great number of “recipes” arise out in the community, just like we did with the great SPCSR repo in Github. In fact, I challenge all of you to start sharing your recipes as soon as you start using this feature!

As soon as we can all get our hands on this new capability (watch for it in First Release by the end of October), I expect to start adding examples to a new Github repo I just created: SPColumnFormatting. There is a new repo on Github managed by the SharePoint team called /sp-dev-column-formatting. It already contains the samples from the Column Formatting documentation, and expect it to grow.

If you don’t understand Github, don’t fret. You’ll be able to just copy the recipes and alter them to meet your needs. If you do understand Github and would like to contribute, please do with a pull request. The faster we build up a set of recipes people can use, the more productive the citizen developer army out there will be!

Enabling the “Anyone” Sharing Setting in and Office 365 Site

Julie (@jfj1997) and I are working hard on SPS New England coming up on October 28 in Burlington, MA. I wanted to set up an easy place for the speakers to drop their slide decks. We have a perfectly good Office 365 tenant, so I figured I’d just create a Document Library somewhere and share the link with the speakers so they could do so.

Unfortunately, it wasn’t quite so simple. (What I needed was an Anyone link, but it was grayed out. The message on hover “Your organization is preventing you from selecting this option.” didn’t help me much since, well, I am my organization! (Well, I do have to answer to Julie from time to time.) I’d love for the message to say something link “Talk to your admin about turning on Anyone sharing, as described at this link.” The Learn more link didn’t really help, either. I considered using Dropbox!

Instead of using Dropbox, since I couldn’t figure out the magic incantation to light up the Anyone link in the sharing dialog, I turned to my favorite RTFM replacement, the Twitters.

I got a bunch of suggestions, and maybe I was too dense to understand them, but I wasn’t getting any joy. Luckily, one of Microsoft’s finest employees, Tom Resing (@resing), saw my tweet:

Tom’s a swell guy, so he poked around at Microsoft and asked a few people how to do this. Today he got back to me.

Before I tweeted, I had checked the settings in the following two places, so thought I should be good.

First, the Office 365 Admin center, under Security & Privacy. That looked good.

Then I had checked in the SharePoint Admin Center under sharing, and that looked good.

It turns out there is yet another setting I needed to change. This one is in the SharePoint Admin Center and is at the individual Site Collection level under Sharing:

Even though the other settings above say that anonymous links are allowed, I still needed to say that anonymous links were allowed at the Site Collection level.

Even worse, if the site is a “modern” Office 365 Group -based site, there is no UI to change this setting yet – at least until we have the new SharePoint Admin Center. You’ll need to use Powershell (IMO, the answer should never be “you need Powershell”).

$s = Get-SPOSite -Identity "fullurl" Set-SPOSite -Identity $s -SharingCapability ExternalUserAndGuestSharing

After a few days of trying to figure out, I can start collecting slides! Thanks, Tom!

 

 

 

Detecting the Current SharePoint User’s Regional Settings

Brian McCullough (@bpmccullough) pinged me on Twitter today with a question:

Brian was asking about an old blog post of mine where I basically screen scraped the data: Detecting the Current User’s Regional Settings with jQuery. It worked fine in the old days, but I didn’t think it would work anymore on SharePoint Online.

It was bugging me that I couldn’t remember the answer, so I went digging into some SharePoint pages to see what I could see. In two different SharePoint Online tenants, I saw that the _spPageContextInfo object has the following properties:

  "preferUserTimeZone": false,
  "userTimeZoneData": null,
  "userTime24": false,
  "webTimeZoneData": {
    "Description": "(UTC-05:00) Eastern Time (US and Canada)",
    "Bias": 300,
    "Id": 10,
    "DaylightBias": -60,
    "DaylightDate": {
      "Year": 0,
      "Month": 3,
      "DayOfWeek": 0,
      "Day": 2,
      "Hour": 2,
      "Minute": 0,
      "Second": 0,
      "Milliseconds": 0
    },
    "StandardBias": 0,
    "StandardDate": {
      "Year": 0,
      "Month": 11,
      "DayOfWeek": 0,
      "Day": 1,
      "Hour": 2,
      "Minute": 0,
      "Second": 0,
      "Milliseconds": 0
    }
  },
  "webTime24": false

As I recall, if the userTimeZoneData  is not null, then the user has set their time zone themselves. If it is null, then the user is going along with the webTimeZoneData. I don’t have an example, but I’m pretty sure you can also check preferUserTimeZone  to see if the user wants to have her own time set.

Note that there are lots of other goodies in _spPageContextInfo as well. The other properties which are pertinent to time zones are:

  • userTime24  – Does the user prefer 24 hour (military ) time?
  • webTime24  – Does the Web display 24 hour (military ) time?

The TimeZoneData  properties are also quite rich, showing the time zone description, the bias from GMT, Daylight Savings Time bias (usually 60 mins, but sometimes 30 mins), when DST starts and ends.

<update date=”2017-10-04″>

Bryan Mathews (@brmathews) reminded me on Twitter that there is a REST endpoint that provides some of this information as well.

If you use the /_api/web/RegionalSettings/TimeZone endpoint, you’ll get back information like this:

<entry xml:base="https://sympraxis.sharepoint.com/_api/" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml">
<id>https://sympraxis.sharepoint.com/_api/web/RegionalSettings/TimeZone</id>
<category term="SP.TimeZone" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<link rel="edit" href="web/RegionalSettings/TimeZone"/>
<title/>
<updated>2017-10-04T13:27:39Z</updated>
<author>
<name/>
</author>
<content type="application/xml">
<m:properties>
<d:Description>(UTC-05:00) Eastern Time (US and Canada)</d:Description>
<d:Id m:type="Edm.Int32">10</d:Id>
<d:Information m:type="SP.TimeZoneInformation">
<d:Bias m:type="Edm.Int32">300</d:Bias>
<d:DaylightBias m:type="Edm.Int32">-60</d:DaylightBias>
<d:StandardBias m:type="Edm.Int32">0</d:StandardBias>
</d:Information>
</m:properties>
</content>
</entry>

This is XML, but the data you’ll get back in JSON is the same. Note that there isn’t as much details here as in the _spPageContextInfo, but this endpoint is available to you in any page.

</update>

Note that depending on the version of SharePoint you’re running, your mileage may vary.

Does anybody really know what time it is?

 

Exclude Specific Content from SharePoint Search Results

This is a quick one, and I’m only posting because it took me a while to find the answer with my Binglage. It’s one of those things I know I can do, but I forget the syntax.

Image from pixabay

Most of the answers I found for this involved going into settings (or running Powershell or writing code – overkill!) to exclude content from results semi-permanently. That’s not what I want in this case.

Say you want to search SharePoint for a specific phrase. In my case today, we want to change the name of the Intranet, so I was searching for the old Intranet name. This would give me the places where documents or pages contained the old Intranet name so I could change it in the content.

When I simply searched for “Old Intranet Name” in Everything, I was getting back a whole lot of documents we put together as we were building the Intranet. The phrase in those documents was irrelevant, so I really wanted to exclude those documents from my search results.

The trick is to exclude the specific path (or any other useful attribute value) so that I reduce the result set down to what I really want. It works basically the same way most search engines do; if you put a value after a dash character, content with that value will be excluded from the results.

"Old Intranet Name" -path:https://myTenantName.sharepoint.com/sites/OldIntranetProject

The trick is that you need to know what the specific attribute – which in SharePoint is a Managed Property – you need to use for the exclusion to work correctly. In this case, I wanted to exclude an entire path, meaning any document which lived at that URL or below.

While I’m working in SharePoint Online today, this works going all the way back to SharePoint 2007, I believe.

Good search skills are critical in today’s world, so knowing how to exclude content is just as important as including it.


It so happens that Brian Jackett (@BrianTJackett) just tweeted something analogous, so here’s a good reference that shows more possible options.

Reference