Include Comment Version History in email sent from SharePoint List

[grahamr]

Hi Team,

I have used the Power Automate Template "Send email from shared mailbox when existing item is modified in SharePoint", which works great, except I have one small issue I am hoping I could please get assistance on? 

I have a "Comments" column which team members can go in and add their comments. I have turned on "Item Version History" and "Append changes to existing text" for this column. When I go into the list and the comment field it has the full date & timestamp and everything looks fine, however when it sends the email it only provides the latest comment? I would like to include the full comment history in the email, does anyone know of a way to do this?

Thanks in advance
 

[v-litu-msft · Accepted solution]

Hi @grahamr,

If you want to get history comments, you should get history versions. You could use Send HTTP request to SharePoint to get these versions of the file or item.

The URI is:

_api/web/lists/getbytitle('List')/items(itemID)/versions

Then you could initialize an array variable to hold these comments.

The following expression means to get the array of versions:

body('Send_an_HTTP_request_to_SharePoint')?['d']['results']

Then put the array into apply to each action to loop through each version, append their comments into the Comments array variable:

This is the code in the Value field: 

{
 "comments": "@item()?['OData__x005f_ModerationComments']"
}

After all, you could convert the comments variable into an HTML table to make it could be inserted into the EMail body:

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

[grahamr]

Thanks for the detailed response! I am just having a few minor issues, apologies this is prob really basic..

For the "Send an HTTP request to SharePoint" I have modified your syntax "_api/web/lists/getbytitle('List')/items(itemID)/versions" to accommodate my scenario...

My List Name = CB AU - OCDD

Column Name = Comments

Item = 12 item <-- Is this column position?

I've changed the variables as per below:
_api/web/lists/getbytitle('CB AU - OCDD')/items(12)/versions

But it's erroring with a 400, Item does not exist.

Thanks again for your help so far!

[v-litu-msft]

Hi @grahamr,

The 12 is itemID, you could get it from the trigger body.

If list name is CB AU - OCDD, the system name should be "CB AU OCDD", so the uri should be:

_api/web/lists/getbytitle('CB AU OCDD')/items(itemID)/versions

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

[grahamr]

I am getting closer and closer! apologies, this relatively new to Power Automate. I have not got it to send me an email but the body contains a lot of unstructured lookup info... It seems like versions for all rows, just not the version for comments. I think it might be wrong syntax I have put in for the Array. I've included screen shot below

For item() I literally just created an expression and put in item() - I know you had in below but wasn't sure if I had to put that in the expression? of create

{
"comments": "@item()?['OData__x005f_ModerationComments']"
}

Thanks again for your assistance

[v-litu-msft · Accepted solution]

Hi @grahamr,

Ok, so the comments column is the custom column create by yourself, not system column?

If so, please try:

{

comments": "@item()?['Comments']

}

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

[grahamr]

Correct, the column I am working with "Comments" was created by me and is not system column. So I am just trying to append all the versions of the Comments column into the email. 

When I try and input that code, it gives an error.

I also just wanted to confirm my syntax below, should this be the "Comments" variable 

or should this be the ID variable?

It didn't seem to like your initial suggestion of "If list name is CB AU - OCDD, the system name should be "CB AU OCDD" so I put back to "CB AU - OCDD"

I feel like i'm so close! 

Thanks a million for your help!

[v-litu-msft]

Hi @grahamr,

Enter the expression should in the Expression bar:

The two setting in the items should be this:

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

[grahamr]

Hi Community Support Team _ Lin Tu,

So I've gotten further and am sooo close... Basically, I am now getting an email with the version history for comments (highlighted in red) but it is including a bunch of other info I don't need. Please refer to screenshot below:

Are you able to please further assist me in getting rid of this additional info? 

Thanks for your detailed responses! I couldn't have gotten this far without you.

[v-litu-msft]

Hi @grahamr,

Use HTML to text action could convert HTML code into simple text:

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

[grahamr]

That Worked, thanks so much! Sorry just one more thing!

It is coming through like this

But I would like it to include the person, and date? Similar to this..

[v-litu-msft]

Hi @grahamr,

You could structure the Comments array in this part, add the modified time and modified by into this part, for example:

{

"Modified On": ,

"Modified By": ,

"comments":

}

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

[grahamr]

Thanks for your patience with me! SO I've made the recommended changes but it's not coming through properly.

Here is my Comments array.

And here is the output... 

I'm not sure if I have the right syntax or where I've gone wrong?

Thanks again for your assistance!

[v-litu-msft]

Hi @grahamr,

Please use the Modified by Email or Modified by DisplayName:

 And could you please share previous steps?

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

[grahamr]

Thanks for the quick response! Please find the overview of steps...

It looks like the emails are coming through with some info but not yet ideal... Thanks again for all your help! 

[v-litu-msft · Accepted solution]

Hi @grahamr,

1. Please remove the HTML to text action under the Create an HTML table action

2. Then add the Html to text action into the Apply to each action, then put item()?['Comments'] into it.

3. Structure the array as below:

{
  "ModifiedOn": "@{item()?['Modified']}",
  "ModifiedBy": "@{item()?['Editor']?['Email']}",
  "Comments": "@{body('Html_to_text')}"
}

Then insert the HTML table into email directly. 

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

[grahamr]

Unfortunately, I am now getting this error. Although, I believe it's something I am doing wrong. Please see screenshots below....

This is the error:

[v-litu-msft]

Hi @grahamr,

Could you please share an instance of the comments entity? (please shield personal information)

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

[grahamr]

Hey Buddy,

I think I've worked it out and got it working! This is what comes back, at first I was like why are some lines blank! but then I realised that I didn't always add a comment! So everytime it's modified it will note who modified it, even though they might not have made comments... an added tracking benefit.

I seriously appreciate your help with this! Prior to this I've had minimal involvement with Power Automate and during this session I've learned so much.

Thanks again

[Anonymous]

Hi @v-litu-msft ! Thank you for posting the solution. I copied your flow, and it worked well for me. The email shows comments but also empty comments. Do you know how I can suppress the empty comments so they do not show in the email?