Send email if Multi-line text column is updated

[JimmyWork]

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.

[RezaDorrani]

@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

[MarkStokes]

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

[v-bacao-msft · Accepted solution]

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:

Best Regards,

[JimmyWork]

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

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.

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]

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

[v-bacao-msft]

Hi @JimmyWork ,

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

Best Regards,

[JimmyWork]

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

[v-bacao-msft]

Hi @JimmyWork ,

Please check my previous reply.

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

Best Regards,

[JimmyWork]

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

123

[v-bacao-msft]

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

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

Regards,

[JimmyWork]

@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

[v-bacao-msft]

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,

[JimmyWork]

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

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

[v-bacao-msft]

Yeap. Just do it.

Best Regards,

[JimmyWork]

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

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

[v-bacao-msft]

Hi @JimmyWork ,

Please try to use HTML to text.

Best Regards

[JimmyWork]

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