cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Returning SQL data from FLOW - JSON?

Below is a screenshot from my flow. I'm using the Text output because I can't for the life of me return the data in a way that's meaningful to me. The data is clearly being returned in a way that's usable by something or someone, because the IT/developer in-house showed me this output on his end when I asked what exactly his stored procedure was returning (first screenshot). The flow testing is the second screenshot. As you can see, it's formatted for a table.

This must be an issue so simple that it's not worth explaining, because I've seen countless asks and answers and videos on how to get to this point but not how to get the data from here. I've tried collections, tables, galleries, etc. Clearly my "compose" step is redundant since input and output both match, but it's what I could use to show the formatted text.

tmmf_2-1596225605413.png

 

tmmf_4-1596225955654.png

 

 

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Returning SQL data from FLOW - JSON?

Hi @tmmf ,

Could you please share a bit more about your scenario?

Do you want to return the retrieved table records from your flow back to your canvas app?

 

If you want to return the retrieved table records from your flow back to your canvas app, I think the "Response" action could achieve your needs in your flow.

 

You could consider add a "Response" action under the "Compose" action, to send table records back to your canvas app. I have made a test on my side, please try the following flow's configuration:

2.JPG

Within the "Response" action, Body field set to following:

 

outputs('Execute_stored_procedure_(V2)')?['body'].ResultSets.Table1

 

 Response Body JSON Schema field should be set to following on your side:

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Column1": {
                "type": "string"
            },
            "Column2 r": {
                "type": "string"
            },
            "Column3": {
                "type": "string"
            },
            "Column 4": {
                "type": "string"
            },
            "Column5": {
                "type": "string"
            }
        },
        "required": [
            "Column1",
            "Column2 r",
            "Column3",
            "Column 4",
            "Column5"
        ]
    }
}

 

Note: Above Response Body JSON Schema value I provided may not be exact, you need to get the exact Response Body JSON Schema value against the "Execute stored procedure (V2)" action based on the following blog:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

 

After that, in your canvas app, you could set up a Collection to store the retrieved returned table records from your flow. You may need to execute the following formuila:

 

ClearCollect(
             RecordsCollection,
             'Your Flow Name'.Run(...)
)

 

 

Please try above solution, check if the issue is solved.

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
New Member

Re: Returning SQL data from FLOW - JSON?

Thank you for your response. I changed my flow to match yours. One point I should note is that I followed the instructions from the link you provided, and I had never copy/pasted the output correctly for the sample schema, so my flow definitely improved there.

However, I thought that would solve my problems, but here's the code in PowerApps and here's the resulting collection:

tmmf_0-1596545824695.png

tmmf_1-1596545969555.png

 

But the flow seems to be triggering and running correctly according to its run history:

tmmf_2-1596546021728.png

 

I'm at a loss as to how to proceed.

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,146)