Populating a Collection based on a SQL Query at runtime

[AK9]

Hi All,

I'm trying to populate a Collection based on a SQL query defined by the App at run time. I have tried replicating Mr. Dangs video  Type and Run a SQL Query inside PowerApps with only limited success. When I test the Flow the records I would expect to be returned are. When I added the Flow to the PowerApp the ClearCollect statement builds the correct table structure based on the Flow but with no data.

The Button in the PowerApps is showing a bad gateway error. I recreated the flow and got the same issue with the new Flow as well. One part I did struggle with was adding the ResultSet in the Body of the response. The option to do this is not there as shown in the video so I typed what I believe to be the right statement (shown in the attached screenshots). 

Any help as to where I might be going wrong would be much appreciated. 

[Mr-Dang-MSFT]

Hi @AK9,

It looks like you're troubleshooting the response step in Power Automate. My initial thoughts:

• Check that if you drilled into Table1 for the Body, make sure the schema aligns. Can you make sure the schema only includes the structure of Table1 and not ResultSets onward?
• In Power Apps, remove and re-add a flow when making changes to its response
• In your app, toggle OFF explicit column selection. File > Settings > Advanced Settings:

[AK9]

Hi @Mr-Dang-MSFT ,

Thank you for the feedback. I've been out of the office and have just managed to give this another go. The schema in the body aligns with what I would expect in the collection. 

I've made two changes:

• The one you have suggested with the 'Explicit column selection' 
• I checked the error log in Flow (definitely something I should have checked earlier) and realised I was passing the wrong text field into the SQL query causing it to fail. 

The Flow is no longer failing which is a good sign. I am now getting an error message in PowerApps saying "GetAppProcess.Run failed: The data returned in the response is invalid." GetAppProcess is the name of the flow. 

Having a look at the Flow history it looks like whilst the Flow is returning an successful response (status code 200) there is no body in the response. I'm comparing it to the screenshots @Mike8 has included in his post Return data to Powerapps issue .

Thanks!