Unanswered

How to make ParseJSON() work with proper JSON formatted table

(0) Share

Report

Posted on by martinav

3,340

Well, its no surprise. They give us a potentially useful function like ParseJSON(), but the execution is convoluted. The example given in the documentation show putting the JSON string into quotes, and double-quoting text devices within the string. Of course, there is no straight forward way to do this.... is there?

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-parsejson

The JSON I want to convert to a table is simple, and in a very tidy JSON file shown here:

[
 {
 "WorkOrderOB": "7307-18",
 "Action": "Updated"
 },
 {
 "WorkOrderOB": "4305-1",
 "Action": "Created"
 }
]

I dont like the idea of using Substitute() because you cannot be sure that a quote is not in the string. I thought this was going to be easy, once I got the JSON string from Flow into PA. Well, now there is another convoluted issue to overcome.

The example given is this:

ForAll( Table( ParseJSON( "[ { ""id"": 1, ""name"": ""one"" }, { ""id"": 2, ""name"": ""two"" } ]" ) ), { id: Value(ThisRecord.Value.id), name: Text(ThisRecord.Value.name) } )

This is the format that makes this statement work. But, how do I reliably get this format?

ForAll( Table( ParseJSON( "[
 {
 ""WorkOrderOB"": ""7307-18"",
 ""Action"": ""Updated""
 },
 {
 ""WorkOrderOB"": ""4305-1"",
 ""Action"": ""Created""
 }
]" ) ), { WorkOrderOB: Text(ThisRecord.Value.WorkOrderOB), Action: Text(ThisRecord.Value.Action) } )

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

martinav 3,340 on at

Like (0)

Report
I really dont want to use a substitude() do this. Funny, chatGPT gave a genius answer...

Text(JSONString, JSONFormat.IncludeQuotes)
However, there is no .IncludeQuotes statement in PA. What a tease.

Was this reply helpful? Yes No
cwebb365 3,294 Most Valuable Professional on at

Like (0)

Report

Shouldn't need to do all that, should be able to just say

Was this reply helpful? Yes No
cwebb365 3,294 Most Valuable Professional on at

Like (0)

Report

Didn't mean to hit save lol. Anyway, should be able to use this. A forAll alone creates a table.

ForAll(ParseJSON(varJSON),{WorkOrderOB:ThisRecord.WorkOrderOB,Action:ThisRecord.Action})

varJSON can be replaced with a varable set to, or you can use the string form it direct in the forall.

Set(varJSON,"[

{

""WorkOrderOB"": ""7307-18"",

""Action"": ""Updated""

},

{

""WorkOrderOB"": ""4305-1"",

""Action"": ""Created""

}

]")

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report
@cwebb365

The issue is not with the syntax of the ForAll, but the format of varJSON. JSON output from MS Flow is single quote, but PA wants double quotes. The question is how to convert the JSON format, either in Flow or PA. I find no way without being complicated. Unfortunately, I havent found how to do that yet. Even submitting to using substitute, I havent found a working way yet.

ForAll(Table( ParseJSON(Concatenate("""",Substitute(UploadAction.actions,"""",""""""),""""))), {WorkOrderOB:Text(ThisRecord.Value.WorkOrderOB), Action:Text(ThisRecord.Value.Action)})

While this provides the proper double quote JSON, it gives this error:

Unfortunately, I spend more time on BS problems like this than actually creating something productive.

Was this reply helpful? Yes No
cwebb365 3,294 Most Valuable Professional on at

Like (0)

Report

Hmm, when it comes over from flow, it should read it as a string/text object and the quotes shouldn't matter? You only need double quotes when trying to set/declare text to a variable / object from within PowerApps.

I just tested a flow using the raw Json passing output into variable and using it in the same ForAll(ParseJSON) and the gallery showed the Items fine.

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report

@cwebb365

I dont know if i'm being clear. The whole purpose that I am aware of with using ParseJSON is to do that very thing. That is what I am needing to accomplish. I need this JSON file to generate a table which I can then use the data as feedback for my app.

Was this reply helpful? Yes No
cwebb365 3,294 Most Valuable Professional on at

Like (0)

Report

I guess I don't understand, because I've taken your original json, put it in a compose action in flow with single quotes (the raw json) that output to powerapps. Then user set() to pass the flow output into a variable and used the variable in ParseJSON to get to the data using the ForAll example which is a table that can be used in Filters / Collections / Gallery etc.

If that's not what you're trying to do then I guess I might not be understanding :).

Was this reply helpful? Yes No
martinav 3,340 on at

Like (0)

Report

@cwebb365

Im not sure what to say... because...

This works:
(Statement is in 'items' in a table object)

This does not:

I cannot repeat what your saying is true. So, I dont know how to proceed. The only difference in the two are single vs double quotes. Until I can see it work this way, i wont even mess with Response. One step at a time.

Was this reply helpful? Yes No
Verified answer

cwebb365 3,294 Most Valuable Professional on at

Like (0)

Report

Yeah, if you are setting straight JSON with "Code" you have to double quote it. If you pass it in, from say Flow as a string object into powerapps, it doesn't have to be double quoted. The quotes are only for the code section. I guess the question is, where is the json coming from, if it's flow you don't have to worry about it, it should just part it right over when you pass in the flow run is what I'm saying. Or if you pull the json from a data source, it'll parsejson just fine. The only reason you have to deal with double quotes is manually setting variables/setting text input with the code editor.

Was this reply helpful? Yes No
martinav 3,340 on at

Like (1)

Report
Hmm, interesting. Looks like you have to know what you are doing, and have blind faith that its going to work. I dont know what it is that I did different, but now, when I do this, it does work fine.

So the bottom line to this one was your last post, which I will call the solution. Thanks for sticking with me through this. What a waste of 2 days messing around.

ForAll( Table(ParseJSON(UploadAction.actions)), { WorkOrderOB:Text(ThisRecord.Value.WorkOrderOB), Action:Text(ThisRecord.Value.Action) } )

Was this reply helpful? Yes No