Returning SQL data from FLOW - JSON?

[Anonymous]

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.

[v-xida-msft · Accepted solution]

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:

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,

[Anonymous]

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:

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

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

[v-xida-msft]

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,

[Anonymous]

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). 

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

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.

[v-xida-msft]

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,

[Anonymous]

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!

[downshire]

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

Which looks like this in the flow

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?