cancel
Showing results for 
Search instead for 
Did you mean: 

Create a Flow Action to Return SQL Server Arrays

As of February HTTP connectors became Premium in Flow. However, I am probably not the only person that had a misunderstanding that using the "Response" action with Flow SQL Server actions with Azure SQL Server. Apparently the "Response" action is considered "part" of the "HTTP Connector" and is therefore Premium. Unfortunately, my client's tenant and my company's tenant didn't start to get a licensing popup until about 2 weeks ago so I had no idea that the "Response" action was Premium.

I think my misunderstanding was reasonable given that:

  • Azure SQL Server is a standard connector
  • Microsoft’s recommendation on how to return Flow SQL Server action array results to PowerApps is to use the “Response” action
  • Therefore, I assumed the “Response” action in this instance was “Standard”

In the meantime I have come up with a partial workaround. I can rewrite SQL Server queries and stored procedures in Flow so they will return a list of unique keys via the “Respond to PowerApps” action (which allows only one value or a list of one value, so useless for array purposes). I’ll put these into a collection into PowerApps and then can use the ForAll and/or Filter commands to retrieve the filtered array of SQL Server items I want. However, this won’t solve some of the problems that PowerApps has with understanding SQL Server data types, various delegation issues or complex key situations.

 

Therefore, I suggest that Microsoft create a new, non-premium action in Flow that will return an array of results from Azure SQL Server actions to PowerApps, similar to how "Response" does now. I think this makes sense because returning results from Azure SQL Server has nothing to do with HTTP, so the current "Response" action is actually an inappropriate way to return Azure SQL Server data to PowerApps.

 

Status: New
Comments
Level: Powered On

Thanks @kbirstein1  for adding this idea here as requested.

thanks @Stephen for advise..

cheers,

Sonal

Level 10

Plus one please. We need to be able to more dynamically return data to PowerApps from Flow and the HTTP response is ideal for this. It does feel like the response should not be a premium connector as it can only respond to whatever called (triggered) it. If the trigger was premium, then that sorts out the licensing already, while if the trigger wasn't premium (as is the case for PowerApps), then the response also shouldn't be.

 

Below are two videos showing examples of how we use the HTTP response to compliment PowerApps and Flow.
For SQL Azure you can use direct (native) queries:
https://youtu.be/DII10gK715I

 

For on-premesis SQL servers, native queries are not supported from Flow (you will get an error: operation Execute Native Sql is currently not supported using an on-prem gateway connection), so we have to use stored procedures to accomplish the same:
https://youtu.be/BAGBzI4zdww

 

Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1