SharePoint List Event Handler: The second-best reason you should use Power Automate

The regular reader might imagine I hate Power Automate w/ a fury of a thousand suns.  You would be wrong.  I hate Dataverse to that degree.  However, Power Automate has actual value.  It is just terrible at delivering it unless you are very careful.

Regardless, the second most value you can get out of Power Automate (here's the first) is to use it as a lightweight event handler for SharePoint.  In fact, it is very good at this and works very reliably.  If you have a list where you want to have "something" happen when a specific value changes to "something else", then this will walk you through it

TLDR: Quick Setup

If you set up a Power Automate flow to trigger on the "On Modified" (or even On Create) event for a list item, then you can further narrow this down so it only triggers for events where a specific field/value (or multiples) result in a TRUE value.  For example, using the "When an item is modified" trigger, if you click on the Settings header and then add a Trigger condition:

@equals(triggerOutputs()?['body/yourFieldNameHere'], 'yourFieldValueHere')

Change the blue text above to reflect the name of the field you want to trigger on and the value you want to trigger on.  In my screenshot above, I am triggering on the field Status and the value of 2Up.  That means, it is only going to run if a row in SharePoint is MODIFIED and has a Status value of 2Up.

NOTE: It is important to have your trigger be a field you're planning to modify.  Having it be something like an "interim" value that fires your automation, which then gets changed at the end of your automation.  Otherwise, if you do not change the field you are triggering on and then you or anyone else updates this field (again, via any method), it will refire your script.  If your script also writes back to the row: infinite loop.

Setting up Trigger Condition(s)

As I explained in the prior section, you can have multiples here or you can create a compound statement vs. the simple one I did above.  You don't have to have it use an equals() function, you could use anything that resolves to a Boolean, like:

• greaterOrEquals()
• greater()
• less()
• lessOrEquals()

All of these will return a Boolean where a true value results in the flow running.  These aren't the limits of what you can do, but it is the standard use case.

What this gives you is the ability to put in some back-end workflow items into your SharePoint list that allows for hybrid solutions (where multiple interfaces might alter your list including manual changes) to still work reliably.  

Avoiding the Infinite Loop Trap (for On Modified scripts)

The important thing to remember is that the On Modified Sharepoint trigger is not when that specific field is changed but if/when any field is changed.  So if your trigger field isn't changed by your flow to "something else" at the end, then any change to that record will fire off your flow the same as before.

There are advanced scenarios where you might want to do something like that, but 99% of the time, you want to ensure you change your trigger value at the end of your flow. 

See the next section for an example workflow use-case that will make this clearer.

Example Workflow: Managing by the Status field

Let's say you have a field that represents where we are in a workflow and let's say that the field's name is Status.  In my above screenshot example, I have a series of statuses that represent not only what person is handling something but what they have decided to do.  For example, let's say we have a status list similar to:

• Pending  (user has created it but hasn't submitted it)
• Submitted (user has submitted it)
• Leader  <-- Our trigger value
• Leader:Pending (awaiting leader review)
• Leader:Approved (leader approved)
• Leader:Denied (leader denied)
• Leader:Cover (leader wants someone else to handle this and move to another leader)
• Completed:Approved (completed and approved)
• Completed:Denied (completed and denied

Our Trigger Condition for our Flow will be:

@equals(triggerOutputs()?['body/Status'], 'Leader')

Why should we do this?

As our business workflow moves forward, we might want to insert some logic for preparation before certain things happen.  Sometimes there are complex rules of what Leader something should go to (working in a different area of the organization than a person normally works, people are on a leave of absence, etc.).  So putting things into a "Leader" Status is a prequel to the next stage of work.

For example, I might need to alter the security for that specific row so the leader can see it.  I might want to change which leader is supposed to handle this based on some ancillary enhanced rules.  I also might want to update a leader dashboard.  

Once we complete whatever tasks we want to do at the end, we'll want to update our original SharePoint List Item w/ a new Status.  For example:

When we update the Status field to the next value, that means the original Trigger Condition is no longer true, so our flow will not run again for this specific item.

NOTE: This also makes it easy to do manual updates where a leader/approver is manually changed for example and the value is simply reset back to the original Status=Leader and all of the security/coverage logic can run again.

Workflow Microservices

What this leads us toward is a design pattern where the individual workflows can even be managed by different people.  If we have a specific group that manages say HR approvals.  The HR approval flows can be shared with them to co-manage and they can update their own aspect of certain approvals.  

In my initial screenshot, our workflow had changed to add a second approval layer.  We added a "2Up" approval (leader and then the leader's leader).  This meant that we just copy/pasta'd all of the same workflows for the Leader tasks and created a new 2Up series of statuses.  This made it exceptionally easy to add new approval paths/trees without elaborate code logic inside of Power Automate (which you should not do).  

Now you can set up back-end automation that allows users to make changes within the application(s) itself or moderators/admins to manually make changes to a SharePoint list and the same logical work still fires.  Notifications can be sent, automated approvals/rules can be implemented, etc.

Certainly this can seem like "a lot of Power Automate flows", but this means each of these flows are exceptionally simple to troubleshoot and change at a moment's notice.  If someone suddenly wants to change the notifications for a Denial, then it should be fairly simple to figure out where to do it (hint: it is the ones that end with _OnDenial).  This lets training be minimized so that changes can be quickly implemented with limited documentation.

As I mentioned above, some people might be tempted to have one "master flow" that then routes logically to an array of things.  However, Power Automate is difficult to troubleshoot or edit safely.

Adopting the microservice approach means that when (not if) you have to make a quick change and it isn't working as expected, you have a very small # of things that can go wrong.  Elaborate flows make it impossible for you to make a change for one thing without possibly impacting an unrelated thing.  This becomes more of a problem in large organizations where different departments have different rules/preferences.  You don't want to impact department #1 because department #47 decided to do something crazy.

Final Thoughts

Do go back and see my note again about infinite loops.  Be sure to change the value of your trigger column at the end.  You'll probably only forget to do this once.

SharePoint is actually pretty robust when using this pattern.  Event handling happens fairly quickly.  The default is 3 minutes for response, but you can certainly drop that down a bit w/o too much risk.  It is very reliable and is probably some of the easiest automation you can do.

It can get a little cluttered when you have 30 flows triggering off of one or more fields for a single list.  However, it is still notably easier than trying to troubleshoot some mega-flow.  Proper naming conventions will help you here.  There's no single rule here, but as you see from mine I use a pattern of:

• projectName_mainThingWeAreDoing_optionalSubThing

Sometimes I include sub-projects (because there are multiple lists or such) but I try to be flexible while also making things legible vs. adhering to a complete standard.  The key is to make things so you can understand them, not so your Architect (which I am) can yell at you about you not following the rules they laid out for you.

While I would caution you against trying many of the "levers" inside Power automate (don't you ever touch that Concurrency toggle!), this is one of them that works reliably and well.  It is one of the (very) few things that I think is worth investing your time/energy into and committing to use as a primary pattern for your next project.