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
