Type and Run a SQL Query inside PowerApps

Hello @mr-dang 

I need your help pls bro.

I have a problem, I dont know that more continue, because I want show results of my query in the label of the powerapps

In the collect, I have this formula in the buttton

But I am receiving in the collect, I see this

Just something else from Microsoft to break, FLOW the app should do its own work give the programming back to development.  This makes me want to Google!

RE: Also, I can't get "ResultSets.Table1" in flow.

I must say I found this video was an excellent introduction.., though if you want to go deeper... in explanations then I encourage you can to have a look at the following link

Return an Array from Flow to PowerApps (Response Method) 

in which Brian goes a bit deeper in explaining each and every steps with providing animations. Some cool stuff you may not want to miss. 

If you scroll down you'll notice that ResultSets.Table1 can be obtained through an expression....

---

@Anonymous wrote:

Collection comes back empty even though flow shows results.

 

Also, I can't get "ResultSets.Table1" in flow.  

---

"If it does not appear, we can use the expression box to manually enter it. Click the Expression tab and in the fx bar, type:

body('Execute_a_SQL_query').ResultSets.Table1

This means, "Return the body of the 'Execute a SQL query'* step. Drill down into ResultSets and then into Table1, which is the part you really want." 

In conclusion, I'll confess that running the Flow didn't work out as expected the 1st time I watch this video.. I thought hmmmm maybe I missed something? Indeed... after carefully reviewing each and every steps in the link provided above I managed to get it working and even more.,, Thanks for sharing Brian. This is real cool stuff.

Collection comes back empty even though flow shows results.

Also, I can't get "ResultSets.Table1" in flow.  

hello Dang

I follow the same procedures, I am calling a stored procedures and it returns data in Flow, but the collection on the PowerApps only shows one column "Value", and it changes from false to true after Flow is completed, do you know what's wrong with it ?

thanks a lot !

Thanks for the video and information. It helps a lot. Appreciate it.

Hi,

Have you tried using Execute store procedure action? Use that. It works well with it.

Convert your select query to a stored procedure with parameters. 

Hope this helps.

Thanks

Sarvesh

@mr-dang Thanks for the video! However, it did not seem to work for with my on-premise gateway to SQL. 

Hi Brian

Thanks for you input Brian.

My concern about the number of Flow runs is about the Flow runs to be consumed if a normal user refresh data from the app would use a flow run instead of the native SQL connector. It would be great if PowerApps can issue sql queries directly!

I can pass an array of ids to return in the SQL query to Flow which works, but Flow doesn't support sql queries through the on premises data gateway currently and this is required for the current scenerio.

Q1: Any idea when this will be supported?  

Because SQL queries are not supported through the data gateway yet, I can use odata filters instead of SQL queries from Flow, but the apply to each takes long to run, so ideally an odata filter should be used with one GetSQL Rows action in Flow, but I am not able to find a way to pass a list of ids to check in odata filter.  

Q2: Any ideas to pass something like WFRecordID in ['crm_helpdesk-777','crm_helpdesk-778',crm_helpdesk-779'] in Flow Odata filter? It works if I construct multiple odata statements like "WFRecordID eq 'crm_helpdesk-777' or WFRecordID eq crm_helpdesk-778 or WFRecordID eq 'crm_helpdesk-779", I can only do about 20 ids at a time because of an error "The node count limit of '100' has been exceeded. To increase the limit, set the 'MaxNodeCount' property on EnableQueryAttribute or ODataValidationSettings." I get if I pass more than 20 expressions in the odata filter. I can loop through sets of 20 ids, but that doesn't feel like the most efficient way to do it.

Thank you Brian, have a great day.

Hi Dawid,

In general, it's not a good practice to bring everything in anyway 🙂 For galleries, bringing in 16,000 records means showing the labels and images for all of them. This video is mostly a solution for times when you just need to overcome the basic limitations and it's not a frequent occurrence. 

I have been running SQL views for out of the box transformation on my original SQL tables. I changed the amount of data I needed to bring in and run all aggregate functions in Views.

For your situation with ids, I'd try to send the list of ids to Flow as a string of JSON. Parse the string so that it is formatted as JSON. Then run an Apply to Each action that appends records to an array variable. Send that array back to PowerApps via Request - Response.

This way, you do not need to use ForAll in PowerApps and run the flow multiple times--you effectively tell Flow to do it instead. Let me know if I understood your situation correctly.