Skip to main content

Notifications

Community site session details

Community site session details

Session Id : YuH2G2hVXac0zcNs1MSLpX
Power Apps - Building Power Apps
Answered

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

Like (0) ShareShare
ReportReport
Posted on 30 Jan 2024 20:35:31 by 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:
  • cwebb365 Profile Picture
    3,294 Most Valuable Professional on 07 Feb 2024 at 05:42:27
    Re: How to make ParseJSON() work with proper JSON formatted table

    So check this out. New functions that dropped that are very useful for working with JSON. 

    https://t.co/FbLJGNMHmD

  • cwebb365 Profile Picture
    3,294 Most Valuable Professional on 31 Jan 2024 at 20:25:04
    Re: How to make ParseJSON() work with proper JSON formatted table

    Yeah, i hate the whole untyped / have to define the json thing. Wish it would kind of auto parse like the parse action does in flow. 

  • martinav Profile Picture
    3,340 on 31 Jan 2024 at 20:22:48
    Re: How to make ParseJSON() work with proper JSON formatted table

    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)
     }
    )

     

  • Verified answer
    cwebb365 Profile Picture
    3,294 Most Valuable Professional on 31 Jan 2024 at 16:54:55
    Re: How to make ParseJSON() work with proper JSON formatted table

    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.

  • martinav Profile Picture
    3,340 on 31 Jan 2024 at 07:31:13
    Re: How to make ParseJSON() work with proper JSON formatted table

    @cwebb365 

     

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

     

    This works:

    (Statement is in 'items' in a table object)

    martinav_0-1706686104062.png

     

    This does not:

    martinav_1-1706686205592.png

     

    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.

     

  • cwebb365 Profile Picture
    3,294 Most Valuable Professional on 30 Jan 2024 at 23:05:07
    Re: How to make ParseJSON() work with proper JSON formatted table

    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 :). 

  • martinav Profile Picture
    3,340 on 30 Jan 2024 at 22:54:50
    Re: How to make ParseJSON() work with proper JSON formatted table

    @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.   

  • cwebb365 Profile Picture
    3,294 Most Valuable Professional on 30 Jan 2024 at 22:20:38
    Re: How to make ParseJSON() work with proper JSON formatted table

    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. 

  • martinav Profile Picture
    3,340 on 30 Jan 2024 at 22:11:06
    Re: How to make ParseJSON() work with proper JSON formatted table

    @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:

     

    martinav_0-1706652637492.png

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

  • cwebb365 Profile Picture
    3,294 Most Valuable Professional on 30 Jan 2024 at 21:16:47
    Re: How to make ParseJSON() work with proper JSON formatted table

    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""
      }
    ]")
     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,786 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,093 Most Valuable Professional

Leaderboard