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 weeks 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
#1 Azure Monitoring Platform
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.

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

  1. How would you do it in reverse? I have a FLOW that uses the REQUEST connector, and its JSON into has an array object. How can I take that array object and pass it into the SQL server (stored procedure) connection as a table parameter?

  2. Why not use the ‘Create HTML table’ action? And how to use it? I am new, so looking for help. I’m using the SQL Server Connector trigger ‘When an item is modified (v2)’ but cannot make it work.

    1. Hi Marcos, at that point ‘Create HTML table’ action was not available. and sometimes if you really want to customize your table this still the best option.
      What is the error that you are facing?

  3. Hi, I’m trying to run SQL “Execute a Stored Procedure” in a Flow that starts with a Form ResponseID.

    Basically, if they don’t have a primarykey to process a request the Form branches to let the user fill out the required fields needed to lookup against a stored procedure in SQL. Then it emails the user a primarykey to fill out the request correctly.

    It runs without error, verified in SQL Studio that it’s one unique output; however, I can’t get it to output any information in Flow to use going forward.

    Any help would be appreciated

  4. Hi,

    Thank you so much for your post. For some reason, the Is HTML property does not appear for the Send an email Outlook action.

  5. On the append variable step, I’m only getting Query Results and ResultsSets from the list of “Execute a SQL query” action tokens. I don’t get any of the values in my SQL Query

  6. Hi,

    I have found that all of the columns from the Execute SQL Query Results Sets appear only if there are no other steps performed before the Execute SQL Query Step.

    As soon as I put a step before the Execute SQL Query step, the only options I have are Query Results and ResultsSets. I get no other option showing Result Sets .

    That’s a bit of an issue, because I need to add a variable in my SQL Query, and thus need to add a step to initialize that variable before I execute the SQL Query.

    Has anyone ran into this issue, and know a workaround?

  7. Hi Sandro,

    hanks for posting this. It works well but how do I add the column headers to the table. In your case Name and Country

  8. thanks but im badly missing the for each loop- which step did you introduced as i followed above and dont get this done.

Leave a Reply

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

turbo360

Back to Top