Business Applications, dataflow, Dataflow, Dataverse, Flow, Power Automate

Getting data from a function in an on-prem SQL server via Gateway in Power Automate

Dataflow magic in Power Automate. SQL with Power Query in Power Automate. Did you know you can do this?

CloudMinded Blog

“We are screwed!”

me at the moment of technology induced panic

That was the feeling when I saw the below error messages.

The client’s setup is an on-prem SQL server exposed to the cloud via an on-prem Gateway. It works perfectly fine for exiting Dataflow integration, but this time we needed data from a function in Power Automate.

Now problem, you can use SQL server connector, right?

Almost (but yes, if you read this post or knew before).

Set back #1

You cannot use “Execute a SQL query (V2)” with an on-prem gateway connection. Not supported!

Sure, let’s then use “Execute stored procedure (V2)”.

Set back #2

Apparently, you cannot return dataset from the function. Can call it if it was just a function or procedure without return value. But if it returns data – bad luck!

Panic mode on!

Wait, but what if…

Power Query to the rescue! Again.

View original post 159 more words

Flow, No-code vs Code, Power Automate, Uncategorized

Power Automate for CDS: “what was it before we run the update?” or “bye,bye, no-code!”

The requirement: for Account(Property in our case) if related Contact(Tenant) is "un-linked", create a Note. No-code solution: none. Why? These are triggers available for CDS(current environment): CDS current environment triggers We choose Update trigger for Contact filtering by parentcustomerid ... and this is it. This is where our no-code journey ends. When Update triggers on… Continue reading Power Automate for CDS: “what was it before we run the update?” or “bye,bye, no-code!”

man is trying to roll a large rock uphill
Common Data Service, Flow, Uncategorized

How to get it right: “List records” just-first-record lookup value expression for CDS current connector

Sometimes I use my own blog to find some information which I am keeping forgetting. I am always forgetting how to do THIS so I keep it here. Problem: from the list of items retrieved via List records action for CDS current connector i would like to pick up the first item and get the… Continue reading How to get it right: “List records” just-first-record lookup value expression for CDS current connector

Common Data Service, Flow, Uncategorized

Power Automate CDS (current environment). Set Regarding.

While using CDS (current environment) connector for the Regarding property which is separately listed for each entity would you assume it's unnecessarily to prefix a dynamic property value with a type ? Well, unfortunately, you have to. The same way as for other lookups, you have to specify type prior the dynamic property. In my… Continue reading Power Automate CDS (current environment). Set Regarding.