cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeppevatane
Frequent Visitor

Executing a query and sending it to email with Power Flow

Hello all,

 

I'm new to Power tools and I need some help! I'm trying to create a flow which goes something like this: 

Step 1. Execute a query that finds new contracts from our database, query below

 

SELECT
t1.Name'
,t2.SocialSecurityNo'
,t2.emailaddress'
,t2.telephone'
,t1.value'
,t1.createdon'
,t1.contractnumber

FROM .[dbo].[contract] AS t1
LEFT JOIN[dbo].[pro_contact] AS t2 ON t1.id = t2.Id
WHERE  t1.createdon >= DATEADD(HOUR,-1,GETDATE())

AND t1.contractnumber = 29201019

ORDER BY createdon desc

 

Step 2: Send the results to a specified email

 

 

As it happens, I am running an on-premise gateway, so the 15 minutes I was planning on this project turned out to be much longer. Is there a way to do this?

 

KR,

 

jeppe

13 REPLIES 13
ManishJain
Solution Sage
Solution Sage

Hi @jeppevatane ,

 

Can you please share more on querying part. Are you trying to connect to OnPrem database and using it in Online CRM version ? 

 

If so please follow the below url to connect : 

 

https://www.c-sharpcorner.com/article/how-to-connect-on-premise-sql-server-from-microsoft-flow-using...

 

https://www.mssqltips.com/sqlservertip/4918/microsoft-flow-and-sql-server-integration-example/

 

Thanks

Hi  @ManishJain and thank you for your links! We have a Dynamics-based CRM and therefore we have a OnPrem database where I extract the data from. I am trying to extract contracts that have been created within the last hour, which I will send to our partner automatically. This information will be extracted with SQL Management Studio from our crm database.

 

When I am trying to create the flow (Execute a Query), I get this error message "Gateway GetPassThroughNativeQueryMetadataAsync - this operation (Get Native Sql Metadata) is currently not supported using an on-prem gateway connection"

 

jeppe

Hi @jeppevatane ,

 

Thats great. If you follow the links for connecting to On Prem gateway and use Execute a Query action to retrieve data from database. 

 

In Order to send formatted email you can use following url :

 

https://spmaestro.com/handling-json-in-microsoft-flow/

 

Thanks

Hi @ManishJain ,

 

When I am trying to create the flow (Execute a Query), I get this error message "Gateway GetPassThroughNativeQueryMetadataAsync - this operation (Get Native Sql Metadata) is currently not supported using an on-prem gateway connection". 

 

jeppe

Hi @jeppevatane ,

 

Please share your flow screenshot.

 

Thanks

Hi @ManishJain 

 

 

Here is a picture of my flow, it immediately gives me the error message. It's in Finnish, but the SQL step is "Execute a Query".

 

 

 

jeppevatane_0-1594720630848.png

 

Hi @jeppevatane ,

 

Even I got the same message. Please try to use Get rows(V2) action instead and specify your server name , database and table name. When you click on show advanced option you have other fields which you can use to further narrow down the data you want. 

 

Thanks

Hi @ManishJain ,

 

But I can't join 2 tables in the "get rows"-step, can I?

 

jeppe

Hi @jeppevatane ,

 

With on-prem gateway currently it is not supported . Might be you can do two calls where in based on first call try to get data from second table.

 

Thanks

Hi @jeppevatane ,

 

Please follow this url : 

https://flow.microsoft.com/fr-fr/blog/powerquery-flow/

 

Thanks

Hi @ManishJain ,

 

I don't think that works for me, as I need to check results every hour and only print new contracts that have been created within the last hour. How would I do this with power query?

 

Edit. I managed to get my power query working as wanted, but sending an html table to my email seems to be the next issue. I get the following error when trying to create an html table: "The 'from' property value in the 'table' action inputs is of type 'Object'. The value must be of type 'Array'."

 

jeppe

 

Hi @jeppevatane ,

 

Can you please share screenshot of table data output. Possibly you need to do compose first and see which item to be picked for loop.

 

Thanks

jeppevatane
Frequent Visitor

Hi @ManishJain , 

 

I made it work! My flow goes as follows: 

 

jeppevatane_1-1595402242423.png

 

1st step: Recurrence once a day

2nd step: We find two relevant tables from our SQL server, filter the created column to find contracts created today and finally merge the tables together, deleting irrelevant columns

3rd step: Select Data - we parse the result with the select data-step and name the selected values

4th step: we create a table from the output of our parsing

5th step: We send the result html table to a selected email address

 

Thanks @ManishJain  for your help!!

 

jeppe

 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,551)