Getting a flow to retrieve data from an internal SQL Server and return it to a Collection in Power Apps

[InfinityAlarm]

I'm trying to build a Flow that will be called from Power Apps, retrieve some data from an on-prem SQL Server, and return it to Power Apps. I have a "Get rows (V2)" action, followed by a "Response" action to send the data back. Power Apps will then put that into a Collection. Here's the flow:

I can see from running the app and that triggers the flow, that it does execute the flow correctly. It gets the data that it's supposed to get, and puts it in the Response. Something about the formatting is off though, because the Collection in Power Apps just winds up with this as the collection:

Here's the raw output of the Response Body returned by the Flow:

{
   "statusCode": "200",
   "schema": {
      "type": "array",
      "items": {
         "type": "object",
         "properties": {
            "ItemInternalId": {
               "type": "string"
            },
            "PropID": {
               "type": "integer"
            },
            "PropCode": {
               "type": "string"
            },
            "PropName": {
               "type": "string"
            },
            "@odata.etag": {
               "type": "string"
            }
         }
      }
   },
   "body": {
      "@odata.context": "https://flow-apim-REDACTED.azure-apim.net/apim/sql/hexstringredacted/$metadata#datasets('SERVER NAME REDACTED%2CDATABASE NAME REDACTED')/tables('REDACTED')/items",
      "value": [
         {
            "@odata.etag": "",
            "ItemInternalId": "hex string redacted",
            "PropID": 1,
            "PropCode": "RA1",
            "PropName": "Name number 1"
         },
         {
            "@odata.etag": "",
            "ItemInternalId": "hex string redacted",
            "PropID": 2,
            "PropCode": "RB2",
            "PropName": "Name number 2"
         },
         {
            "@odata.etag": "",
            "ItemInternalId": "hex string redacted",
            "PropID": 3,
            "PropCode": "RR8",
            "PropName": "Name num 3"
         }
      ]
   }
}

How can I break that "value":[ARRAY OF JSON DATA] up in a way that I can have Power Apps put it into a Collection? It's not really even clear to me whether this is better done within the flow, or within the app.

Any help would be appreciated!

[Pstork1 · Accepted solution]

Instead of using the body dynamic content for the body of the response you should be using the value dynamic content. That would be the list of items. Another approach that would be even better would be to use the value dynamic content in a Data Select so that you can choose the specific columns you want to return. Then use the output of the Data Select in the response.

[InfinityAlarm]

Thank you for your help so far! I switched the flow over to using the dynamic Value instead of the body like you suggested and it switched it over to an array, but I'm having a different problem now.

The Power Apps code that runs the flow looks like this:

ClearCollect(PropCollection, GetPropData.Run());

but when the data gets returned from the flow, Power Apps says: "JSON parsing error, expected 'object' but got'array'". It seems like ClearCollect should work with an array, so I'm confused why it's saying it expects an object.

Here's what the Response step in the flow is sending:

[Pstork1]

That's because you originally tried to run it with the earlier response using the body. So it originally created the collection using an object with an embedded array. The easiest way to fix it is to change the name of the collection. Otherwise add a Clear(PropCollection) in the onStart of the app or OnVisible of the screen and make sure that runs once before you try to create the collection from the flow. That Clear should hopefully wipe out the old schema so it can create a new one.

[InfinityAlarm]

Thank you so much! It's working now!