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

Leave a Reply

Your email address will not be published. Required fields are marked *

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top