Problem
Let’s assume, that in your back-end data source, you have a field which contains delimiter separated data. E.g. ‘,’ separated string or ‘;’ separated string. It might be tabular data, which is stored in this format in your database.
Now, if you want to read this data and send as nice html table, through email to your end users, how would you do that?
Solution
In my scenario, I have created a repeating table using PowerApps, to add more than one record and stored that data as concatenated string, in Multiple line text column in SharePoint list.
I can use Microsoft FLOW to convert such delimited string into a HTML table. Let’s see how to do that. There could be two solutions to this problem.
First
Convert delimited string to JSON formatted string, then use Parse JSON action and use Create HTML table action. I will explain this solution in another article.
Second
Using Compose action, we will replace delimiters with html tags like <tr>, <td> and generate an html table and send that through email. In this article, we are going see the second solution.
e.g. this is your concatenated or delimited string – with ; used for column values separation and | used for rows separation.
- 123;HP ProBook;5;50000;250000;01/10/2019;Sarvesh Shinde|222;Asus Notebook;2;35000;70000;05/09/2019;Mike|345;Microsoft Surface;4;90000;360000;08/07/2019;Steve|546;iPad 4;2;20000;40000;04/10/2019;Bill|
Go to Microsoft FLOW >> Add required trigger in your flow >> make sure you have got the data from data source. In my case, I have Used “Get Item” action, to get my SharePoint list item.
Add 1st Compose action >> you will find this action under Data composition section,
We will use Replace function to replace ; with <td></td>. If you want to add any styling css, then you can add that too.
Here is the expression I have used,
- replace(body('Get_item')?['QnA'],';','</td><td style="border:solid #000;border-width:0 1px 1px 0;">')
Add 2nd compose action >> we will replace | with </td></tr><tr>. Again, if you want you can add styling css for showing borders to your html table.
Here is the expression I have used,
- replace(outputs('Compose_-_Replace_delimiter_with_td_tags'),'|','</td></tr><tr><td style="border:solid #000;border-width:0 1px 1px 0;">')
Add 3rd compose action >> we will use Substring function >> to remove extra <tr> at the end, which gets added in the above action.
Here is the expression I have used,
- substring(outputs('Compose_-_Replace_row_delimiter_with_td_and_tr_tags'),0, sub(length(outputs('Compose_-_Replace_row_delimiter_with_td_and_tr_tags')),60))
Add last and 4th compose action >> we will use Concat function >> to concat output of above action with <table><tr><td> at the beginning and </table> at the end,
Here is the expression I have used,
- concat('<table cellpadding="10"><th style="border:solid #000;border-width:0 1px 1px 0;">Purchase ID</th><th style="border:solid #000;border-width:0 1px 1px 0;">Item Name</th><th style="border:solid #000;border-width:0 1px 1px 0;">Quantity</th><th style="border:solid #000;border-width:0 1px 1px 0;">Unit Price</th><th style="border:solid #000;border-width:0 1px 1px 0;">Sub total</th><th style="border:solid #000;border-width:0 1px 1px 0;">Date</th><th style="border:solid #000;border-width:0 1px 1px 0;">Requested by</th><tr><td style="border:solid #000;border-width:0 1px 1px 0;">',outputs('Compose_-_Remove_extra_tr_td_from_end'),'</table>')
Now add Send email action >> use output of above action and add it in body of email.
Run the flow and check if you get nice html table of your delimited string.
That’s how you should receive the email, with nice html table body.
Summary
Depending on how you have stored your data, in your data source, you can use the replace-substring-concat logic to generate nice table html content. I hope this will help. Thanks for reading.