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

Last Friday, while checking Twitter, I found a tweet from Jon Levesque asking if someone had a sample on how to take SQL data table results and pass a Markdown-formatted table into an Approval Request. By coincidence, two weeks ago, I was helping a colleague add a well-formatted table of results from 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 is intended to be a notification email, we will use 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 basis, so in 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 choosing 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 choosing 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 choosing 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 retrieve multiple rows from SQL Server, the Flow designer will automatically place this Variable – Append to string variable inside a loop that iterates over 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 choosing 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 address 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="bor>er:1px soli> black; bor>er-collapse: collapse;">
@{variables('lines')}
</table>
by www.>evscope.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.

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son. 

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. View all posts by Sandro Pereira