cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Reading JSON in SP Column

I have a SharePoint list constructed to read like a multi-level list in a Word document (image attached). The document type is a meeting agenda, typically built with a multi-level list. The SP list reading like a multi-level list something the client wants because their participation in SharePoint is to read it for content, and in an original list format instead of, better, in a form or even a Power App. I am still making the Power App.  To approximate reading like a multi-level (Word) list, there are a lot of blank column values in items (rows), as the image shows. (Note that this SP view is for the developer, not the users.)

 

The Power App has the agenda user selecting the meeting date (SP column 'Meeting Date') in order to fill the screen (a gallery control in one screen, a listbox in another) with the contents of the agenda. That content is read in from all the rows (SP items) that correspond to the meeting date.

 

My problem was to read only the items (rows) in the SP list that corresponded to the meeting date. This is the item with the meeting date 'mm/dd/yyyy' in it and all the blank values in the column until the next 'mm/dd/yyyy'.

 

I started out using a Filter() to find all the column "Meeting Date" that were not blank, but then had issues with then filtering the items with blank dates following it until the next value with a date

 

I thought it better to create a SP column (multiline plain text) that had JSON for all the useful needs for the Power App app to read. The JSON could be read in for each item and identify each item (row) that corresponded to the meeting date, instead of reading blank 'Meeting Date' columns until the next 'mm/dd/yyyy' was encountered.

 

So here's the problem: while Power Apps has a JSON() function that will write JSON as text, it will not read in serialized text to parse a JSON object (probably because the JavaScript-like object is not supported in Power Apps?).

 

So what is the workaround? Just treat JSON as a string (text) when reading it? Perhaps use a Match() function with regular expressions as a method of parsing it?

 

SPList.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi @StevePenner  :

First of all,I'm afraid that PowerApp does not have any function to directly convert JSON text into array or Object.

Secondly,as you said, you can use the Match() function to parse JSON text, I'v made a text for your reference:

1\My data source:

v-bofeng-msft_0-1606115404456.jpeg

 

2\Add a DataTable(DataTable5) and set its items property to

colImpactFullDetails

/* The function of this DataTable is to select a record and get its "Item Data"*/

3\Add a button and set its OnSelelct Property to:

Set(
TheRecord, /* TheRecord  is my custom variable*/
{
MeetingDate:Match(Substitute(DataTable5.Selected.'Item Data',"""",""),"(?<=MeetingDate:).*?\d{4}").FullMatch,
Editable:Match(Substitute(DataTable5.Selected.'Item Data',"""",""),"(?<=Editable:).*?(True|False)").FullMatch,
TicketIds:Split(Substitute(Substitute(Match(Substitute(DataTable5.Selected.'Item Data',"""",""),"(?<=TicketIds:).*?]").FullMatch,"]",""),"[",""),",")
}
)

/* When the user clicks this button, the result of the analysis will be saved in the variable TheRecord */

Best Regards,

Bof

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Hi @StevePenner  :

First of all,I'm afraid that PowerApp does not have any function to directly convert JSON text into array or Object.

Secondly,as you said, you can use the Match() function to parse JSON text, I'v made a text for your reference:

1\My data source:

v-bofeng-msft_0-1606115404456.jpeg

 

2\Add a DataTable(DataTable5) and set its items property to

colImpactFullDetails

/* The function of this DataTable is to select a record and get its "Item Data"*/

3\Add a button and set its OnSelelct Property to:

Set(
TheRecord, /* TheRecord  is my custom variable*/
{
MeetingDate:Match(Substitute(DataTable5.Selected.'Item Data',"""",""),"(?<=MeetingDate:).*?\d{4}").FullMatch,
Editable:Match(Substitute(DataTable5.Selected.'Item Data',"""",""),"(?<=Editable:).*?(True|False)").FullMatch,
TicketIds:Split(Substitute(Substitute(Match(Substitute(DataTable5.Selected.'Item Data',"""",""),"(?<=TicketIds:).*?]").FullMatch,"]",""),"[",""),",")
}
)

/* When the user clicks this button, the result of the analysis will be saved in the variable TheRecord */

Best Regards,

Bof

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (12,648)