cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichelH
Advocate V
Advocate V

JSON function using JSONFormat.IndentFour not working as suggested in documentation

I read the JSONFormat.IndentFour option should be able to format a json string, giving it proper indenting so it becomes readable.

 

The example I found here uses a label to display the formated json text.

It even shows a coloured formatted JSON output. (could it be real?)

 

When I try to do the same, I do not see this result unfortunately.

 

My plain JSON text, displayed in a label looks like this:

{"name":"c43187c3-3693-4ac9-844f-08dfefd8f09d","id":"/providers/Microsoft.Flow/....

After using the JSON function:

Set( OutPutTxt , JSON( MyJSONtxt ,JSONFormat.IndentFour ));

the output in the label looks like this:

"{\"name\":\"c43187c3-3693-4ac9-844f-08dfefd8f09d\",\"id\":\"/providers/Microsoft.Flow/....

 

Has anyone tried? Does anyone see where I go wrong here?

 

I've tried the same in an input field, and a rich text field.

I see the same results.

 

Thanks for any feedback.

21 REPLIES 21
Pstork1
Dual Super User III
Dual Super User III

Try loading it into a Collection instead of a Variable.  I think that's your problem. Even if its only one value it should be coming back from Flow in the form of a record as JSON.  



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi @Pstork1 ,

 

I will try that, but I have my doubts.

This is what it looks like.

{"name":"c43187c3-3693-4ac9-844f-08dfefd8f09d","id":"/providers/Microsoft.Flow/environments/Default-e3cf3c98-a978-465f-8254-9d541eeea73c/flows/c43187c3-3693-4ac9-844f-08dfefd8f09d","type":"Microsoft.Flow/environments/flows","properties":{"apiId":"/providers/Microsoft.PowerApps/apis/shared_logicflows","displayName":"Button -> Get row","userType":"Owner","definition":{"$schema":"https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#","contentVersion":"1.0.0.0","parameters":{"$connections":{"defaultValue":{},"type":"Object"},"$authentication":{"defaultValue":{},"type":"SecureObject"}},"triggers":{"manual":{"type":"Request","kind":"Button","inputs":{"schema":{"type":"object","required":[],"properties":{}}}}},"actions":{"Get_row":{"runAfter":{},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"GetItem"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sql']['connectionId']"}},"method":"get","path":"/datasets/default/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[MH_SAPdata_updates]'))}/items/@{encodeURIComponent(encodeURIComponent('121029'))}","authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}}},"Initialize_variable":{"runAfter":{"Get_row":["Succeeded"]},"type":"InitializeVariable","inputs":{"variables":[{"name":"com","type":"String","value":"@{body('Get_row')?['Comment']};@{body('Get_row')?['Reason']};@{body('Get_row')?['Status']}"}]}}},"outputs":{}},"triggerSchema":{"type":"object","required":[],"properties":{}},"state":"Started","connectionReferences":[{"connectionName":"0cc11648080d441ead1904e034b305bb","displayName":"SQL Server","id":"/providers/Microsoft.PowerApps/apis/shared_sql"}],"installedConnectionReferences":{"shared_sql":{"connectionName":"0cc11648080d441ead1904e034b305bb","apiDefinition":{"name":"shared_sql","id":"/providers/Microsoft.PowerApps/apis/shared_sql","type":"/providers/Microsoft.PowerApps/apis","properties":{"displayName":"SQL Server","iconUri":"https://connectoricons-prod.azureedge.net/sql/icon_1.0.1306.1889.png","purpose":"NotSpecified","connectionParameters":{"server":{"type":"string","uiDefinition":{"displayName":"SQL server name","description":"SQL server name","tooltip":"SQL server name","constraints":{"tabIndex":1,"required":"true","capability":["gateway","cloud"]}}},"database":{"type":"string","uiDefinition":{"displayName":"SQL database name","description":"SQL database name","tooltip":"SQL database name","constraints":{"tabIndex":2,"required":"true","capability":["gateway","cloud"]}}},"authType":{"type":"string","allowedValues":[{"value":"windows"},{"value":"basic"}],"uiDefinition":{"displayName":"Authentication Type","description":"Authentication type to connect to your database","tooltip":"Authentication type to connect to your database","constraints":{"tabIndex":3,"required":"false","allowedValues":[{"text":"Windows","value":"windows"},{"text":"Basic","value":"basic"}],"capability":["gateway"]}}},"username":{"type":"securestring","uiDefinition":{"displayName":"Username","description":"Username credential","tooltip":"Username credential","constraints":{"tabIndex":4,"clearText":true,"required":"true","capability":["gateway","cloud"]}}},"password":{"type":"securestring","uiDefinition":{"displayName":"Password","description":"Password credential","tooltip":"Password credential","constraints":{"tabIndex":5,"required":"true","capability":["gateway","cloud"]}}},"gateway":{"type":"gatewaySetting","gatewaySettings":{"dataSourceType":"sql","connectionDetails":["server","database"]},"uiDefinition":{"constraints":{"tabIndex":6,"required":"false","capability":["gateway"]}}},"sqlConnectionString":{"type":"securestring","uiDefinition":{"displayName":"SQL Connection String","description":"SQL Connection String","tooltip":"Password credential","constraints":{"required":"false","hidden":"true"}}}},"runtimeUrls":["https://europe-001.azure-apim.net/apim/sql"],"primaryRuntimeUrl":"https://europe-001.azure-apim.net/apim/sql","metadata":{"source":"marketplace","brandColor":"#ba141a","allowSharing":true,"useNewApimVersion":"true","version":{"previous":"releases/v1.0.1305\\1.0.1305.1883","current":"releases/v1.0.1306\\1.0.1306.1889"}},"capabilities":["tabular","gateway","cloud"],"tier":"Premium","isCustomApi":false,"description":"Microsoft SQL Server is a relational database management system developed by Microsoft. Connect to SQL Server to manage data. You can perform various actions such as create, update, get, and delete on rows in a table.","createdTime":"2016-09-30T22:53:18.4800804Z","changedTime":"2020-02-19T17:33:10.3921889Z"}},"source":"Invoker","id":"/providers/Microsoft.PowerApps/apis/shared_sql","displayName":"SQL Server","iconUri":"https://connectoricons-prod.azureedge.net/sql/icon_1.0.1306.1889.png","brandColor":"#ba141a","tier":"NotSpecified"}},"createdTime":"2018-10-24T07:27:34.7336734Z","lastModifiedTime":"2018-10-24T07:28:29.9313345Z","environment":{"name":"Default-e3cf3c98-a978-465f-8254-9d541eeea73c","type":"Microsoft.Flow/environments","id":"/providers/Microsoft.Flow/environments/Default-e3cf3c98-a978-465f-8254-9d541eeea73c"},"definitionSummary":{"triggers":[{"type":"Request","kind":"Button"}],"actions":[{"type":"ApiConnection","swaggerOperationId":"GetItem","metadata":{"flowSystemMetadata":{"swaggerOperationId":"GetItem"}}},{"type":"InitializeVariable"}]},"creator":{"tenantId":"e3cf3c98-a978-465f-8254-9d541eeea73c","objectId":"cd3205c7-55ca-486e-88f7-232803bf2ba4","userId":"cd3205c7-55ca-486e-88f7-232803bf2ba4","userType":"ActiveDirectory"},"flowTriggerUri":"https://europe-001.azure-apim.net:443/apim/logicflows/c43187c3-3693-4ac9-844f-08dfefd8f09d/triggers/manual/run?api-version=2016-06-01","installationStatus":"Broken","provisioningMethod":"FromDefinition","flowFailureAlertSubscribed":true,"referencedResources":[]}}

 Now you understand why I'm keen on having the formatting.  🙂

Pstork1
Dual Super User III
Dual Super User III

are you sending that back from Flow using the Respond to Power Apps?  Or the HTTP response action?  For something that large it should be the http response action.  Respond to Power Apps is limited in size and will tend to truncate the JSON.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi @Pstork1 ,

 

I'm using 'respond to power apps'.

The output is not truncated.

When I put it into the collection it does not throw an error.

When I display the output of the JSON function in a label

ClearCollect(FlowResCol,FlowAction2.Run("Get","",Gallery1.Selected.Id).res);
Set( OutPutTxt , JSON( FlowResCol ,JSONFormat.IndentFour ));

I see this:

[
    {
        "Value": "{\"name\":\"c43187c3-3693-4ca9-844f-08dfefd8f09d\",\"id\":\"/providers/Microsoft.Flow/environments/Default-e3cf3c98-a978-465f-8254-9d541eeea73c/flows/c43187c3-3693-4ca9-844f-08dfefd8f09d\",\"type\":\"Microsoft.Flow/environments/flows\",\"properties\":{\"apiId\":\"/providers/Microsoft.PowerApps/apis/shared_logicflows\",\"displayName\":\"Button -> Get row\",\"userType\":\"Owner\",\"definition\":{\"$schema\":\"https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#\",\"contentVersion\":\"1.0.0.0\",\"parameters\":{\"$connections\":{\"defaultValue\":{},\"type\":\"Object\"},\"$authentication\":{\"defaultValue\":{},\"type\":\"SecureObject\"}},\"triggers\":{\"manual\":{\"type\":\"Request\",\"kind\":\"Button\",\"inputs\":{\"schema\":{\"type\":\"object\",\"required\":[],\"properties\":{}}}}},\"actions\":{\"Get_row\":{\"runAfter\":{},\"metadata\":{\"flowSystemMetadata\":{\"swaggerOperationId\":\"GetItem\"}},\"type\":\"ApiConnection\",\"inputs\":{\"host\":{\"connection\":{\"name\":\"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sql']['connectionId']\"}},\"method\":\"get\",\"path\":\"/datasets/default/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[MH_SAPdata_updates]'))}/items/@{encodeURIComponent(encodeURIComponent('121029'))}\",\"authentication\":{\"type\":\"Raw\",\"value\":\"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']\"}}},\"Initialize_variable\":{\"runAfter\":{\"Get_row\":[\"Succeeded\"]},\"type\":\"InitializeVariable\",\"inputs\":{\"variables\":[{\"name\":\"com\",\"type\":\"String\",\"value\":\"@{body('Get_row')?['Comment']};@{body('Get_row')?['Reason']};@{body('Get_row')?['Status']}\"}]}}},\"outputs\":{}},\"triggerSchema\":{\"type\":\"object\",\"required\":[],\"properties\":{}},\"state\":\"Started\",\"connectionReferences\":[{\"connectionName\":\"0cc11648080d441ead1904e034b305bb\",\"displayName\":\"SQL Server\",\"id\":\"/providers/Microsoft.PowerApps/apis/shared_sql\"}],\"installedConnectionReferences\":{\"shared_sql\":{\"connectionName\":\"0cc11648080d441ead1904e034b305bb\",\"apiDefinition\":{\"name\":\"shared_sql\",\"id\":\"/providers/Microsoft.PowerApps/apis/shared_sql\",\"type\":\"/providers/Microsoft.PowerApps/apis\",\"properties\":{\"displayName\":\"SQL Server\",\"iconUri\":\"https://connectoricons-prod.azureedge.net/sql/icon_1.0.1306.1889.png\",\"purpose\":\"NotSpecified\",\"connectionParameters\":{\"server\":{\"type\":\"string\",\"uiDefinition\":{\"displayName\":\"SQL server name\",\"description\":\"SQL server name\",\"tooltip\":\"SQL server name\",\"constraints\":{\"tabIndex\":1,\"required\":\"true\",\"capability\":[\"gateway\",\"cloud\"]}}},\"database\":{\"type\":\"string\",\"uiDefinition\":{\"displayName\":\"SQL database name\",\"description\":\"SQL database name\",\"tooltip\":\"SQL database name\",\"constraints\":{\"tabIndex\":2,\"required\":\"true\",\"capability\":[\"gateway\",\"cloud\"]}}},\"authType\":{\"type\":\"string\",\"allowedValues\":[{\"value\":\"windows\"},{\"value\":\"basic\"}],\"uiDefinition\":{\"displayName\":\"Authentication Type\",\"description\":\"Authentication type to connect to your database\",\"tooltip\":\"Authentication type to connect to your database\",\"constraints\":{\"tabIndex\":3,\"required\":\"false\",\"allowedValues\":[{\"text\":\"Windows\",\"value\":\"windows\"},{\"text\":\"Basic\",\"value\":\"basic\"}],\"capability\":[\"gateway\"]}}},\"username\":{\"type\":\"securestring\",\"uiDefinition\":{\"displayName\":\"Username\",\"description\":\"Username credential\",\"tooltip\":\"Username credential\",\"constraints\":{\"tabIndex\":4,\"clearText\":true,\"required\":\"true\",\"capability\":[\"gateway\",\"cloud\"]}}},\"password\":{\"type\":\"securestring\",\"uiDefinition\":{\"displayName\":\"Password\",\"description\":\"Password credential\",\"tooltip\":\"Password credential\",\"constraints\":{\"tabIndex\":5,\"required\":\"true\",\"capability\":[\"gateway\",\"cloud\"]}}},\"gateway\":{\"type\":\"gatewaySetting\",\"gatewaySettings\":{\"dataSourceType\":\"sql\",\"connectionDetails\":[\"server\",\"database\"]},\"uiDefinition\":{\"constraints\":{\"tabIndex\":6,\"required\":\"false\",\"capability\":[\"gateway\"]}}},\"sqlConnectionString\":{\"type\":\"securestring\",\"uiDefinition\":{\"displayName\":\"SQL Connection String\",\"description\":\"SQL Connection String\",\"tooltip\":\"Password credential\",\"constraints\":{\"required\":\"false\",\"hidden\":\"true\"}}}},\"runtimeUrls\":[\"https://europe-001.azure-apim.net/apim/sql\"],\"primaryRuntimeUrl\":\"https://europe-001.azure-apim.net/apim/sql\",\"metadata\":{\"source\":\"marketplace\",\"brandColor\":\"#ba141a\",\"allowSharing\":true,\"useNewApimVersion\":\"true\",\"version\":{\"previous\":\"releases/v1.0.1305\\\\1.0.1305.1883\",\"current\":\"releases/v1.0.1306\\\\1.0.1306.1889\"}},\"capabilities\":[\"tabular\",\"gateway\",\"cloud\"],\"tier\":\"Premium\",\"isCustomApi\":false,\"description\":\"Microsoft SQL Server is a relational database management system developed by Microsoft. Connect to SQL Server to manage data. You can perform various actions such as create, update, get, and delete on rows in a table.\",\"createdTime\":\"2016-09-30T22:53:18.4800804Z\",\"changedTime\":\"2020-02-19T17:33:10.3921889Z\"}},\"source\":\"Invoker\",\"id\":\"/providers/Microsoft.PowerApps/apis/shared_sql\",\"displayName\":\"SQL Server\",\"iconUri\":\"https://connectoricons-prod.azureedge.net/sql/icon_1.0.1306.1889.png\",\"brandColor\":\"#ba141a\",\"tier\":\"NotSpecified\"}},\"createdTime\":\"2018-10-24T07:27:34.7336734Z\",\"lastModifiedTime\":\"2018-10-24T07:28:29.9313345Z\",\"environment\":{\"name\":\"Default-e3cf3c98-a978-465f-8254-9d541eeea73c\",\"type\":\"Microsoft.Flow/environments\",\"id\":\"/providers/Microsoft.Flow/environments/Default-e3cf3c98-a978-465f-8254-9d541eeea73c\"},\"definitionSummary\":{\"triggers\":[{\"type\":\"Request\",\"kind\":\"Button\"}],\"actions\":[{\"type\":\"ApiConnection\",\"swaggerOperationId\":\"GetItem\",\"metadata\":{\"flowSystemMetadata\":{\"swaggerOperationId\":\"GetItem\"}}},{\"type\":\"InitializeVariable\"}]},\"creator\":{\"tenantId\":\"e3cf3c98-a978-465f-8254-9d541eeea73c\",\"objectId\":\"cd3205c7-55ca-486e-88f7-232803bf2ba4\",\"userId\":\"cd3205c7-55ca-486e-88f7-232803bf2ba4\",\"userType\":\"ActiveDirectory\"},\"flowTriggerUri\":\"https://europe-001.azure-apim.net:443/apim/logicflows/c43187c3-3693-4ca9-844f-08dfefd8f09d/triggers/manual/run?api-version=2016-06-01\",\"installationStatus\":\"Broken\",\"provisioningMethod\":\"FromDefinition\",\"flowFailureAlertSubscribed\":true,\"referencedResources\":[]}}"
    }
]

So the formatting kicks in but seems to get lost in the JSON. So do I.  🙂

Do you think the output would be different if it was returned by http response?

I hope not, but I can try.

 

 

Pstork1
Dual Super User III
Dual Super User III

As I said, I recommend using the HTTP respond to request action instead of the respond to Power Apps.  That action lets you actually load the JSON schema using a sample output. I don't know what else to recommend.  All I can do is tell you what does work.  If you want to keep trying it a different way then I don't know how to help you.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

I'm trying to this http response action as we speak.

Not used it before, so I guess I need to add something like this to the body or headers:

 

body('Get_Flow')

Now I think I need to connection between powerapps and flow again.

 

Power apps doesn't like the .res variable in which I try to return the json

Now my issue is that the .Run() just returns true, which is correctly formatted by the way.

 

 

[
    {
        "Value": true
    }
]

 

I guess I don't yet know how to return information using the 'http response action'

 

Nope that is just fine, thanks to @MR. Dang fine examples

 

Flow is definitely returning the whole body. 

So my issue is on the power apps side, where I just don't know how to get that.

 

 

 

Pstork1
Dual Super User III
Dual Super User III

If you changed the action that does the return you'll need to remove the flow and re-add it in Power Apps to get it to pick up the change.  Also, make sure you use a sample payload that you've gotten previously to load into the Response to generate the schema.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi @Pstork1 ,

 

You nailed it. it works

But I suddenly realize why I can't use this.

The schema returned is different for each flow.

 

[
    {
        "id": "/providers/Microsoft.Flow/environments/Default-e3cf3c98-a978-465f-8254-9d541eeea73c/flows/c43187c3-3693-4ac9-84f4-08dfefd8f09d",
        "name": "c43187c3-3693-4ac9-844f-08dfefd8f09d",
        "properties": {
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_logicflows",
            "connectionReferences": [
                {
                    "connectionName": "0cc11648080d441eda1904e034b305bb",
                    "displayName": "SQL Server",
                    "id": "/providers/Microsoft.PowerApps/apis/shared_sql"
                }
            ],
            "createdTime": "2018-10-24T07:27:34.7336734Z",
            "creator": {
                "objectId": "cd3205c7-55ca-486e-88f7-232803bf2ba4",
                "tenantId": "e3cf3c98-a987-465f-8254-9d541eeea73c",
                "userId": "cd3205c7-55ca-486e-88f7-232803bf2ba4",
                "userType": "ActiveDirectory"
            },
            "definition": {
                "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
                "actions": {
                    "Get_row": {
                        "inputs": {
                            "authentication": {
                                "type": "Raw",
                                "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
                            },
                            "host": {
                                "connection": {
                                    "name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sql']['connectionId']"
                                }
                            },
                            "method": "get",
                            "path": "/datasets/default/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[MH_SAPdata_updates]'))}/items/@{encodeURIComponent(encodeURIComponent('121029'))}"
                        },
                        "metadata": {
                            "flowSystemMetadata": {
                                "swaggerOperationId": "GetItem"
                            }
                        },
                        "runAfter": {},
                        "type": "ApiConnection"
                    },
                    "Initialize_variable": {

 

 Bummer,...

 

 

 

@Pstork1 

 

Or could there be a way to make 'http response action' just return everything?

 

A wildcard schema?

Pstork1
Dual Super User III
Dual Super User III

That is indeed a problem.  If the schema is similar, but some fields aren't returned sometimes because they are null, then there are ways to hand code the schema to allow for that.  But if the schemas are completely different then it just won't work.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,599)