Answered

Transforming JSON inside Power Apps

(0) Share

Report

Posted on by FernandoMT

Hi all, fairly new to Power Apps. I am building an app where users select some items in a gallery which displays Sharepoint search results, to then export them to csv through a power automate flow.

When using the JSON() function in Power Apps to connect with the export flow, I get the "JSON function cannot serialize tables / objects with a nested property called 'Value' of type 'UntypedObject'." error.

The suggested fix in most posts online is using JSONFormat.IgnoreUnsupportedTypes, but results in an empty JSON being sent to Power Automate.

Structure of data returned by Sharepoint search API is:

 [
 {
 "IndividualSearchResult": [
 {
 "Key": "SomeKeyName", //for example "Title"
 "Value": "KeyValue", //actual title of the search result item
 "ValueType": "Edm.Int64"
 },
 {
 "Key": "SomeKeyName",
 "Value": "KeyValue",
 "ValueType": "Edm.Double"
 },
etc.

Is there a way to manually build the JSON to be sent to the flow, or another way to make the JSON function work?

I was thinking to loop through the keys and build a valid JSON inside a variable but there don't seem to be any comprehension options or methods to create it.

All help is appreciated, thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

TheRobRush 11,128 Moderator on at

Like (1)

Report
Copy link

Link copied!
In my experience, when that error is encountered with json, you need to to specify the data type when breaking it down on powerapps side

In example here you can see I had to specify the record (json example has multiple rows of data) then reference column i was looking for and surround ALL that in a Text() Value() DateTimeValue() etc.

and here is an example of what force creating a structure looks like

This is WHAT i sent in via flow to ensure its set up how I want it

checklistJSON = "["&Concat( ForAll(checklistInProgress As dataSource, "{ ""Title"":"""&dataSource.Title&""", ""refID"":"""&dataSource.ID&""", ""inspectionVenue"":"""&dataSource.inspectionVenue&""", ""inspectionDescription"":"""&dataSource.inspectionDescription&""", ""lastInspectionDate"":"""&If(!IsBlank(dataSource.lastInspectionDate),dataSource.lastInspectionDate,currentTime)&""", ""inspectionNotes"":"""&If(!IsBlank(dataSource.inspectionNotes),dataSource.inspectionNotes,"none")&""", ""inspectionPosition"":"&dataSource.inspectionPosition&", ""inspectionComplete"":"&true&", ""inspectionResult"":"""&If(!IsBlank(dataSource.inspectionResult),dataSource.inspectionResult,"pass")&""", ""inspectedBy"":"""&If(!IsBlank(dataSource.inspectedBy), dataSource.inspectedBy, "unknown")&""", ""nestedTable"": ["& Concat( Filter( Ungroup( ForAll( Split(dataSource.nestedTable, "//") As nestedRecords, If(Last(Split(nestedRecords.Value, ",")).Value="fail", "{""Value"":"""&First(Split(nestedRecords.Value, ",")).Value&"""}",Blank())),"Value"), !IsBlank(Value)),Value,", ")& "] }"),Value, ", ")&"]";

where checklistInProgress is a local collection variable based on record I am working on

Was this reply helpful? Yes No
FernandoMT 28 on at

Like (0)

Report
Copy link

Link copied!

Thanks! This is certainly a solution although I would prefer not to do it fully manually if possible...

Was this reply helpful? Yes No
FernandoMT 28 on at

Like (1)

Report
Copy link

Link copied!
For anyone interested, this was the final structure I settled for, which sends a string to power automate, the conversion to a JSON object can be done in the flow side
Set(_jsonSend, "["&Concat( ForAll(colSelectDoc As dataSource, "{""Title"":"""&LookUp(Table(dataSource.Value.Cells),ThisRecord.Value.Key = "Title").Value.Value&""", ""HitHighlightedSummary"":"""&LookUp(Table(dataSource.Value.Cells),ThisRecord.Value.Key = "HitHighlightedSummary").Value.Value&""", ""Path"":"""&LookUp(Table(dataSource.Value.Cells),ThisRecord.Value.Key = "Path").Value.Value&""" }"),Value, ", ")&"]"); ColToExcel.Run(_jsonSend)

Was this reply helpful? Yes No