cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GrobiGer
Frequent Visitor

JSON building and parsing

Weeks ago, I learned how to parse JSON with the help of RegEx. 

Power Apps Validation and JSON Parsing with Regex - April Dunnam - SharePoint Siren

 

But there was one snag. MatchAll collects STING Data only. Because I needed numbers, I had to figure out, how to change my resulting collection. Today I found my solution, so I'm now able to use JSON forth and back within powerapps.

 

Important:

Because I use a German localized Windows, the sources I provide are also using the German system!

To use the source in English systems, you have to change ; to , and ;; to ;

 

My JSON basic data looks like this.

 

 

Set(varJSON;
"[{""Level"":10,""Tool"":""Conceptboard"",""erreicht"":0},{""Level"":10,""Tool"":""Delve"",""erreicht"":0},{""Level"":10,""Tool"":""FindTime"",""erreicht"":0},{""Level"":10,""Tool"":""Whiteboard"",""erreicht"":0}]"
);;
//patch my SP-table, so that there is a new row for the user
Patch(tbl_zert_Expert_details;Defaults(tbl_zert_Expert_details));;
// Setting a global variable with my users eMail Address
Set(varUser;'Office365-Benutzer'.MyProfileV2().mail);;

//patch the JSON to my SP-Table
Patch(tbl_zert_Expert_details;LookUp(tbl_zert_Expert_details;'Created By'.Email = varUser       );{Lernwerkstatt_Module: varJSON});;

 

 

so far, so good. Now the data is stored as a JSON String within my SP List.

 

As mentioniod above, to read back the JSON I use MatchAll. But MatchAll creates STINGs from my JSON even if I have numbers. 

So I had to convert the stings to numbers. 

Here is my solution:

 

 

ClearCollect(
    colLWSModuleMenu;
    DropColumns(
        //DropColumns kicks out unnecessary columns created by MatchAll and also the temporary columns Leveltxt and erreichtTxt
        AddColumns(
            //AddColumns copys and converts the values of LevelTxt and erreichtTxt usign the value() function
            MatchAll(
                varJSON;
                //Matchall extracts all the content from my SP-List item and provides the sting data for my collection
                "{""Level"":(?<LevelTxt>[^,]*),""Tool"":""(?<Tool>[^"",]*)"",""erreicht"":(?<erreichtTxt>[^}]*)}"
            );
            "Level";Value(ThisRecord.LevelTxt);
            "erreicht";Value(ThisRecord.erreichtTxt)
        );
        "StartMatch";"SubMatches";"FullMatch";"LevelTxt";"erreichtTxt"
    )
);;

 

 

Hint: If you're not familiar with REGEX, use RegExr: Learn, Build, & Test RegEx to figure out the right syntax.

 

And tada... I can now use JSON forth and back, even for numbers and not only for stings. 

Hope that helps some of you. as workaround until Microsoft provides better handling for JSON within PowerApps

@R2Power 

0 REPLIES 0

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,339)