cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Power Participant
Power Participant

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
Community Support

 

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
Dual Super User II
Dual Super User II

@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

 

Skilled Sharer
Skilled Sharer

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
Community Support

 

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

@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.
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>\
}
]
 

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

 

 

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.

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

 

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.

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

 

112233

 

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.

@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

 

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.

@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

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

 

 

 

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.

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

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

 

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.

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

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (82,859)