Answered

Procedure returns JSON in table's column... how to set as PowerApps Object?

Like (0) Share

Report

Posted on 20 May 2022 14:25:32 by Community Power Pla...

In previous engagement, I used REST API connector to run Stored Procedure (SP) in the database. The PowerApps connectors were OAS file and PowerApps created the object/gallery automatically... easy

I my current engagement, the client does not allow me to setup an API Server. I must use PowerAutomate to connect the a on-premise SQL server.

I am less familiar with PowerAutomate,

The SP returns a recordset with some columns containing scalar value, on other containing JSON string. The JSON can be a complex object, containing attributes, embedded objects and/or arrays.

I can get PowerAutomate to call the SP, and I see the result.

My challenge is to make the result visible to PowerApps.

I tried the "Response" action as described in many tutorial. It works fine to create a JSON array with the scalar column, but the JSON column is seen as a string by PowerApps, event If I described its schema in the Response operation.

This is the example of the Schema I inserted in the Response operation for a very simple test SP:

{
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "ResultCode": {
 "type": "integer"
 },
 "ResultMessage": {
 "type": "string"
 },
 "FromDatabase": {
 "type": "string"
 },
 "TransactionId": {
 "type": "integer"
 },
 "Payload": {
 "type": "object",
 "properties": {
 "echo": {
 "type": "string"
 }
 }
 }
 },
 "required": [
 "ResultCode",
 "ResultMessage",
 "FromDatabase",
 "TransactionId",
 "Payload"
 ]
 }
}

ResultCode, ResultMessage,FromDatabase and TransactionID are scalar columns in the SP RecordSet, Payload contain the JSON object (very simple here, but this is just a test SP to learn the mechanic, in real SP, will be way more complex).

As I also write the SP, I do have some flexibility. I could, for exemple, include the scalar columns into the JSON and thus have a recordset of only one column. If the SP returns many records, I could also put them all in the JSON, thus always having a recordset of one record, one column.

I am open to suggestions.

Thanks

Categories:

Power Platform Build Tools

All responses (2)

Answers (1)

Verified answer

Community Power Pla... on 23 May 2022 at 14:54:43

Like (0)

Report

Re: Procedure returns JSON in table's column... how to set as PowerApps Object?

Thanks cchannon,

Your reply is useful, but not sufficient. It did helped me, but I did not yet solve the entire issue.

This is what I did to make it work:
1- Change the Stored Procedure to return a single row, single column recordset. The column (ResultJson) contains a JSON object,
2- In the flow, after calling the SQL Stored Procedure Action, I added a these steps:
- Compose with this formula: first(outputs('x')?['body/resultsets/Table1']).ResultJson, which create a single "variable" with the first record's column ResultJson
- Parse JSON which convert the text output into a JSON object
- Response which put the JSON object into the Body of the response.

Test the Flow, works fine

Now when I call the Flow from PowerApps, I can Set() a record variable to the procedure output.

Thanks again for your help.
cchannon 4,702 Super User 2025 Season 1 on 22 May 2022 at 00:37:39

Like (0)

Report

Re: Procedure returns JSON in table's column... how to set as PowerApps Object?

Power Automate has a native Parse Json action that you can use to make this easier. You just feed it an example JSON document and it uses that to convert your real JSON to objects.

How to use Parse JSON action in Power Automate - Microsoft Tech Community