SharePoint: Trigger flow on specific Task List Column change (just once)

[ET55]

Hi everyone!

I want to create a flow for SP Task List but can't find a way that would work properly. My situation: the task list has 4 columns (Task name (single line); Assigned to (person); Status (drop-down menu; Not started/In progress/Solved); Completion approved by (person)). When the task "Status" is changed to "Solved", the Manager should get an approval email for the completion of the task. If Manager approves - his name is inserted into the "Completion approved by" column. If he rejects the approval - the "Assigned to" person is notified about it by the email.

First problem is that flow would trigger on any update of the task if using "When an item or a file is modified" trigger. I've found a way to limit the updates to the "Status" column by adding this to the Trigger Conditions:

@equals(triggerBody()?['Status']?['value'],'Solved')

Second problem is that all tasks with "Solved" status would be affected by this flow. If someone edits the task with "Solved" status, the Manager would receive an email for approval. So I wanted to narrow the flow trigger down to the moment when the column is changed to "Solved" for the first time.

For this, I've tried to use condition - "Completion approved by" is equal to 'null' (if Yes - next step; if No - do nothing) with the idea that once "Completion approved by" field is filled in, then the updates of the task don't matter anymore and no approvals are needed for it. This way, it would only work for the first time when "Completion approved by" column is empty.

However, after many testing this option didn't work out since the flow test results in "No" (do nothing) all the time. Then I've tried out many condition options with Completion approved by Claims/Email/Name... Nothing works the way I expect.

I've also thought of the trigger "For a selected item" but not sure how that works and whether it's the same as "For a selected file" which allows users to trigger the flow themselves by selecting the file.

Any help would be appreciated, thank you in advance!

ET55

[pagonza]

Hello,

For your second issue, may yo can add another field ( maybe not visible) on SharePoint set by default to NO, and when someone change the status to solved, change this to Yes.

You can put a condition if this field is No, The flow should work, if it is yes the flow shouldn't work.

Hope this can help you!

[ET55]

Hi,

Thank you for the idea. However, I don't see how that would solve the second problem, because the flow would trigger for any Task changes that has the hidden column set to No. The same as at this moment, when flow triggers on any changes that are made for "Solved" task.

To be more specific, I need something that would only trigger when the Status column value is changed to specific option ("Solved").

Still having some hope with this case, thank you @pagonza 

ET55

[pagonza · Accepted solution]

Hello,

I think this idea can solve your issue, please let me know if I am understanding something wrong.

The flow would trigger for any Task that has been created, the hidden field is going to be No (set this by default)

You create a condition, always the field modified, that check two things at the same time, if the status is approve and if the field is "NO".

Once someone change the flow to approve ( this field is going to have the value "NO"), the Manager should get an approval email for the completion of the task. If Manager approves - update the fields with his name is inserted into the "Completion approved by" column and set this field to "YES ". If he rejects the approval - the "Assigned to" person is notified about it by the email and the field is going to continue in "NO".

So if someone change something, once the status is approve, the manager is not going to received nothing, due the flow is not going to pass the two conditions.

Please let me know if this helps you!

[ET55]

Actually, it did work out when I've tried it out several times!

Thank you so much @pagonza , your solution was so simple yet very effective.

Best regards,

ET55