It is really tricky to handle JSON or Table with many layers including 'collection' in powerapps canvas. I call it 'Table in Table' sturcture.
The picture above is 'Table in Table'. As you can see, the collection 'controlSortbyID_fundering' consists of many columns, and the column 'CommentRev...' has table in its every single cell. (The red circles)
Especially when I talk about PowerApps, collection and table are almost same words. Sometimes they are technically indicating different things though.
Let's get back to the subject. Before I directly jump into the how to handle this structure in PowerApps, I will talk about the data form. I put JSON data from SharePoint list and from PowerApps here below.
JSON data in SharePoint list
[{"Bemaerkninger":"Udføres af Adapteo","Bilag":"Se bilag 3.2.2","CommentReview":[{"chat":"Hello are you available today for the meeting?","requestorName":"Joonseok Pak","reviewerName":null,"time":"02/11/23 12:53:57"},{"chat":"we have some issues with column A43 and calculation erros","requestorName":"Joonseok Pak","reviewerName":null,"time":"02/11/23 12:54:17"},{"chat":"Please send us the new drawing by Wednesday. we will stop the construction immediately.","reviewerName":"Torben Lange","time":"02/11/23 13:23:42"}],"Dato":"","Dokumentationskrav":"Situationsplan for ledninger, samt placering af pavillonbygningen.","Hvad":"Der skal fortages en LER-undersøgelse, om ledninger i jorden konflikter med pavillonopstillingen, f.eks. kloak, tanke osv.","Hvordan":"LER.dk","Hvornar":"Inden arbejdets påbegyndelse","ID":1,"Kriterie":"","col":"K1","image":"K1"},
{"Bemaerkninger":"Udføres af Adapteo","Bilag":"Se bilag 3.2.2","CommentReview":[{"chat":"What's up?","requestorName":null,"reviewerName":"Torben Lange","time":"01/11/23 10:53:13"},{"chat":"Hiloo","requestorName":"Joonseok Pak","reviewerName":null,"time":"01/11/23 10:53:13"},{"chat":"We changed some specification of the first slab (S1F1, S2F1, S3F1). Please check this out","requestorName":"Joonseok Pak","reviewerName":null,"time":"02/11/23 13:13:26"},{"chat":"The person in charge of it is off until Friday. It can be checked next Monday.","requestorName":null,"reviewerName":"Torben Lange","time":"02/11/23 13:18:00"}],"Dato":"","Dokumentationskrav":"BD: Foto, evt. kommentar hvordan den mekaniske komprimering er udført.","Hvad":"Terræn for opstilling, Der gives en simpel vurdering af terrænunderlag, hvor pavillonbygningen skal stå på","Hvordan":"Visuelt (VK)","Hvornar":"efter punktets udforelse","ID":2,"Kriterie":"","col":"K2","image":"K2"}]
JSON data in PowerApps Variable
So, the blue part of the JSON data above is a nested layer. The best way to quickly grasp data structure is to convert them into table format in PowerApps using 'ParseJSON() and Table() and Collection()'.
The code below is the sample of convering JSON data to Table(or collection).
You should focus on the red box in the picture above. If you have good understanding of 'ForAll and Table', you can understand this doubly used ForAll function. This is the most difficult part I struggled to figure out the way.
I will tell you the most fundamental concept of Power Fx language first.
This language also likes 'defiend data type'. Otherwise, it won't try to udnerstand and execute code line saying 'untypedObject'. So if you find an error code with 'untyped Object', you should check if all the parameters are defiend well such as 'text, value(integer, number), table, and so on'.
Once you parse JSON data from SharePoint or whatever, to make it into table(collection) format, you must use 'ClearCollect, ForAll, and Table' (refer to the code above).You normally have to name the columns and define data type. (This is very basic knowledge unless you are beginner.)
(ex. {col: Text(Value.col)}, col is column name. Text is definition of data type. Value.col is data).
(ex. {col: Text(Value.col)}, col is column name. Text is definition of data type. Value.col is data).
Therefore, if you want to see data of nested layers(array, object, whatever) in a table structure in a mother table, you should repeat the same process as the basic table creation. To do this, you can use 'ForAll' function twice or more(it depends on how much JSON is nested).
Execution code in PowerApps
ClearCollect(controlSortbyID_fundering,
ForAll(Table(parsingJSON_fundering),
{
col: Text(Value.col),
ID: Int(Value.ID),
Hvad: Text(Value.Hvad),
Hvordan: Text(Value.Hvordan),
Hvornar: Text(Value.Hvornar),
Kriterie: Text(Value.Kriterie),
Dokumentationskrav: Text(Value.Dokumentationskrav),
Dato: Text(Value.Dato),
Bemaerkninger: Text(Value.Bemaerkninger),
Bilag: Text(Value.Bilag),
image: Text(Value.col),
CommentReview:
ForAll(Table(Value.CommentReview),
{
chat: Text(Value.chat),
time: Text(Value.time),
requestorName: Text(Value.requestorName),
reviewerName:Text(Value.reviewerName)
}
)
}
)
);
ForAll(Table(parsingJSON_fundering),
{
col: Text(Value.col),
ID: Int(Value.ID),
Hvad: Text(Value.Hvad),
Hvordan: Text(Value.Hvordan),
Hvornar: Text(Value.Hvornar),
Kriterie: Text(Value.Kriterie),
Dokumentationskrav: Text(Value.Dokumentationskrav),
Dato: Text(Value.Dato),
Bemaerkninger: Text(Value.Bemaerkninger),
Bilag: Text(Value.Bilag),
image: Text(Value.col),
CommentReview:
ForAll(Table(Value.CommentReview),
{
chat: Text(Value.chat),
time: Text(Value.time),
requestorName: Text(Value.requestorName),
reviewerName:Text(Value.reviewerName)
}
)
}
)
);
Table in Table
Once you hit the table icon (the red circle), you can see the nested table like below.
Comments
Post a Comment