Power Automate SharePoint List Column Status Change Trigger, Send Email (Only for new changes, not existing status)

[Shannah_Bonner]

Good morning. Power Apps newbie here.

Question: In Power Automate, I'm trying to create a trigger for a SharePoint List when a status for a column changes. The problem I'm running into is, when triggered, the flow finds each list item in that column with the same status and sends multiple emails (an email for each item), because there is more than 1 item with that status.  This occurs for SharePoint template "when an item is created" and for "when an item is created and modified".

I found information on using template "get changes for an item or file (properties only)" with "trigger windows" and using "send an http request to SharePoint" to check for version changes using "since version number". However, I can't seem to get the "Uri" correct. I get an error. (FYI: Triggered actions made in Power Apps, stored in SharePoint list).  Yes, versioning is enabled.

Anyone know a way to get this to work so that new changes are triggered and only an email for the new change is sent? Any help or information is greatly appreciated. Thank you.

[Rhiassuring]

Hi there, 

Hopefully I'm not missing anything, but, it sounds like you'll just need "When an item or file is modified", another column, and a trigger condition in your flow (you'll find it in the Settings of your initial trigger action). The other column should hold a copy of the status at the time it changes (NOT a calc column.) Then, your trigger condition can be "if Status is not equal to StatusCopy".  Then, just operate on that item, and that item alone - no need to get all items (ie, use the Dynamic Content that appears, don't pull anything else from the list.)

However, there's a caveat.... if you're doing this on a schedule (ie, not instant, and instead every 6 hours or something).... then you would need to "get all items" and then filter / iterate for all where Status does not equal Status Copy.  But.. that's another story.

I hope that helps? Let me know if it needs clarification...

Cheers,

Rhia

[Shannah_Bonner]

Thanks so much for the response!  Ok, so I have the "when an item or file is modified" first.   I tried adding the Trigger Condition, but it's not valid.  I know the syntax is off.  How do I write the condition for if the "Status" has changed equals true?

As far as the other column containing the copy of my status....what you mean by a copy of it at the time it changes?  Can you please explain that?  And this will be an automatic flow, not scheduled.  Thank you.

[ammarhaider22]

@Shannah_Bonner  I think your first approach is correct and you are almost there. If you add this in your "Since" field: sub(int(triggerOutputs()?[‘body/{VersionNumber}’]),1) this will compare current version with previous one.

https://wonderlaura.com/2020/08/25/flow-when-a-sharepoint-column-is-updated/

As far as your Uri is concerned, what I would do is HTTP request to get all versions using this uri:

/_api/web/lists/getByTitle('ListView')/items(ItemID)/Versions

And then Parse JSON and use the data from those versions to get what I want.

[Shannah_Bonner]

Thank you for the response and the information.  I found a video by a contributor on YouTube that included using the HTTP request to get the versions but I could never get it to work.  I did everything in the video but kept getting an error.

And I'm so new with Power Automate that I'm unfamiliar with using JSON.

[Shannah_Bonner]

I tested out the expression in the "Since" box and it says the expression is invalid.  When I simply copied and pasted (not as an expression) it accepted it, however, there is an error for it.

So, I checked out the link you sent provided.  I followed the instructions to a tee and it still errored with the "Since" expression.  

Thanks.

[Shannah_Bonner]

Question: How do I PARSE JSON for the version data?  Thank you.

[ammarhaider22]

https://powerusers.microsoft.com/t5/General-Power-Automate/How-to-get-previous-versions-of-a-list-it...

This may help.

[Shannah_Bonner]

I was able to find another way.  I used "When an item or file is modified", then "Get changes for an item or a file (properties only) with ID and Trigger Window Start and Stop, and Conditions with the following; 1) Has Modified Column Changed is equal to True, and 2) Has Status Column Changed is equal to True, and 3) Status is equal to "status value".  

Thank you all for your assistance!

[CSTEFDHS2121]

Hi Shannah, I'm attempting to do this exact flow but I'm struggling to get a successful trigger. Do you mind sharing your flow so I can see what I am missing?

In a nutshell, I want to trigger an email - among other things - when a user updates a column called status to show "Completed."

[Shannah_Bonner]

Hi.  I can't share screenshots, as I am a DoD civilian, but I am using the following flow for when a column is modified:

1. When an item or file is modified (SharePoint) -> include the site address, the list or library name and folder.

2. Get changes for an item or a file (properties only) -> include site address, list or library name. 

        The *ID is the column name I want to check changes for (i.e. Ticket Number)

        The *Since is Modified

        Include Minor Changes -> Yes

3. Condition -> And

                          Has Column Changed/Modified - is equal to - true

                          Has Column Changed/Ticket Status - is equal to - true

                          Ticket Status Value - is equal to - Assigned/In Progress (this is manually typed in)

If Yes -> Send email

If No -> empty

I have been able to copy this same flow for other columns and other statuses for the specific values that are available for those columns.  Hope this helps.  Thanks.