Answered

Include Comment Version History in email sent from SharePoint List

(0) Share

Report

Posted on by 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

Categories:

Building flows

I have the same question (0)

All responses (20)

Answers (3)

Verified answer

v-litu-msft on at

Like (0)

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

Was this reply helpful? Yes No
grahamr 22 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

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

Was this reply helpful? Yes No
grahamr 22 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

v-litu-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No
grahamr 22 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No
grahamr 22 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No
grahamr 22 on at

Like (0)

Report

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

Was this reply helpful? Yes No