My Life Is Good – Celebrating Ten Years of Sobriety

Today marks ten years to the day since I had my last drink of alcohol. That’s 3,654 days without a drink, or 87,696 hours, or 5,261,760 minutes. If you can’t live without a drink, you know how big those numbers are. I quit cold turkey on September 30, 2007. Most people can’t do that and it’s much harder for them. I was lucky that I could simply turn it off – but I had gotten to a point where if I didn’t turn it off, I might not have lived to write this post.

I tried Alcoholics Anonymous and an addiction therapy group for about six months after I quit, and for me they just didn’t feel right. I joined a men’s therapy group and spent over six years working on my demons and just normal issues to help make myself as whole as possible. You don’t have to use groups like this to get off the sauce, but for many people they can be great anchors.

It amazed me how much of my personal growth had been stunted by my alcohol abuse. I had moored myself in the age where I really got going with my drinking. The literature backs this up – many drunks stop maturing in their thoughts and actions once the booze takes over their lives. I was what many people call a “functional alcoholic”. I did my job – and not so badly – but when I got home, I had to have a drink. Over the years, I needed to drink more and more to make myself feel what I though of as “good”. The bottles became better friends to me than my actual friends, or even my wife.

When I first stopped drinking, it amazed me how embedded drinking is in our culture. Some of that embedding is simply about enjoying life to its fullest (think a French country meal without a nice glass of the local red), but far more often here in the USA, it’s about partying and going crazy. (If you’re doing a lot of the latter, you might want to think about your relationship with alcohol.) In many ways, we’re taught that alcohol = fun. Without drinking, we’re missing out.

After I quit, it amazed me:

  • That going out with friends was actually more fun in many ways
  • How good I could feel waking up in the morning
  • How much more time there was in the day – drinking is a time consuming job
  • How I no longer wrestled with my “oh, I could quit anytime if I wanted to” nonsense discussions with myself

This isn’t just a self-congratulatory post, though. (I admit – it does feel pretty good!) I want to help anyone I know out there who feels like they may have a problem. Addictions are crazy things; when we are in the middle of them, we can’t see them – once we stop, it’s like a grey cloud has been lifted.

I’m not here to preach to anyone about their own choices. For me, stopping my drinking was – I believe – a matter of life or death. But it doesn’t have to be that serious for it to be a good decision for you. Alcohol cheated me out of many good years, and probably many good relationships. When you’re a drinker, you don’t tend to blame the alcohol or yourself – you blame others.

In the last ten years, I:

  • Started my own successful business
  • Wrote an open source library used by thousands of people around the world – SPServices
  • Became a Microsoft MVP for SharePoint
  • Traveled the globe speaking about SharePoint, collaboration, and knowledge management – all my passions
  • Regained the love of my wife (despite all my other failings)
  • Tried to be the best Father I can be to our awesome son Calder (aka “The Dude)
  • Took back control of my life

Most importantly, I’ve enjoyed so much of that time. (Into every life some rain must fall.) For many years, I had thought I needed the booze to keep me grounded and happy. The image I had in my head was of Jacob Marley in A Christmas Carol. I don’t know why it was that image, but it stuck with me for years. Without the booze, I’d no longer be grounded, plus the booze was a burden I deserved. By casting off my alcohol-laden Jacob Marley chains, I have felt lighter, and breathed more clearly than I did in the time I was drinking.

Now that I look back at that thinking, I can’t believe it made any sense to me. I consider myself reasonably intelligent, yet I was able to convince myself of the value of a way of life which was totally not what I thought. That’s the insidious nature of a substance like alcohol – it rearranges the way your mind works so that you can make sense of your own behavior.

If you ever want to have a conversation with me about what my journey was like, I’m here. No one should go through the suffering addition causes without an opportunity to make big changes. But the only person who can make those changes is you. Oddly, there’s little stigma to drinking, yet considerable stigma to being an alcoholic, even if you can quit. I’m here to tell you that any amount of stigma is worth it to live the full and happy life you deserve, despite yourself.

Thank you to everyone who has been with me on this grand journey. I’m not going to list names, but many people in my life have been important parts of what amounts to my rebirth. I also want to thank all of the people who read this blog, because your thanks and knowing I’m helping some of you do better things with the technology I love to work with give me sustenance every day. If you’ve made it all the way to this point, thanks for spending a little time with me on what is – for me – an important day.

Next time you see me, let’s raise a glass of club soda together. And m. and C. – I’m here for you.


Microsoft Flow: Updating File Metadata When You Have More Than 5000 Items

It’s been way too long since I did a blog post. Let’s remedy that, shall we?

Paul Culmsee (@paulculmsee) has been doing some great work with Microsoft Flow and PowerApps. His daughter Ashlee even won a contest put on by the PowerApps team to make a fidget spinner app! When Paul digs into a technology, stand back, because he beats it into submission like few others. If there is a Clever Workaround – and we need those way too often – he’ll find it.


Paul’s latest post on this topic shows that we now can get the ID of the newly created item from the Create File step, which we can use with the newer SharePoint – Update File Properties Flow action. Thank goodness my tricks here are no longer necessary!  The (new current) best way to update SharePoint document metadata using Flow


Paul did a post back in August I’ve referred to multiple times as I’ve tried to use Microsoft Flow. My approach with Flow here is that I try to use it for anything new I need to automate in Office 365. If I hit a roadblock, then I revert to SharePoint Designer workflows. The good news is that I’m finding I revert less and less, though it still happens maybe a third of the time at this point. (I’m guessing, but that’s about what it feels like. I would love it to be 0%!) Unfortunately, that means rework down the road if we want to be fully “modern”.

Paul’s article The (currently) best way to update SharePoint document metadata using Flow shows us the unfortunately byzantine way we need to do things in order to update metadata on a file we’ve just created. The fact that we don’t get a useful “handle” back to the file we just created is sorta crazy, and the Microsoft Flow team knows this. In the meantime, Paul’s approach works. Well, it works unless you have more than 5000 items in a library.

I had exactly that situation. At one of my clients, we have an automated process which dumps files into a library I’ve inventively called __Temp. When a new file shows up, I run a Flow to copy the file into a place where we want to store it and apply metadata to the copied file. My problem arose when I tried Paul’s second step. Because my library has way over 5000 items, that step failed.

So, until we are able to get the ID or some other useful handle for the newly created file, I needed to come up with a cleaver workaround on top of Paul’s clever workaround.

The trick was to do the following:

  • Add an index to the Modified column in the destination list.
  • Set up a TimeStamp variable in my Flow to capture the startOfDay() when the Flow is running
  • In the GetItems step, use a filter based on the Modified column, and *then* the FileLeafRef to find the file I just created

Here it is in pictures…

Make sure you have an index on the Modified column in the target list. In my case, I have indices on several other columns as well.

In the flow, set up a variable to hold startOfDay(utcnow()) . You set this up in the Expression Builder. I initially tried to set the variable just before I created the new file, setting it to utcnow() . In theory, this should have worked, but I found I had about a 50% failure rate. This doesn’t make a lot of sense, but to be safe, I switched to startOfDay() , which has proved reliable.

Just like Paul’s example, I then create the new file.

In the GetItems step, I use the more complex filter:  Modified gt datetime'TimeStamp​​' and FileLeafRef eq 'Name' Because Modified is an indexed column, and it is the first filter in my expression, the result set is reduced to fewer than 5000 items and the query works.

Now everything works reliably. I can’t wait to reduce the complexity of this Flow when we get a handle to the newly created file in the Create File step!

Aren’t you proud of me? I wrote this whole post without whining about the 5000 item limit.

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.

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.


Auth, Auth, Auth – The Bane of Our Development Existence

I was reading through one of Bob German’s (@Bob1German) recent (and great!) posts about Porting REST calls to SharePoint Framework and thinking about a conversation Bob, Julie (@jfj1997) and I had at our “salon”* when I was reminded of a very specific truth about our working days.

Image from New York City Department of Transportation on Flickr

I would venture to say that some ridiculously high percentage of all of our time developing with SharePoint and Office 365 comes down to figuring out why we can’t get something to work due to authorization or authentication issues. Some say this is a necessary evil to create enterprise software, but I say it’s an unnecessary problem. Unfortunately, documentation about auth stuff tends to be tremendously opaque and difficult to understand. In the same way that I expect the server to be there and I want nothing to do with it directly, I want auth stuff to just work – easily.

Sadly, this has never been the case with so-called “enterprise-class” software. On some level, I think the obtuseness of auth is there on purpose to keep the bar high enough to exclude lower end hackers. Unfortunately, we all get caught up in the kudzu of auth as a corollary effect.

Image from NatalieMaynor on Flickr

Ok, so that’s my editorial on auth. I wish it were easier, but it isn’t.

Recently in one of my Single Page Applications (SPAs) for a client, we kept getting weird failures in posting data to a list. Weird mainly in that I never saw the error on my end, my client is far away, and screen sharing is tricky due to the technical proficiency on that end. I’m not dissing anyone; they are great at what they do, but debugging JavaScript with me is not in their wheelhouse.

If you write software, you know that the worst bugs to squash are those that happen sporadically, and only on someone else’s machine – especially if you don’t have direct access to that machine. As usually, though, it was simply me doing something dumb with auth – which is not easy. Have I mentioned that?

Basically, the problem was that while I was fetching the request digest from the page (this is a “classic” page in SharePoint Online), I wasn’t ever refreshing the token. In this application, people move around from page to page enough and use the application for short enough time periods that we simply hadn’t seen the problem in testing.

Smart people will think “Marc’s an idiot” on this one, but I play the fool so you don’t have to.

The code comes from a service I use everywhere in my applications for this particular client. It’s basically a set of utility functions that are useful when you’re using Angular with SharePoint. I’ve built the SPA using AngularJS (1.x), so my code below represents that. However, similar logic can work with jQuery or whatever instead of AngularJS and $q. I adapted it from some Typescript code Julie was using, so credit there, with a bit of add on from me. I’ve added a bunch of comments and have also left some of the console logging I used in debugging – but commented out.

The data we get back looks something like this:

The thing we care about most is the FormDigestValue , as that’s what we use to make our POSTs to SharePoint while it’s valid. But also note that there is an attribute in the returned JSON for FormDigestTimeoutSeconds. That’s the number of seconds that this particular token will be valid. In every tenant where I’m using the code, that works out to be 30 minutes. However, there may well be a setting which can change that, or Microsoft may change the time span on us. Because of this, I use the value to calculate how often to request a new token: T-2 minutes.

I’m pretty sure that there are pages in SharePoint which don’t do this correctly, so I’m not feeling like too much of an idiot. For example, when we click on an “app” – usually a list or library to you and me – in Site Contents, it opens in a new browser tab. Very often when I go back to that Site Contents page – when it has been sitting there for a while – I’ll get an unauthorized error. This may be fixed by now, though it has happened to me a lot.

I hope this is helpful. Using functions like this, we can make the whole auth thing easier on ourselves – and there zero reason to need to write this code from scratch every time. Store int in one place and if anything changes, you’ll have one place to fix it later.

  • We have occasional “software salons” – usually in one of our homes – where we simply get together to kick around what’s going on in our work lives, interesting things we’ve solved, etc. It’s a tremendously educational and useful exercise. I would encourage you to do something similarly informal if you don’t have enough water cooler time. At Sympraxis, we have no water cooler.

Wherein I Profess My Love for Document Sets, My Hatred of the 5000 Item Limit, and Some Tips

I love Document Sets. There, I’ve said it. They help us solve so many important business needs, it’s crazy. Unfortunately, because telemetry tells Microsoft that not very many people use Document Sets, they haven’t gotten any love for a long time. On the other hand, I hate the 5000 item limit in lists and libraries because they prevent us from getting good work done for our end users.

With Document Sets, we essentially get folders on steroids. We have a canvas available to us in the form of the Welcome page, which is a Web Part Page we can customize to our heart’s content. That means we an add images, other Web Parts, script (using a CEWP or SEWP), whatever we need in order to make the Document Set sing for our users. We can even push specific metadata from the Document Set level down into the documents within it.

While on the one hand it’s great that Microsoft hasn’t given them any love for a long time (they haven’t broken anything), it will be great when they eventually get the “modern” sheen. (See my comment about not breaking anything – that’s key if the “modern” version is to get any use.)

Today’s episode comes courtesy of one of my clients where we’re using Document Sets to the max in SharePoint Online. It’s a life sciences R&D operation, and we’re tracking most of their significant research data in Document Sets, among other mechanisms. It’s a really cool project, and I often wish I could show more of what we’re doing.

When we first built one of the main libraries using Document Sets as the basis (with 14 different Content Type variants inheriting from Document Set and each other), we talked about how many items would ever be in the library. At the time, 5000 seemed like a huge and distant number. Even so, I added some indices to hedge against it, but clearly not enough indices. It’s been over two years using this system, and we’ve done a bunch of development on top that we couldn’t have predicted originally.

Recently, a couple of things stopped working the way they should. Even though we never expected to, we recently went over the 5000 item limit in the Document Library – 5099 is the current count. Here are summaries of the issues and how we fixed them. The ever wonderful and talented Julie Turner (@jfj1997) came to my rescue on some of it, as you’ll see.

Adjusting the Indices While Over 5000 Items

This has historically been a HUGE problem. Once you cross the 5000 item limit and actually NEED indices on some of your columns, you haven’t been able to create them. When you tried to do so, you’d get an error telling you that you had more than 5000 items, so you couldn’t add an index. Helpful. Off to Sharegate to move some content out, fix the indices,then Sharegate the content back in.

In our Document Set instances, we were getting some errors where we were making REST calls to retrieve items related to the current one. (The Document Sets connect together in pathways of experiments, and we store the ParentID for each item’s parent Document Set.) The REST call would only retrieve one item from the library, since there was a filter:

Unfortunately, ParentID wasn’t indexed, so we were getting 500 errors back from our REST calls. Sigh. I assumed I’d need to shuffle some content out to add the index.

Just on the off chance, I went to add the index anyway, even though we were over the 5000 item. Never hurts to try, right?

Miracle of miracles, I was able to add the index without SharePoint batting an eye. I haven’t had a chance to test this elsewhere, but in this tenant I was able to do what previously was impossible.

If this is indeed the new normal, our lives have indeed gotten a lot easier.

We can add indices to lists and libraries with over 5000 items!

In any case, it solved my immediate problem. Maybe I shouldn’t talk about it so loudly near the tenant in case it changes its mind.

Fixed the broken default view

No one – and I mean no one – likes to see this message on a SharePoint page:

This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.


To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold.

We were seeing this horrible messaged in the List View Web Part at the bottom of the Welcome Pages. Since I have code running in the page, I wasn’t 100% sure that it wasn’t my fault.

Since the List View Web Part is only showing documents for this Document Set, it should only want to show a handful for each Document Set; nowhere near 5000. I was starting to think the Document Set was fundamentally broken by design.

Luckily, Julie was online and I asked her to take a look. She had the answer, and probably saved me hours trying to figure out why this was happening.

Her suggestion was to make sure the view doesn’t have “Show all items without folders” set to true. Sure enough, when I checked the view we were using for the Document Sets List View Web Parts, that was the setting. Julie pointed me to the article Manage large lists and libraries in SharePoint, specifically:

If you choose the Show all items without folders option in the Folders section when you create or modify a view in this list or library, you must then use a filter that is based on a simple index to ensure you don’t reach the List View Threshold.

Aha! For whatever reason over the years, we had set that very setting, and that was the problem. By turning that off, everything was right as rain again.

Document Sets Can Have Unique Views

This leads me to a little known thing about Document Sets: we can have a different view at the root of the library than inside the Document Sets. In fact, since you can inherit from Document Sets, you can have a different view per Document Set-based Content Type!

In fact, I was just reminded this yesterday from reading a post from Cameron Dwyer. Sure, it’s a setting on the Document Set settings page, but frankly, I’ve rarely noticed it.

The setting isn’t visible in the Document Set settings page when you create the Content Type, because you aren’t yet in the context of a list. Once you have enabled the Content Type on a list, you’ll see the additional settings.

Here’s the bottom of the Document Set settings in the Content Type definition:

and here’s the bottom of the page in a list context:

Note that the options are slightly different. In the list context we can choose a unique view for the Document Set-based Content Type. That means in my library, I could have 14 different views of the Document Set contents, one per Content Type, should I choose to do so.


Document sets are awesome. The 5000 item limit is not.