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
Hi @Anonymous ,
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.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 @Anonymous ,
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 @Anonymous ,
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".
Hi @Anonymous ,
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 @Anonymous ,
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 @Anonymous ,
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 @Anonymous ,
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
Hi @ManishJain ,
I made it work! My flow goes as follows:
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
User | Count |
---|---|
103 | |
36 | |
29 | |
25 | |
16 |
User | Count |
---|---|
136 | |
54 | |
53 | |
36 | |
26 |