Format of Approval history in sharepoint list and email

[Dustbin2021]

Hi,

I am running an approval flow with several approvers (apply to each). The flow does not stop, even if one rejects. 

For the "approval history" I have defined a string variable with the following details:

• Result of the approval request
• Who has answered the approval request
• Time of answer
• possible Comment

I want to a.) use this "approval history" in a sharepoint list and so update the item and b.) use the "approval history" in an E-Mail summarization. Here is the screenshot:

When updating the sharepoint list, the history is shown as continuous text.

In the sharepoint list in column Approval history now the result is shown. But due to the reason, that there could be several approval answers, I want to have kind of a format in there.  The same in the E-Mail summary:

How is it possible to e.g. make a section between each of the approval histories? 

Thanks a lot!

Dustbin

[tom_riha]

Hello @Dustbin2021 ,

I'm using 'Multiple lines of text' column with Rich text enabled to store the approval history. If the column supports rich text, you can use HTML tags = you can create an HTML table to store the history, like this:

I wrote a blog post with the whole process a few months ago.

[Dustbin2021]

Hello @tom_riha ,

thank you and wow, a complete blog post with exactly the solution I was looking for. 

Unfortunately I still have some troubles.

The column was already multiple lines of text and I have now activated rich text. The result looks like this:

Here is the flow details:

I did not use the "format time". Any idea? Thank you.

KR

Dustbin

[tom_riha · Accepted solution]

Hello @Dustbin2021 ,

it looks I had a mistake in the post, the column must be 'Enhanced rich text', not just rich text, I had to update it also in the blog post.

[jobb89]

Hi Tom,

I am having the same issue with the information escaping the html tags. I created the approvals history column as a site column with enhanced rich text from the outset, then added this to the library. Then I set up the flow as you've shown, except the only difference is I am updating the file metadata using update file properties (instead of update item). The content all looks to be correct and it will display as a HTML table in a HTML previewer, but in the column it shows the html tags. Do you have any idea why this might be the case? Thanks!

[tom_riha]

Hello @jobb89 ,

can you add a few screenshots? To show where it's shown correctly and where it's wrong?

[jobb89]

Hi Tom,

I've done a bit more digging, and it seems as though the enhanced rich text is not functional with document libraries. It seems as though you can edit the column to allow for enhanced rich text using SharePoint Designer, but I can't seem to work out how to do it in SPO. https://www.sharepointdiary.com/2017/02/rich-text-column-in-sharepoint-document-library.html#ixzz4ir...

I will just add the approvals details as non-HTML formatted text. When you have your column set up in your blog post with three approval entries, are these all of the approvals from one flow run, or do you have the column set up to append changes to existing text? Great blog posts too by the way, thanks!

[tom_riha]

Hello @jobb89 ,

thank you, that's what I do too, on document libraries I use it as a plain text, without the HTML tags, just using some separators between the values, e.g. Date | User | ...

On the blog it's a single flow with 3 approval steps, after each approval step it'll append to the variable and update the history column.

I almost never use 'Append changes to existing text' as Power Automate can't work with data in such column.

[jobb89]

Hi Tom,

I have worked out how to append the approvals history into one multi-line text column without the append changes functionality. I wanted a full record of document approvals history in the one entry that is updated each time the doc is approved, and this is inserted into the Word doc as a quickpart. It seems to work for the three separate approvals I've tried on the one document, and I can add screenshots later if you are interested.

[tom_riha]

Hello @jobb89 ,

yes, I'm interested to see your solution. 🙂

It might be also helpful for the future visitors of this thread.

[jobb89]

Step 1: Send an HTTP request to SharePoint. Method: GET, Uri: _api/web/lists/getbytitle('doclibrary')/items(@{outputs('Get_file_metadata')?['body/ItemId']})/versions/

Step 2: Parse JSON on HTTP request body

Step 3: Initialize string variable (ApprovalsHistory)

Step 4: Apply to each loop using d.results (body('Parse_JSON')?['d']?['results']) from the parse JSON. Then I used a compose function for the parsed content: items('Apply_to_each_5')?['Approvals_x005f_x0020_x005f_History'], where this was the content that was already in the Approval History column.

Step 5: Combine current approvals info with approvals history into one compose function. The version number is: 

add(int(first(split(triggerOutputs()?['body/{VersionNumber}'], '.'))), 1) (at this stage prior to approval, it is version 0.XX and given that it is approved, it will iterate to the next integer for the major version number; I did this because I check out the file as the unapproved version, update file properties, check in file, then set content approval status to Approve).

Step 6: Update file properties with the combined approvals information.  

My background is not in programming or computer logic, so not sure if this was the best way to approach it, but it seems to work. I haven't got around to trying it for a long document history but will test it further soon. Would be interested if it works for you and if you have any pointers on how to make the process more logical

[tom_riha]

Hello @jobb89 ,

if you already have some historical data in the version history then I think it's a good solution.

If it were a new solution then it could be a bit easier, you could store the current value from the 'Approval history' column in a variable and by the end of the flow append the new value to that variable. That would be based on the logic that the 'Approval history' already contains the history from the beginning.