cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @Anonymous ,

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.

View solution in original post

7 REPLIES 7
v-xida-msft
Community Support
Community Support

Hi @Anonymous ,

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.
Anonymous
Not applicable

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.

 

Hi @Anonymous ,

Regarding the screenshot that you mentioned, I think there is something wrong with your flow's configuration. Please consider share a screenshot about your flow's configuration.

 

Have you added the "Response" action in your flow's configuration. If you configured your flow as I mentioned above, when you execute the ClearCollect(RecordsCollection, 'YourFlowName'.Run()) formula, the RecordsCollection should be populated with proper table records returned from your flow.

 

The sample data you need to generate JSON Schema should be the "Table1" array part from the "Execute stored procedure (V2)" action in your run history.

 

Please do not add a "Respond to a PowerApp or flow" action in your flow to return data back to your app, this action could not return table data back to PowerApps app.

 

Please consider remove the flow connection from your canvas app, and then re-create a new connection to your flow in your app, then try it again, check if the issue is fixed.

 

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.
Anonymous
Not applicable

I've formatted my flow and my call to match yours and here are the results. I still can't access any of the information in the table (as it doesn't recognize that there is any sort of table). 

tmmf_0-1596728405132.png

tmmf_1-1596728425829.png

tmmf_2-1596728499866.png

 

Here is the data from the flow. The successful run and the layout/code/format.

tmmf_3-1596728803686.pngtmmf_4-1596728890578.png

 

One problem I might note is that I'm not the flow / app "owner." My supervisor had to make both and share editing/co-owner rights with my account and this prevents me from adding the flow to the app - I am unable to delete and re-add (or "recreate a new connection") in the app because of this.

 

I sincerely appreciate your help with this matter. It feels like everything should be working, but galleries and data tables display nothing and the only return is "return" instead of any of the desired fields that I KNOW the stored procedure is returning.

 

 

Hi @Anonymous ,

Yeah, you are right. It is the strange where the "return" column is returned rather than a Table records in your canvas app.

 

Regarding the screenshot of your flow's run history, I think your flow's configuration is correct. I think this issue is related to the flow  integration in your canvas app.

 

You must make sure that the canvas app has added proper flow connection inside it. In addition, if you made some changes to the flow, the changes may not take effect in your canvas app automatically.

 

Please consider ask the owner of the canvas app/flow, to edit this canvas app, then remove the flow connection from this shared app, then re-create a new connection to this modified flow, then re-publish it again, 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.
Anonymous
Not applicable

Just want to say that removing and re-adding the flow to the canvas app fixed the issues. The flow/app owner (with permissions for the connections) returned from vacation today and the collection showed the correct data immediately.

 

I've "accepted" the post with the flow configuration that I followed to get it to work. Thanks for all your help and patience!

Hello, I hope someone might be able to help me. I have been trying to make this work now for some time, finally admitted defeat.

 

I am trying to get some results from a SQL proc into a collection for use in a canvas app.

 

I can request the data from the proc

 

downshire_0-1668464289094.png

 

Which looks like this in the flow

 

downshire_1-1668464421698.png

I then used the response action with

body('Execute_a_SQL_query').ResultSets.Table1

I built the JSON schema from results under Table1 which created

 

{
    "type""object",
    "properties": {
        "StaffID": {
            "type""integer"
        },
        "firstName": {
            "type""string"
        },
        "surName": {
            "type""string"
        },
        "shortName": {
            "type""string"
        },
        "staffMobile": {
            "type""string"
        },
        "staffEmail": {
            "type""string"
        },
        "staffDepot": {
            "type""integer"
        },
        "staffVehicle": {},
        "UserFuelCard": {}
    }
}
 
When I run this through my power app I only get the headers coming through into my collection. What am I doing wrong?
 
 

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 (4,626)