Which formula or custom widget is appropriate? I want to parse Json line items into rows.
Hi @MK_2109! Just parsing JSON is easy using Python:
import json json.loads($OrderLines)
i.e. literally type these two lines (starting with
import json) into a formula in another column (e.g. named
Parsed). The result will look the same as the
OrderLines cell, but it will be parsed JSON. So other formulas could use e.g.
$Parsed to get the first order line.
The second part of your question is trickier. Do you want the parsing to automatically generate new rows? Those would be rows of another table. I can suggest a trick using summary tables.
- Extract an identifier for the new rows: create a new column
orderLineIDwith the formula
[x["orderLineID"] for x in $Parsed].
- Turn this new column
orderLineIDinto a ChoiceList column. Ignore the fact that the choices have a red border: they are not recognized, but that doesn’t matter. (In fact, you can hide this column later.)
- Add a summary table, summarizing
PO_V7table by the new column
orderLines. It will get a row for each
- In this summary table, you can extract the JSON object associated with the
next(line for line in $group.Parsed if line["orderLineID"] == $orderLineID)
- If the column from step 4 is called
orderLine, then it’s easy to add more columns to this summary table using e.g.
$orderLine["price"], etc. In your case, you might want
float($orderLine["price"])to turn it from a string to a number.
Note that as a summary table, the data here is purely derived from the original table. This creates some limitations (e.g. impossible to add non-formula columns). You could turn it into a real table by using the “Detach” option of summary tables. Then things like data columns and linking would work as for regular tables. But once detached, the data won’t auto-update – e.g. if you add a new order to the original table, its lines would not appear in the detached table.
Here, I made an example: https://public.getgrist.com/oryL2cZyN6KR/Parse-JSON-into-Rows.
I included both a summary table and a “Detached” table. In the latter, I tweaked formulas to leave only the most relevant columns.