This post was originally published here

Last Friday while checking the Twitter I found a twitter from Jon Levesque asking if someone had a sample on how to took SQL data table results and passed a markdown-formatted table into an Approval Request. By coincidence, two week ago, I was helping a colleague of mine doing something similar, in that case, it was just adding a well formatted table with the results of an SQL expression to a notification email. Two different but similar scenarios.

To be able to create this simple proof-of-concept you should:

  • Access the Flow portal: https://flow.microsoft.com/ and sign in with your account
  • In flow.microsoft.com, select “My flows” in the top navigation bar, and then select “+ Create from blank” to create a Flow from scratch.
  • On the “Create a flow from blank”, select “Create from blank” to start the process of creating an empty Flow

Because this is a simple POC and it supposed to be a notification email we will be using the Scheduler trigger to start the Flow from time to time. For that you need:

  • On the Flow designer, on the Trigger event enter “Schedule” and select the option “Schedule – Recurrence”

Microsoft Flow: markdown-formatted table - Schedule – Recurrence

  • For this POC, we want to receive a notification on a daily, so on the Schedule Trigger configuration set the:
    • “Interval” property as “1”
    • And the “Frequency” property has “Day”

Microsoft Flow: markdown-formatted table - Schedule – Recurrence configuration

  • Add the next step by clicking the “+New step” button and then choose the “Add an action” option

Microsoft Flow: markdown-formatted table - Add action

  • On the “Choose an action” window, enter “SQL Server” and select the action “SQL Server – Execute a SQL query”

Microsoft Flow: markdown-formatted table - SQL Server action

Note:
This can be a different action like, get rows or execute a stored procedure.
    • After you connect to the proper SQL Server and database, on the Execute a SQL query action configuration:
      • On the “query” property, type
SELECT [Id],
       [Name],
       [Age],
       [Nacionality]
FROM [dbo].[Persons]

Microsoft Flow: markdown-formatted table - SQL Server action configuration

  • Add the next step by clicking the “+New step” button and then choose the “Add an action” option
  • On the “Choose an action” window, enter “Variables” and select the action “Variables – Initialize variable”

Microsoft Flow: markdown-formatted table - Add Variable

    • On the Variable action configuration:
      • On the “Name” property, type “lines”
      • On the “Type” property, specify the type as “String”
      • On the “Value” property leave it blank

Microsoft Flow: markdown-formatted table - Add Variable configuration

Note:
Initialize variable needs to be performed on Top Level, in other words, outside any cycle operation (Do Until or For Each). Initialize variable inside cycles are not permitted.
  • Add the next step by clicking the “+New step” button and then choose the “Add an action” option
  • On the “Choose an action” window, enter “Variables” and select the action “Variables – Append to string variable”

Microsoft Flow: markdown-formatted table - Append variable action

    • On the Variable action configuration:
      • On the “Name” property, select “lines” variable
      • On the “Value” property type
<tr>
<td style="border:1px solid black; border-collapse: collapse;">@{items('Apply_to_each')?['Name']}</td>
<td style="border:1px solid black; border-collapse: collapse;">@{items('Apply_to_each')?['Nacionality']}</td>
</tr>

Microsoft Flow: markdown-formatted table - Append variable action configuration

Note:
@{items(‘Apply_to_each’)?[‘Name’]} and @{items(‘Apply_to_each’)?[‘Nacionality’]} are inserted and selected from the list of “Execute a SQL query” action tokens
  • Because we can have multiple rows retrieved from SQL Server, the Flow designer will automatically place this “Variable – Append to string variable” inside a Loop that will iterate thru each SQL row.

Microsoft Flow: markdown-formatted table - Append variable inside loop

Now that we have our SQL rows partially well formatted in a markdown-formatted table, what we need to do is create our notification/Approval Request email. To do that we need:

  • Add the next step by clicking the “+New step” button and then choose the “Add an action” option
  • On the “Choose an action” window, enter “Office 365 Outlook” and select the action “Office 365 Outlook – Send an email”

Microsoft Flow: markdown-formatted table - outlook action

  • On the Office 365 Outlook action configuration:
    • On the “To” property, type the email for which you want to send the email
    • On the “Subject” property, type the subject of the email
    • On the “Body” property, type:
<h1>Summary</h1>
<table style="border:1px solid black; border-collapse: collapse;">
@{variables('lines')}
</table>
by www.devscope.net

Microsoft Flow: markdown-formatted table - outlook action configuration

Note:
@{variables(‘lines’)} is inserted and selected from the list of “Variables” tokens

Now the final trick for you to receive this type of email:

Microsoft Flow: markdown-formatted table - outlook email final result

instead of this:

Microsoft Flow: markdown-formatted table - outlook email initial result

On the Office 365 Outlook action configuration you need to:

  • Click on “Show advanced options”.

Microsoft Flow: markdown-formatted table - outlook advance options

  • And on the “Show advanced options”:
    • On the “Is HTML” property, select “Yes”

Microsoft Flow: markdown-formatted table - outlook advance options HTML

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.