Demo file: json-plus-virtual-list.zip
My original plan was to integrate JSON with virtual list reporting, but in the interest of clarity, I decided to save that for a future article. Today we have a straightforward demo that a) generates a small amount of JSON from a standard FileMaker table, and then b) renders it in a virtual list table.
There have been a number of introductory articles on FMP + JSON over the last few months, so I’m not going to recapitulate material that has already been well covered (for more information, see the list of resources at the end of this article). Nor will I be explaining the concepts behind virtual list — if you’re not familiar with it, or need a refresher, see Virtual List Reporting, part 1.
Today we are going to first create some JSON using FileMaker’s built-in JSONSetElement, and then we are going to parse that JSON using calculated repeating fields in the virtual list table, via a second FileMaker function: JSONGetElement.
To quote from the Introduction at json.org…
…and these two structures are often combined in creative ways. However, today we are only concerned with the first structure: an object consisting of name/value pairs.
Here is a small JSON array we might create via JSONSetElement, and below you can see how we can retrieve individual bits of data from that array using JSONGetElement (bearing in mind that JSON uses a zero-based index).
Note 1: the above JSON was made pretty via the JSONFormatElements function, which also alphabetizes the elements within a given object, regardless of the order in which the elements were created.
Note 2: Since JSON does not have a “date” type, I’ve chosen to encode dates as numbers, thereby avoiding any possible international confusion re: how to interpret, for example, “8/1/2017”.
Generating the JSON
In the demo, this script generates the JSON by looping through the sales records, populating/updating a variable called $$arrayJSON.
As mentioned above, JSON uses a zero-based index, hence the setting of $index to 0 in step 6, which then is incremented at step 11 — basically $index is a counter that starts at 0 instead of at 1.
After the loop exits, $$arrayJSON contains this…
…and you’ll notice that the “names” in our name/value pairs are not “date”, “customer”, etc., but “col 1”, “col 2”, “col 3”, “col 4” and “col 5”.
Parsing the JSON
Here’s the remainder of the script…
…and when it completes, the virtual list table reflects the data in $$arrayJSON.
At the risk of stating the obvious…
- cell_date_r renders “col 1”
- cell_text_r renders “col 2”
- cell_text_r renders “col 3”
- cell_num_r renders “col 4”, and
- cell_num_r renders “col 5”
…and each row, beginning with the first row (where the ID is 0, not 1), corresponds to one of the five JSON objects generated when we looped through the rows in the sales table.
Here are field defs for the virtual list table:
The definition is identical for all three repeating “cell_” calc fields; only the result type differs.
Next week we’ll extend this technique to work with virtual list reports.
Links to FM + JSON articles and FileMaker JSON help pages
- Seedcode: FileMaker 16 JSON Script Parameters
- DB Services: FileMaker JSON Functions
- Skeleton Key: FileMaker 16 – New JSON Functions
- Jeremy Bante: FileMaker JSON Type Handling
- FileMaker Pro 16 Help: Working With The JSON Functions
- FileMaker Pro 16 Help: JSONFormatElements
- FileMaker Pro 16 Help: JSONSetElement
- FileMaker Pro 16 Help: JSONGetElement