cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimmyWork
Level 8

Send email if Multi-line text column is updated

Hi

 

I have a SharePoint list i use the following columns.

"Comments column" Multi-line text column

"Assigned To column" Person column
"Requester Column" Created by column

 

Now what im tryingt to achive is the following.

Send an email to user in "Assigned to" IF "Requester" types something in the "Comments column"

Send an email to "Requester" IF "Assigned To" types something in the "Comments column"

Do not send email if nothing changes or if the "Comments column" is empty.

 

I solved that before when i had "Append Text" activated i could then check Comments is not equal to CommentsData, and then run a update item with the new comments. But if i dont have Append Text i cant use CommentsData.

 

And i cant use Append Text anymore. Anyone have a solution or workaround to this. I need someway to check for changes in the Comments Multi-lin text column but right now i dont know how to without Append Text.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Send email if Multi-line text column is updated

 

Hi @JimmyWork ,

 

Please refer to the method mentioned in this blog to monitor whether the specified field value has changed.

https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Check-for-a-Change-in-a-SharePoint...

The premise is that you need to set the list to Create a version each time you edit an item in this list.

Determine if it has changed by comparing the value of the corresponding field with the previous version.

Image reference:

68.PNG

 

Best Regards,

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

View solution in original post

19 REPLIES 19
Super User
Super User

Re: Send email if Multi-line text column is updated

@JimmyWork 

 

do you have versioning enabled

If yes - you can query rest api from Flow get previous version of item and compare the comments with the new comments

 

If versioning is disabled, then the comments field data will be lost and there is no way to know what was modified

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

Super User
Super User

Re: Send email if Multi-line text column is updated

I totally support the approach of requesting the previous version of the item and comparing the contents of the comments box. To see if Assigned To or Requester made the change you simply see who the Modified By was on the item that triggered the flow.

2 alternative approaches:

Have an OriginalComments field on the list and if that is different to the Comments field you send your update and then update the list item replacing OriginalComments with the new comment value. This will re-trigger the Flow to make sure you quickly check if the fields match and cancel if they do match. There might be a way to use the new trigger filters to filter out unchanged updates. I’ve not tried that yet.

The other approach is to use a second list for comments and have a lookup to the item the comment refers to. It depends how you are displaying your item and comments to the user.

- Mark
Community Support Team
Community Support Team

Re: Send email if Multi-line text column is updated

 

Hi @JimmyWork ,

 

Please refer to the method mentioned in this blog to monitor whether the specified field value has changed.

https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Check-for-a-Change-in-a-SharePoint...

The premise is that you need to set the list to Create a version each time you edit an item in this list.

Determine if it has changed by comparing the value of the corresponding field with the previous version.

Image reference:

68.PNG

 

Best Regards,

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

View solution in original post

JimmyWork
Level 8

Re: Send email if Multi-line text column is updated

@v-bacao-msft @RezaDorrani  (Reza this is actually from the powerapps video you created about appending text, so you know how i built the powerapps form Smiley Happy

 

So i tried to do it like this.

And then i have a condition first(body('SelectVersions'))?['CaseComments'] is equal to 

last(body('SelectVersions'))?['CaseComments']
IF yes = Terminate
IF No = Condition 2
 
And i get the fields changes and version but the flow will just terminate
The data i get from the comments field is like this
Version 2.0
<div class="ExternalClass318C146AA333469A98621437E401D445">User18/8/2019 9&#58;29 AM <p>New Comment2</p>
 
Old Version 1.0
<div class="ExternalClass318C146AA333469A98621437E401D445">User18/8/2019 9&#58;29 AM <p>New Comment2</p>
 
Same thing even if it's 2 versions.
2019-08-08 09_34_57-Edit your flow _ Microsoft Flow.png
No difference from the versions even if i post new comment.
 
[
{
"VersionLabel": "47.0",
"Title": "Test",
"ID": 2,
"AssignedTo": null,
"CaseComments": "<div class=\"ExternalClassD94D3F5CC7C446AF89DE2922F4AC6D6E\">User1 8/8/2019 9&#58;58 AM <p>New comment2</p>\
},
{
"VersionLabel": "46.0",
"Title": "Test",
"ID": 2,
"AssignedTo": null,
"CaseComments": "<div class=\"ExternalClassD94D3F5CC7C446AF89DE2922F4AC6D6E\">User1 8/8/2019 9&#58;58 AM <p>New comment2</p>\
}
]
 
JimmyWork
Level 8

Re: Send email if Multi-line text column is updated

@v-bacao-msft  seems my last post got deleted.

I got it to work i think i need to do some more testing but i got the “Invalid type. Expected String but got Null.”

Had to add ["string","null"] for it to accept it.

 

Because of that the Comment column is a string if i try to send an email with only the latest comments i now get all the comments instead.

 

And im trying to get the conditions right.

First condition is to check if comments are the same as old version. If it is the same terminate flow, if not the same.

New condition.

Requester(created by) is not equal to AssignedTo

If YES

New condition

Modified by is equal to AssignedTo

IF YES

Send email to Requester 

IF NO

Send email to AssignedTo

 

Does this sound correct?

 

Community Support Team
Community Support Team

Re: Send email if Multi-line text column is updated

 

Hi @JimmyWork ,

 

It seems that your logic is correct. Have you created a similar Flow for testing? Any questions?

 

Best Regards,

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

Re: Send email if Multi-line text column is updated

@v-bacao-msft I have one for testing now trying to get everything right.

 

But when a new item is created the flow now fails and i get this error.

InvalidTemplate. Unable to process template language expressions in action 'Initialize_variable' inputs at line '1' and column '2646': 'The template language expression 'body('Parse_JSON')?['d']?['results'][1]['CaseComments']' cannot be evaluated because array index '1' is outside bounds (0, 0) of array. Please see https://aka.ms/logicexpressions for usage details.'.

Community Support Team
Community Support Team

Re: Send email if Multi-line text column is updated

 

Hi @JimmyWork ,

 

Please check my previous reply.

94.PNG

You also need to use Condition to determine Modified and Created.

 

Best Regards,

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

Re: Send email if Multi-line text column is updated

@v-bacao-msft I have version history active and i did like the link your posted.

 

I have a couple of issues it seems.

 

- Flow fails if a new item is created, i guess because there is no versions to compare to?
Error message in Flow.
InvalidTemplate. Unable to process template language expressions in action 'Initialize_variable' inputs at line '1' and column '2646': 'The template language expression 'body('Parse_JSON')?['d']?['results'][1]['CaseComments']' cannot be evaluated because array index '1' is outside bounds (0, 0) of array. Please see https://aka.ms/logicexpressions for usage details.'.

 

- If i edit the item it send email, i just want it to send email if Case Comments column have been changed.

My complete flow below.

 

Im not sure my conditions are correct, when i edited the assigned to column an email was sent to requester(created by)

 

2019-08-09 08_36_31-Edit your flow _ Microsoft Flow.png12019-08-09 08_40_33-Edit your flow _ Microsoft Flow.png22019-08-09 08_41_12-Edit your flow _ Microsoft Flow.png3

Community Support Team
Community Support Team

Re: Send email if Multi-line text column is updated

 

You need to configure Condition under the trigger to determine if Created is equal to Modified.

96.PNG

And the following actions are all configured in the IF no branch.

 

Regards,

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

Re: Send email if Multi-line text column is updated

@v-bacao-msft But if i put Created by is equal to Modifed by

Then when the created by types a comment the flow will close and not move forward? Because the Created by will modfie the item when typing in new comments

Community Support Team
Community Support Team

Re: Send email if Multi-line text column is updated

 

Hi @JimmyWork ,

 

You could create an action that you expect to execute in If yes. Please change the configuration according to the actual situation.

 

Best Regards,

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

Re: Send email if Multi-line text column is updated

@v-bacao-msft Thank you very much for answering me i really appriciate it.

My skills in flow is limited as im new to do and have no programming background.

 

The flow is trigged only once on assigned and not when its changed.

 

Im not really sure how to do this. "You could create an action that you expect to execute in If yes. Please change the configuration according to the actual situation."

 

And i do understand why it fails when a new item is created because versions is 1 and it has nothing to compare to but how do i fix this?

 

Im sorry if i ask so many questions at the same time im very thankful for lal the help i get

JimmyWork
Level 8

Re: Send email if Multi-line text column is updated

@v-bacao-msft I get it now, dont know why i over complicate things when i read a post.

So i just do the the Created by is equal to Modified by.

 

Seems that this works i have not fully tested it but im i thinking correct?

After When an Item is created or modified

Condition int(triggerBody()?['{VersionNumber}']) is equal to 1

IF YES = Terminate

IF NO - run the rest of my flow

 

Does this sound right?

Checks is it version 1 if it version 1 terminate if its version 2 or above it will run the rest of my flow.

 

 

Community Support Team
Community Support Team

Re: Send email if Multi-line text column is updated

 

Yeap. Just do it.Smiley LOL

 

Best Regards,

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

Re: Send email if Multi-line text column is updated

@v-bacao-msft Thanks for all the help, really appriciate it.

 

May i ask if it's possible to send an email to the Created by or Last modified by, that only cointains the latest comment. Now when i pull the data i get all the comments that have been inputed. I just want to display the latest one.

 

If i use the value from the Comments column i will get everything that it contains.

JimmyWork
Level 8

Re: Send email if Multi-line text column is updated

@v-bacao-msft @RezaDorrani 

Sorry for asking this but im not solving in flow how i can send an email only containing the latest added value to the "Comments column"

 

If i use the "Comments" from dynamic content it will display all comments entered. I only want to display the latest added one. I tried using a Create HTML table and so on but im not able to get it right.

 

If a do a intitalize variable i get the following results from the "Comments column"

I would only like to display the latest comment without the <div class="ExternalClass6FF39C346AA54C2AB2D7D1B97FEF1A82">

 

So how can i get the comments without the extra formatting and add this to the email im sending out?

 

Results

<div class="ExternalClass6FF39C346AA54C2AB2D7D1B97FEF1A82">365 User 8/11/2019 2&#58;59 PM <p>Test Success Message</p>
<div class="ExternalClass133B3F6ED51649558D1B77BE201EC18E">365 User 8/11/2019 2&#58;40 PM <p>Requester comments on the case.</p>
<div class="ExternalClassC3BB53BD6BDB447C82B54A83B32CD543">EPN SYSTEMS 8/11/2019 2&#58;38 PM <p>Assigned To comments on the item/ticket.</p>
<div class="ExternalClass8E461C1B099843148DF67C3CEFBE9DAC">365 User 8/11/2019 2&#58;25 PM <p>Comment without anyone assigned</p>
</div></div></div></div>

Community Support Team
Community Support Team

Re: Send email if Multi-line text column is updated

 

Hi @JimmyWork ,

 

Please try to use HTML to text.

 

Best Regards

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

Re: Send email if Multi-line text column is updated

@v-bacao-msft Thanks

But it's not an issue with formatting it is displayed in text correctly but it will display all comments ever made in the email body. I just want to display the latest comment so how do i make a variable that stores the latest comment made and use that in email body i send.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

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!

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: 73 members 3,544 guests
Please welcome our newest community members: