Render JSON string in SharePoint column as Data table

[mrrelational]

SharePoint list has a column that contains JSON data similar to this:

{ "ItemList": [ { "Item": { "Title": "Heart Failure Booklet", "UnitQty": 1, "UnitOfIssue": "workbook", "OrderQty": "5", "TotQty": 5 } }, { "Item": { "Title": "Chronic Heart Condition Booklet", "UnitQty": 1, "UnitOfIssue": "booklet", "OrderQty": "10", "TotQty": 10 } } ] }

and I am trying to customize the list's display form to show this in a datatable.  Note that this will not be edited. All I need to do is show it as a table.

I tried Table(ParseJSON(ThisItem.Items).ItemList) for the Items property of the Datatable. Note that it works for a Gallery, and I am able to render the various fields like this: Text(ThisItem.Value.Item.Title), but even though there is no error, the Datatable has no fields to display.

I removed the "Item" property name from the data and nothing changed.

So then I tried ForAll(Table(ParseJSON(ThisItem.Items).ItemList),
{Title: Text(ThisRecord.Value.Title),
OrderQty: Text(ThisRecord.Value.OrderQty),
TotalQty: Value(ThisRecord.Value.TotalQty)}
) as the Items property for the datatable. No error, but no fields.

So I removed the "ItemList" property name from the data (leaving just the array of objects) and the Table function. Still no errors. Still no fields. Same when I use Value.Title instead of ThisRecord.Value.Title.

I am trying very hard to understand what is happening, but I am at a loss with available resources. What have I missed?

[cdwhite]

Hi @mrrelational ,

I've tried with your original JSON and can't get it to work. Even tried adding additional square brackets either end but can't get into the sub nested objects.

However, I've tried to replicate at my end, please see if either of these options could help. Both are using a SharePoint list with a multiple line of text field to store the JSON, with slight tweaks to your JSON:

Option 1:
In the list, I stored the JSON string like so:

[{"Title": "Heart Failure Booklet", "UnitQty": 1, "UnitOfIssue": "workbook", "OrderQty": "5", "TotQty": 5  },{ "Title": "Chronic Heart Condition Booklet", "UnitQty": 1, "UnitOfIssue": "booklet", "OrderQty": "10", "TotQty": 10 }]

I have the list showing in a gallery, when I select the row I'm running the following Power Fx:

ClearCollect(
    colJSONOutput,
    ForAll(
        ParseJSON(ThisItem.JSON),
        {
            Title: ThisRecord.Title,
            OrderQty: ThisRecord.OrderQty,
            TotalQty: ThisRecord.TotalQty
        }
    )
)

Output into a gallery works:

Option 2:
A slightly different tweak to the JSON:

[ { "Item": { "Title": "Heart Failure Booklet", "UnitQty": 1, "UnitOfIssue": "workbook", "OrderQty": "5", "TotQty": 5 } }, { "Item": { "Title": "Chronic Heart Condition Booklet", "UnitQty": 1, "UnitOfIssue": "booklet", "OrderQty": "10", "TotQty": 10 } } ]

And therefore a slightly different tweak to the Power Fx to create the output:

ClearCollect(
    colJSONOutputv2,
    ForAll(
        ParseJSON(ThisItem.JSON),
        {
            Title: ThisRecord.Item.Title,
            OrderQty: ThisRecord.Item.OrderQty,
            TotalQty: ThisRecord.Item.TotalQty
        }
    )
)

Same output as option 1 when putting the collection in a gallery:

Not sure if either of these would work for you? You shouldn't have to do the Text, Value etc around each of the values unless you then want to sort or do other logic by the outputs. But, even with the Text/Value wraps, both methods above still work ok.

------------------------------------------------------------------------------------------------------------------------------
If I've answered your question, please mark your post as Solved. You can accept more than one post as a solution.

If my response was a good one, please give it a Thumbs Up!

Visit my blog: https://platformsofpower.net

[mrrelational]

@cdwhite  Thanks for the quick reply! I am having success with Vertical Gallery, and suppose I could try to mimic a table layout with that, but it seems like the "Data table" option should save me the trouble. Here is a screen shot of trying to use your first approach
and I am getting this error:

Also I have tried to create a collection OnSuccess of my SP form and use that as the Items for the DataTable, but an error free formula produces no data and the same formula as above gives the same error in the OnSuccess property.

[cdwhite · Accepted solution]

Hi @mrrelational ,

I'm wondering if it's because you're trying to embed a data table into a Form/Datacard? Data tables in Power Apps are still a preview feature I think and they have some known limitations. As per the documentation, I've spotted:

I think you would have to add the table as a separate entity in your SharePoint list form. From there, you should be able to reference a collection or even do the ParseJSON direct. I've tested my end, and able to use a collection for the table (using either one I built in yesterday's images) or even put the ParseJSON into the Items property of the data table:

I've mocked up a SharePoint list form to do what (I think/hope) you need. Note that the data table control on the left is outside of the main form control. The Items property references the relevant datacard.text for the JSON string to then populate the tbl:

Hopefully that helps you get a bit further?
------------------------------------------------------------------------------------------------------------------------------
If I've answered your question, please mark your post as Solved. You can accept more than one post as a solution.

If my response was a good one, please give it a Thumbs Up!

Visit my blog: https://platformsofpower.net

[mrrelational]

Well @cdwhite , you have given me clear and pertinent examples to work from, but I have not succeeded. I believe I have meticulously followed your second screen shot, which I can see succeeded for you. Sadly, it does not work for me. Unless you can see something I have done incorrectly here, I will abandon this approach and figure out something else. Thank you so much. I appreciate the time you have given to my problem.

[cdwhite · Accepted solution]

Hi @mrrelational 

Probably a really silly question and just checking - with the table control selected, have you used the option in the right-hand properties pane to manually select the fields? You need to do that with data tables, it doesn’t add them automatically. That’s the only thing I can think of.

Otherwise, feel free to private message me and we can set up a Teams call if you like, for a convenient time this week? It will bug me that we’ve not resolved this when I know we are super close!

[mrrelational]

That's it. I read, "There are no fields in this data table," and believed what it said. I did not think to click +Add fields because any other time I have dealt with data sources in PowerApps, the fields were automatically shown when the connection happened. This makes me wonder if any of the myriad other approaches I had tried would have worked similarly. I also wonder how something can be in Preview for 5 or 6 years and why the tool lets you add a datatable to a SharePoint Form if it knows it won't work. Who can know the mind of Microsoft?

[cdwhite]

Hi @mrrelational , brilliant! I’m glad we sorted it! You might be right, some of the previous approaches may well have been fine. You have this thread now to go back and try for your current or future builds. I’m a big fan of the Parse JSON function in Power Fx, fingers crossed it’s not in preview for 6yrs like Data Tables 😉