Microsoft Flow: How to pass the SQL data table results in a markdown-formatted table into an Email notification or Approval Requests

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”

• 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”

• 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 “SQL Server” and select the action “SQL Server – Execute a SQL query”

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]

• 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”

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

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”

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

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.

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”

• 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

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:

instead of this:

On the Office 365 Outlook action configuration you need to:

• Click on “Show advanced options”.

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