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.

<UPDATE>

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

</UPDATE>

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.

Advertisements

SharePoint and Office 365: The New Beautiful Cookbook Series

Most of us are “meat and potatoes” people when it comes to the technology we use. We like what we know and we know what we like. (Yes, there are vegan “seitan and potatoes” people, vegetarian “sprouts and potatoes” people, pescatarian “cod and potatoes” people, etc. I’m not trying to leave anyone out.)

Every once in a while, though, someone hands us a new ingredient – something we’ve never seen before, something we’ve never cooked with.

Image from the Netflix show Chef’s Table S3E6 – Virgilio Martinez

That new ingredient becomes a part of our pantry, and we want to try to cook with it. We’ve probably heard how delicious it is or how it can make an ordinary dish taste amazing.

Sometimes, we get a whole new palette of ingredients. (Many of us love to watch cooking shows for just this reason: we see novel dishes and decide if we’d like to try them at home.)

Image from the Netflix show Chef’s Table S3E6 – Virgilio Martinez

We need to take a ton of time to figure out what the new ingredients are, how we can work with them, and what we can cook. If we don’t cook with the ingredients pretty often, then we lose the knowledge of how to use them, what ripeness is best.

Writing off something because it tastes bad in one context means we may miss a great use of the ingredient later – a ripe plum tastes so much better than an unripe one. Once someone has eaten an unripe plum, they may decide they hate plums.

But if we can overcome these hurdles and learn about the new ingredients, we can make some incredible dishes.

Image from the Netflix show Chef’s Table S3E6 – Virgilio Martinez

This is what I think we are going through with SharePoint and Office 365 right now. Microsoft is offering us an entirely new set of ingredients with which to make our stew.

Let me give you an example…

In my “meat and potatoes” way of looking at the world, which has been pretty consistent for the last ten years or so, even though SharePoint and my approaches have evolved, I might use this set of ingredients:

  • A Single Page Application (SPA) written with AngularJS or KnockoutJS – or even just plain old JavaScript
  • A dollop of values passed on the query string to a…
  • Standard list form, with a little JavaScript mixed in to pre-populate some columns in the form
  • A SharePoint Designer workflow to add notifications on top (Substitute Alerts if your local market doesn’t carry SharePoint Designer)

But there are new ingredients now. Instead we could whip something up with these:

  • A SharePoint Framework Web Part (still maybe written with AngularJS or KnockoutJS)
  • Creating list items using REST based on the values in our SPFx Web Part
  • Microsoft Flow to add in the notifications and any process
  • Stir in a pinch of PowerApps – until they are ready

That’s quite a shift. We’re being asked to think about cooking in a very different way. We’ve been through stages of evolution before – new cooking techniques like sous vide (Sandbox Solutions), gelification (Add-In Model, nee App Model), etc. – but this time it’s really different. We’re not even sure if we’re supposed to like everything we taste. Is it just the next wave of kale frenzy or is it an ingredient that will last?

At this point, Microsoft is asking us to dream big, and reach for the previously unimaginable. I think we need to try to do it.

Image from the Netflix show Chef’s Table S3E6 – Virgilio Martinez

Some of us will be able to cook up truly amazing solutions on the “modern” platform. Don’t be afraid to give it a taste.

Image from the Netflix show Chef’s Table S3E6 – Virgilio Martinez

In case you didn’t figure it out, this post was inspired by the Netflix show Chef’s Table S3E6, which profiles the Peruvian chef Virgilio Martinez. It’s an outstanding series, and this particular episode was stellar.

Also see any volume in the Beautiful Cookbook series.

How Can I Customize List Forms in SharePoint Online?

The other day, a client asked me a pretty classic set of questions about customizing SharePoint Online list forms. As with some other arenas of endeavor in Office 365, there is more than one way of doing things and it can be confusing.

I am looking at options to customize List forms on sites in SPO and I am trying not to have to deal with code.

My first choice has been InfoPath Designer but I know this is being deprecated and it seems like some of my sites are not allowing the use of InfoPath to customize my forms. [This was an internal issue, not an InfoPath issue.]

I know I could add web parts to the form pages and use JavaScript / jQuery or I could try and edit in [SharePoint] Designer but without the design view I am hesitant to mess around there too much.

Do you have any other tools you recommend for customizing List Forms?

Here’s an expanded version of the answer I gave my client, which incorporates some additional ideas and feedback I gleaned from Dan Holme (@DanHolme) and Chris McNulty (@cmcnulty2000) at Microsoft. (It’s nice being in Redmond for MVP Summit this week, where I can easily catch these guys in the hallway!)


The answer is the classic “it depends”. The main thing it depends on is what type of customization you actually want to do. There are a number of approaches and each has its pros and cons.

Adding JavaScript to the out-of-the-box forms

This is still possible, but I would discourage it in many cases, even though this has been my bread and butter approach for years. The out-of-the-box forms are changing, and “script injection” to do DOM manipulation is less safe. Remember you’re working on a service now, and it can change anytime.

SPServices

Unfortunately, this means that getting things like cascading dropdowns into forms is becoming harder than it used to be with SPServices. It’s not that you shouldn’t use that approach, but it does mean that the clock is ticking on how long it will continue to work. At lthis point. I’m recommending building entirely bespoke custom forms rather than adding JavaScript to the existing forms, though I still do plenty of the latter.

InfoPath

InfoPath

Yes, it’s deprecated or retired or whatever, but Microsoft will support InfoPath through 2026 at this point. InfoPath is often overkill – you can do a lot with list settings – but at the same time, it can still be useful. Keep in mind that using InfoPath means you’ll need to stick with the classic view for the list or library.

PowerApps + Flow

PowerAppsFlow

These new kids on the block are the successors to InfoPath, as announced at Microsoft Ignite. They can do a lot, but they may or may not meet your needs at this point. They did reach GA recently.

PowerApps would be how you would build the forms themselves and with Flow you can add automation – what we’ve called workflow in the past.

PowerApps embedding is “coming soon”. This will allow us to embed PowerApps into the list form context, as shown in the screenshot below. This will be a GREAT thing for a lot of list scenarios. At that point, I think the need for InfoPath will be greatly diminished.

PowerApps Embed

SharePoint Framework (SPFx)

SharePoint Framework (SPFx)

The SharePoint Framework is the next gen development model for things like custom Web Parts, which will run client side. We can build pretty much anything you want with this, but it’s still in preview. At some point in the not-too-distant future, I think we’ll be building a lot of custom forms using SPFx.

Fully custom forms

AngularJS

KnockoutJS Logo

To create fully custom forms today, you might use development frameworks like AngularJS or KnockoutJS (to name only a few). This can be the right answer, with the goal being to build in a way that can eventually merge into SPFx and the new “modern” pages. Knowing a bit about SPFx is a *very* good idea if you are going to go this route today. You’ll want to build your custom forms in such a way that you aren’t locking yourself out of wrapping them up into the SPFX for improved packaging and deployment down the road.

Third party tools

Because of how I roll, I haven’t used any of the third party tools out there, but there are many. The ones I hear come up most often are Nintex Forms, K2 Appit, and Stratus Forms. Obviously, there’s a economic investment with these choices, as well as a learning curve, so it’s always “your mileage may vary”.

Nintex Forms K2

Stratus Forms


The landscape here continues to change, so keep your eyes and ears open for more news from Microsoft and bloggers like me in the future!