Edit or View JSON collection in SP List in a dynamic Gallery

[Morke]

 Hi

I created a dynamic Form for new items in a SharePoint list.

The form has several options, dropdowns and textboxes, and one of those cards is a dynamic gallery in where users can add or remove entries.

One the user saves the entry, I use the JSON function to store it as a variable and save it in a JSON collection in the corresponding SP List field.

So, once submitted, the column shows a JSON collection.

The problem is when users try to edit or view the submitted items. I don't know how to "read" the saved entry from that record's JSON column and everything I put in the Gallery's "Default" value is marked as red because it's not compatible with the Items data source (which is a collection)

How can I "read" that JSON from that record's column and recreate the gallery when users are editing the record?

[Morke]

Ok, the answer was to use the Data Card Default instead of the gallery default

I put ThisItem.'column with JSON' as the Data Card containing the Gallery default and it worked

EDIT: Didn't work. After rechecking I found out that if I do that, all the records show the last record's value for that column instead of that specific record value.

[RandyHayes]

@Morke 

You will not be able to turn the JSON back into a collection in PowerApps.  JSON is a one-way street right now.

There is an Idea in the Idea Forum where this capability is requested.  You might want to go there and vote and review any workarounds.  Because right now, you will have to parse it on your own - painfully with string operations.

I hope this answers your question.

[Morke]

Thank you for your reply.

That's too bad...

How can I select the record in Edit mode to parse it?
I understand I need to do it on the OnEdit Sharepoint Integration resource, but I'm not sure how to select that specific Record column value to retreive it

[RandyHayes]

@Morke 

I'm not sure I entirely understand where you are having the issue.  Is it just getting to the column in your List?  If so, then it is just that Column name.  So, if you are in a Gallery or EditForm, the formula is ThisItem.yourJSONcolumn but remember, this is just text!

The JSON column is not a record, I assume you have it defined as a text column in your SharePoint list.

[Morke]

So I managed to get that record.

In sharepoint integration, OnEdit, I used

Set(variableJSON, JSON(SharepointIntegration.Selected.ColumnName));

However, I'm not sure how to traverse this and format it approperly so I can "recreate" a collection based on this.

Am I pointed in the right direction?

[RandyHayes · Accepted solution]

@Morke 

So I'm still a bit confused on your scenario.

My understanding was that you were creating a record and then, in that record you have a column for your JSON.  When you save, you are taking some existing collection and using the JSON function on it and then storing the resulting JSON text into your record in the JSON column.

But that does not seem to be what you are getting from what you are now showing.  It seems more that you are now taking some sort of column and turning into JSON text??

Can you confirm the type of Column that you have in SharePoint and let me know what that is?  

If it is Text, then you do not need to do anything with JSON when you read the record back for Edit.  It will be text and will already be in a JSON format.  It will be SharepointIntegration.Selected.ColumnName that corresponds to your column where the JSON is stored. That's where it ends...there is nothing that will convert that back into a collection for you.  You would need to use string manipulation to try and convert it.

Let me know what kind of column this is in SharePoint.

[Morke]

I understand. Thank you for your explanation, really helped me understand it.

That's a shame, though... makes no sense to have a function to parse a collection into JSON, but not the other way around.

[RandyHayes]

@Morke 

No problem and agreed!

My $0.02 - licensing!  If you could parse JSON in PowerApps, it would allow you to connect to premium content without a Premium License.  I hope one day I find that to be incorrect, because it would open up a WHOLE new world of power to a PowerApp.