Unanswered

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

(0) Share

Report

Posted on by 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.

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (0)

Rhiassuring 8,690 Moderator on at

Like (1)

Report

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

Was this reply helpful? Yes No
Shannah_Bonner 44 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
AH-22072022-0 on at

Like (1)

Report

@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.

Was this reply helpful? Yes No
Shannah_Bonner 44 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Shannah_Bonner 44 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Shannah_Bonner 44 on at

Like (1)

Report

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

Was this reply helpful? Yes No
AH-22072022-0 on at

Like (0)

Report

https://powerusers.microsoft.com/t5/General-Power-Automate/How-to-get-previous-versions-of-a-list-items-from-Sharepoint/td-p/49335

This may help.

Was this reply helpful? Yes No
Shannah_Bonner 44 on at

Like (1)

Report

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!

Was this reply helpful? Yes No
CSTEFDHS2121 2 on at

Like (0)

Report

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."

Was this reply helpful? Yes No
Shannah_Bonner 44 on at

Like (0)

Report

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.

Was this reply helpful? Yes No