Type and Run a SQL Query inside PowerApps 01-23-2019 21:35 PM

[mr-dang]

Knowing how to return an array to PowerApps will make many more connectors useful to you. The existing "Execute a SQL query" action in Flow is now capable of sending its resulting array back into the app where it was triggered. This allows you to leverage the SQL language from within your apps and retrieve thousands upon thousands of records. To put this into perspective: you can type a query to filter objects in a gallery or dropdown menu in addition to the current capabilities in PowerApps.

watch?v=K_H5r0nzwy4

[DavesTechTips]

Very nice Mr Dang.

I have a situation where the SQL table has more than 2000 records, and the only option I can see it to filter the data according to a list of ids from another collection. The problem is that the list of ids that should be used is not in a table in SQL so the 'in' operator is not delegatable to SQL, which means that only 2000 records become eligable for the filter operation.

You solution above will do the trick to return more than 2000 records, I am just concerned about the number of flow runs that would be required if this is the primary way to populate the PowerApp gallery with data, instead of using the SQL connector directly from PowerApps.

Any thoughts on the above will be welcome!

[mr-dang]

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.

[DavesTechTips]

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.

[Anonymous]

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

[sarveshshinde15]

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

[sarveshshinde15]

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

[manlamcheng]

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 !

[Anonymous]

Collection comes back empty even though flow shows results.

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

[MawashiKid]

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.