The Short
The Long
Scenario:
So impressive is your list and Flow that usage far exceeds your expectations! Suddenly you find yourself inundated with emails from coworkers lambasting you because that thing that you made isn't working anymore.
"No sweat," you say, "I'll just check the Flow activity logs!"
Interesting. Maybe it's under 'See all'?
Nothing! At least - nothing obvious. However, if you pay close attention you'll notice in the top-right there's a drop-down list. Let's pick 'Failed checks' and see what we find.
Oh! Now we're getting somewhere.
For the unaware, even though Microsoft has been kind enough to make Flow simple for nearly anyone to use, a lot of the triggers that we take for granted often times do some heavy operations that are invisible to us the end-user. The 'SharePoint - When an item is created' trigger just so happens to be one of those.
It turns out that Microsoft Flow is querying your SharePoint list on a set interval to see if anything has been added (or modified, depending on your trigger) since the last time it looked. Without getting too deep into the weeds, this query is subject to the same limit as any other filter or query - known as the List View Threshold. For SharePoint Online, that limit is hard-set at about 5,000 items.
That list you created earlier? It just hit 5,001.
Let's go back to our 'Failed checks' list and click into one of the items.
'BadGateway'? Doesn't tell you much, does it?
Go ahead and click the 'x' in the 'Failure summary' pane and then the 'When an item is created or modified' trigger to expand it.
Scroll down to see the trigger outputs, specifically the 'Body'.
Inside the body field scroll down to 'message' to uncover our hidden nemesis.
There it is! Here's the message you'll see if you scroll further to the right:
"The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."
Ok, now we know what's wrong. How do we fix it?
Thankfully the fix is relatively painless - assuming your list hasn't exceeded 20,000 items (if it has then you'll need to do some trimming to bring it under that magic number).
Update: Microsoft has mercifully lifted the 20,000 item limit on creating new indexes, which means you can now freely create indexes regardless of item count. Huzzah!
Let's head over to our SharePoint list settings to fix this once and for all.
Scroll down to 'Columns' and click on 'Indexed columns'.
In 'Indexed columns' click 'Create a new index'.
Select 'Modified' from the 'Primary column for this index' dropdown.
And finally, click 'Create'.
If all went according to plan, you'll be welcomed by a screen that looks like this - with our newly created 'Modified' index!
You'll need to give it some time for your index to be generated. Depending on the amount of items you have this may take a while.
When all is said and done, you'll be able to go back to your Flow run history under 'Checks (no new data)' and hopefully see recent checks with a Status of 'Skipped'. This means the Flow was able to successfully query the newly created Modified index instead of crashing into the List View Threshold.
Go ahead and add a new item to your list (which in my case had 73,000 items!) and you'll see the Flow kick off like it did before you had to go through all of this!