cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StevePenner
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
v-bofeng-msft
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
v-bofeng-msft
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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,314)