I am trying to get the results of a SQL stored procedure to appear in a data table in a PowerApp by following along with this blog post: https://flow.microsoft.com/en-us/blog/howto-upload-return-file/. Everything appears to work as expected, except that no data is being returned in the collection in my PowerApp -- the collection has the correct field headings, and I know there are results being returned by the SP (the results are appearing correctly in the outputs section when i test the Flow), but there are no data records in the collection. Any help would be appreciated!
Solved! Go to Solution.
Hi @doubles,
Based on the screenshot that you provided, I think there may be something issue with your flow's configuration.
I have made a test on my side, and don't have the issue that you mentioned. The screenshot as below:
Flow's configuration:
Within the "Response" action, set the Body field to following formula (WDL expression):
body('Execute_stored_procedure')?['ResultSets']?['Table1']
Set the Response Body JSON Schema field to following:
{ "type": "array", "items": { "type": "object", "properties": { "DepartId": { "type": "integer" }, "DepartName": { "type": "string" } }, "required": [ "DepartId", "DepartName" ] } }
The flow executes successfully as below:
More details about using expression in flow actions, please check the following blog:
https://flow.microsoft.com/en-us/blog/use-expressions-in-actions/
App's configuration:
The queryRequests collection show well as below:
Please check and see if the following blog would help in your scenario:
https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/
Please take a try to modify your flow or re-create a new flow based on above blog, then try it again, check if the issue is solved.
Note: Within Microsoft Flow, the "Execute stored procedure" action of SQL Server connector has same output schema with the "Execute a SQL query" action, so you could apply the solution within above blog into your flow.
Best regards,
Kris
Hi @doubles,
Based on the screenshot that you provided, I think there may be something issue with your flow's configuration.
I have made a test on my side, and don't have the issue that you mentioned. The screenshot as below:
Flow's configuration:
Within the "Response" action, set the Body field to following formula (WDL expression):
body('Execute_stored_procedure')?['ResultSets']?['Table1']
Set the Response Body JSON Schema field to following:
{ "type": "array", "items": { "type": "object", "properties": { "DepartId": { "type": "integer" }, "DepartName": { "type": "string" } }, "required": [ "DepartId", "DepartName" ] } }
The flow executes successfully as below:
More details about using expression in flow actions, please check the following blog:
https://flow.microsoft.com/en-us/blog/use-expressions-in-actions/
App's configuration:
The queryRequests collection show well as below:
Please check and see if the following blog would help in your scenario:
https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/
Please take a try to modify your flow or re-create a new flow based on above blog, then try it again, check if the issue is solved.
Note: Within Microsoft Flow, the "Execute stored procedure" action of SQL Server connector has same output schema with the "Execute a SQL query" action, so you could apply the solution within above blog into your flow.
Best regards,
Kris
Hello, I did exactly as Kris said, but PowerApps records collection always gets Value = True.
How do you get the table result ? Do you modify collection setting ?
Same Problem with me .. Please let me know if you find anyway to make it happen.
Hi @doubles
Below are two videos dealing with advanced queries to SQL from PowerApps (using Flow).
For SQL Azure you can use direct (native) queries:
https://youtu.be/DII10gK715I
For on-premesis SQL servers, native queries are not supported from Flow (you will get an error: operation Execute Native Sql is currently not supported using an on-prem gateway connection), so we have to use stored procedures to accomplish the same:
https://youtu.be/BAGBzI4zdww
Please let me know if you don't come right.
Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.
User | Count |
---|---|
176 | |
111 | |
86 | |
44 | |
42 |
User | Count |
---|---|
228 | |
116 | |
115 | |
72 | |
67 |