How to populate controls by values from a JSON formatted string?

[vangrieg]

I have a SharePoint list with some project data. Some projects have an arbitrary number of intermediate milestones. I need to store some information about these milestones in the list, and use a PowerApps form for editing and creating items in this list. 

The data itself is very simple, just a milestone name, a date, and a numeric value. Using text input boxes and date pickers...

I can easily create records like this: 

{
    "Milestones": [
        {
            "Name": "Milestone 1",
            "Date": "12/31/2018",
            "Value": 5000000
        },
        {
            "Name": "Milestone 2",
            "Date": "2/1/2019",
            "Value": 5000
        }
    ]
}

... and store them in SharePoint Multiline Text fields. However, I cannot figure out how to use this data to edit entries - I need to parse JSON, extract relevant values and populate the controls with them when the form is in Edit mode. 

Any ideas? 

Thanks a lot in advance! 

[humanshield]

Hi guys, I was monitoring this thread for info as I have a similar use case. Figured it out and posted solution here:

https://powerusers.microsoft.com/t5/Creating-Apps/Parse-JSON-in-PowerApps/td-p/168884

I haven't yet worked through pushing the collection back to JSON > SharePoint field (presume Concat, or the JSON function), but will post that step once I have. If anyone wants to jump in, that'd be great!

[Anonymous]

Hi @humanshield ,

Could you please share your approach in this thread? Not sure why, but when I  click on the link it gives an access denied message.

[SPDaniel]

I get the same access denied - hope this is not just a tease. 🙂

[TiagoFreire]

AFAIK there is not yet a Parse JSON command in PowerApps. 

But there is one in Power Automate (formerly Microsoft Flow).

It will give you a layer of indirection, but you will get a structured response. 

You can call a Power Automate command that:

1) reads from your SP list items that have JSON data,
2) Parse JSON on your list items of interest

3) Builds the return value to PowerApps with a HTTP Response that contains a valid schema for your data. 

This will let you Collect() information with a nested object structure in PowerApps.

Be aware that depending on the amount of data, including nested objects, you might have an expensive/slow Collection, and that may require you to do strategies to cache and update only the changed rows, instead of ClearCollect()ing every time. 

Depending on the amount of data, if you find that the performance is suffering, it may be necessary to bring in a more powerful DB, like Cosmos DB. The same steps above would also be necessary to treat CosmosDB data to be consumed by PowerApps.