cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AK9
Frequent Visitor

Populating a Collection based on a SQL Query at runtime

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. 

2 REPLIES 2
Mr-Dang-MSFT
Power Apps
Power Apps

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:

image.png

 

AK9
Frequent Visitor

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!

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (1,838)