cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karthi
Level: Powered On

Modification Mail to be sent only once for specific column changes

Hi Team,

 I have sharepoint list with two columns "Assigned To" and "Forwarded To" i wanted to send mail when user enters the email id in Assigned To & Modified To. 

 

Based on the below flow (logic behind it: When assigned to is empty)  mails are triggered but whenever modification happens in this list. it again sends an mail.

Multiple mails are generated. I wanted to send only once when the assigned to & modified To columns are updated.

 

Can you please help me with this issue. User mail box is filled with mails due to this issue. 

 

 

 

Screen Shot 08-22-17 at 02.03 PM.PNGFlow

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Modification Mail to be sent only once for specific column changes

Hi @karthi,

 

The issue here is the condition would always be false once the assigned to field has been updated.

And the trigger works whenever there is a modification occurred in the SharePoint list.

So the No part would be run whenever there is a modification, which then result in multiple emails.

There is an idea submitted to suggest Flow to add trigger which hwn the modification only happens to sepcial field, I think that should be suitable in your scenario:

Trigger a flow when a specif SharePoint list field is updated.

Please consider vote it up, so that this would be considered in next Flow release.

 

 

 

For a workaround, we should create another field to mark the assigned status (AStatus, with initial value set to 0, once assigned, change this field to 1), for example, a number field.

In addition to check the empty status of the AssignTo field, we need to check if the Assigned status equals to 0.

 

The logic here is: (under first condition, we need to check two fields, one for empty, another for the Assigned status), if they both verified, then under the Yes or no part (depend how you write the condition), send the email, after the Send Email action, add another action, SharePoint->Update item, update the Assigned Status to 1.

For example, if we write the condition in the following way:

@or(and(empty(triggerBody()?['Assigned_x0020_To']?['Email']),
        empty(triggerBody()?['AStatus']) 
        ),
    and(empty(triggerBody()?['Assigned_x0020_To']?['Email']),
        equals(int(triggerBody()?['AStatus']), 0)
        )
  ) 

Then under the YES part, we add the Send Email Action, after that, add the Update item action, then update the Astatus value to 1, under the No part, just leave it blank.

The formula checks if the Assigned To is empty, and whether the Astatus is empty or equals to 0, in such situation, we send the email and update the item, otherwise, do nothing.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Modification Mail to be sent only once for specific column changes

Hi @karthi,

 

What is the data type of the Astatus in your example?

If Astatus is a Choice type, then the formula within the second part should be changed to:

 

@or(and(empty(triggerBody()?['Assigned_x0020_To']?['Email']), empty(triggerBody()?['AStatus']) ),and(empty(triggerBody()?['Assigned_x0020_To']?['Email']), equals(int(triggerBody()?['AStatus']?['Value']),0) ) )

 

If the Assigned status is defined as a Number type, then the previous formula for the condition should work.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Community Support Team
Community Support Team

Re: Modification Mail to be sent only once for specific column changes

Hi @karthi,

 

The issue here is the condition would always be false once the assigned to field has been updated.

And the trigger works whenever there is a modification occurred in the SharePoint list.

So the No part would be run whenever there is a modification, which then result in multiple emails.

There is an idea submitted to suggest Flow to add trigger which hwn the modification only happens to sepcial field, I think that should be suitable in your scenario:

Trigger a flow when a specif SharePoint list field is updated.

Please consider vote it up, so that this would be considered in next Flow release.

 

 

 

For a workaround, we should create another field to mark the assigned status (AStatus, with initial value set to 0, once assigned, change this field to 1), for example, a number field.

In addition to check the empty status of the AssignTo field, we need to check if the Assigned status equals to 0.

 

The logic here is: (under first condition, we need to check two fields, one for empty, another for the Assigned status), if they both verified, then under the Yes or no part (depend how you write the condition), send the email, after the Send Email action, add another action, SharePoint->Update item, update the Assigned Status to 1.

For example, if we write the condition in the following way:

@or(and(empty(triggerBody()?['Assigned_x0020_To']?['Email']),
        empty(triggerBody()?['AStatus']) 
        ),
    and(empty(triggerBody()?['Assigned_x0020_To']?['Email']),
        equals(int(triggerBody()?['AStatus']), 0)
        )
  ) 

Then under the YES part, we add the Send Email Action, after that, add the Update item action, then update the Astatus value to 1, under the No part, just leave it blank.

The formula checks if the Assigned To is empty, and whether the Astatus is empty or equals to 0, in such situation, we send the email and update the item, otherwise, do nothing.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
karthi
Level: Powered On

Re: Modification Mail to be sent only once for specific column changes

Hi @v-micsh-msftMichael,

Thanks for your detailed reply.

Unfortunately im getting the below error:

"Unable to process template language expressions for action 'Condition' at line '1' and column '2119': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'."

 

Can you please guide me how to rectify this error. Below is the condition which i entered based on your reply.

 

@or(and(empty(triggerBody()?['Assigned_x0020_To']?['Email']), empty(triggerBody()?['AStatus']) ),and(empty(triggerBody()?['Assigned_x0020_To']?['Email']), equals(int(triggerBody()?['AStatus']),0) ) )

 

 Thanks,

Kaarthi

Community Support Team
Community Support Team

Re: Modification Mail to be sent only once for specific column changes

Hi @karthi,

 

What is the data type of the Astatus in your example?

If Astatus is a Choice type, then the formula within the second part should be changed to:

 

@or(and(empty(triggerBody()?['Assigned_x0020_To']?['Email']), empty(triggerBody()?['AStatus']) ),and(empty(triggerBody()?['Assigned_x0020_To']?['Email']), equals(int(triggerBody()?['AStatus']?['Value']),0) ) )

 

If the Assigned status is defined as a Number type, then the previous formula for the condition should work.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Lexnnn
Level: Powered On

Re: Modification Mail to be sent only once for specific column changes

I have an approval process for list items and I wanted to send an approval email only when the status of the item changed from "Ordered" to "Ready".
 
The change of the status "Ordered" to "Ready" is an automized process so I though I would just put a "Wait until" or "Wait" function but this was not possible as the wait time is not always the same. I wanted to send an approval email from the moment it's ready so I have searched for a solution where there would be a check on a specific field. 
 
I tried using switch case or condition but there was a problem when other fields were updated and the condition was already fulfilled, it would send again an approval email:
eg item changed - Status is not "Ready" = Terminate (that's OK)
item changed - Status is "Ready" = send approval email (that's correct) but now item is changed again, other field is updated and the status is still "Ready" = approval email resent (that's not correct as it does not check what was updated" 
 
I did not want to use nested conditions or switch cases.
The solution link provided by grahampasmurf with version checking did the trick!! http://johnliu.net/blog/2018/5/microsoft-flow-sharepoint-trigger-on-specific-fields-changed-via-sp-h...
 
Thank you for that!
 
It did require some adaptation but it works!
 

flowemail1.PNGflowemail2.PNG

 

- ID can be found in "See more" Dynamic content
- SPREST-Versions is just renamed "Send HTTP request to Sharepoint"
- In the Url, if you use space or other special chars, you need to pay attention to how it's written eg space = %20
- Value is the expression from the linked website Body('SPREST-Versions')?['value']
- Orderstatus = valuefield "ValoTeamwork" is not found via Dynamic content but via Output from "Select" When I used the Dynamic content value it did not work as "Valo Teamwork" contained the actual value and was passing it throung to "GroupSiteUrl" from Dynamic content which remaind always the same.
- HTML table is not needed but usefull to debug and make sure it works correctly
- Conditions can again be found on the linked website: first(body('Select'))?['Orderstatus'] - last(body('Select'))?['Orderstatus']

More about approval flow

https://powerusers.microsoft.com/t5/Using-Flows/Flow-and-SharePoint-List-Status/m-p/132299#M3532

juan_fungi
Level: Powered On

Re: Modification Mail to be sent only once for specific column changes

Hi Michael,

 

I have a similiar issue as Karthi. My columns are 'Assignee' and 'Astatus'. 'Assignee' is a person or group lookup field 'Astatus' is a number field, default value 0.

 

When I attempt the below formula:

@or(and(empty(triggerBody()?['Assignee']?['Email']), empty(triggerBody()?['AStatus']) ),and(empty(triggerBody()?['Assignee']?['Email']), equals(int(triggerBody()?['AStatus']?['Value']),0) ) )

 

I get this error:

Unable to process template language expressions for action 'Condition' at line '1' and column '2525': 'The template language expression 'or(and(empty(triggerBody()?['Assignee']?['Email']), empty(triggerBody()?['AStatus']) ),and(empty(triggerBody()?['Assignee']?['Email']), equals(int(triggerBody()?['AStatus']?['Value']),0) ) )' cannot be evaluated because property 'Email' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

 

It seems 'Email' is the problem, I'm not sure what I should replace this value with?

 

Thanks,

 

Highlighted
karthi
Level: Powered On

Re: Modification Mail to be sent only once for specific column changes

Hi @juan_fungi

This requirement is now simplified when we use "Flow Guru" John Liu solution based on versioning. Im using John method and its working perfectly. 

Please refer the below link and let us know if you are still facing issue. 

 

http://johnliu.net/blog/2018/5/microsoft-flow-sharepoint-trigger-on-specific-fields-changed-via-sp-h...

 

Regards,

Karthik

juan_fungi
Level: Powered On

Re: Modification Mail to be sent only once for specific column changes

Good afternoon Karthi,

Thank you for pointing to this method. I do have a question. I'm not currently using the 'Title' field, not required and hidden from views.

 

In flow (SPREST-Versions) *Uri = _api/web/lists/getbytitle('ListWithHistory')/items( 'ID')/versions? $top=2

Is getbytitle going to cause trouble since these fields will be blank in my list?

 

(DataOperations - Select) *From = 'Value' doesn't exist as dynamic content. I'm not sure how to proceed?

                                                           *Map = 'VersionLabel' doesn't exist as dynamic content. Is this similiar to 'value-key-item-output'?

 

I run into the same situation in (Condition-SameTitle) where the formula is calling 'Title'.

 

I appreciate any feedback.

 

 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

thirdImage

New Flow Community Board!

Check out the new Microsoft Flow Community Blog Topic Suggestion board!

fourthImage

Australia Summit

Travel to Melbourne and network with thousands of peers!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 76 members 4,379 guests
Please welcome our newest community members: