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! 

[Eloy]

Hell @vangrieg, I would do the following:

• Create another Custom List in your SharePoint site to hold the milestones. Make sure you create a field that will hold the project Id.
• Link the 2 lists (projects and Milestones) in a One-to-Many relationship. Check instructions on how to do it here: Sharepoint One-to-Many Lists

Then you can use PowerApps to select the milestones based on the project ID.

Hope this makes sense.

I am not sure the way you are trying to do it is the easiest or best way.

[vangrieg]

Thanks a lot @Eloy for your reply! 

What you are suggesting makes perfect sense, except Lookup columns have given me tons of headache when I tried to use them with PowerApps and Flow, causing all kinds of unexpected issues. Hence my attempt to try a different approach, taking all logic, relationships and calculations away from SharePoint elsewhere. 

Maybe I'm trying to go too far in this direction, but I'm not sure yet. 

[vangrieg]

So I played with the second list idea, and it has a problem. When a user creates a new project and the form is in New mode, project ID isn’t available. So one has to create a project, then come back to edit it and then create milestones. Not good.

Any suggestions on how to work around this?

Thanks!

[Eloy]

I would probably do the following:

1) Create a new form so they can input the Project Information. Include the Project ID as a field. The project ID will be blank when the form is in New Mode. 

2) When the user submits the form and it passess data validation, the Form will become an Edit form hopefully showing the Id of the project. If that is the case I would put some code on the OnSuccess event of the form to capture the Project ID and Navigate to the Milestones Add Screen where you can use to save the milestones to the Sharepoint List using the project ID.

Hopefully this makes sense, I am going to try it tomorrow when I get back to my office. 

[vangrieg]

Thanks @Eloy. 

Turns out you can use Form.LastSubmit.ID to retrieve the ID of the record you just submitted. 

So I'll probably do this - use ClearCollect to gather all the information about my Milestones, then use LastSubmit.ID, ForAll and Patch to create records in the second list after submitting the Project form. 

Anyway, this does seem to be a better way of achieving what I need, so thanks for the suggestion! 

[Eloy]

Excellent!!

Let me know of the outcome of your Project.

[v-xida-msft]

Hi @vangrieg,

Could you please share a bit more about your SP list?

Do you store the JSON String in a Multiple lines of text type column of your SP list?

Further, do you want to parse a JSON string within PowerApps?

If you want to parse a JSON string within PowerApps, I afraid that there is no way to achieve your needs in PowerApps currently.

If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:
https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

I also agree with your thought almost. If you have solved your problem, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify.

Best regards,

Kris

[akosila]

@vangrieg  I am also facing this right now. what method did you do to parse JSON and put them in their respective fields during edit?

[vangrieg]

@akosilaI didn’t find anything really, so didn’t so it.