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

 

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 News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,366)