31 July 2017: As per my exchange with Beverly Voth in the comments section of part 2, the date encoding technique used here is strictly intended for situations where FileMaker will both produce AND consume the JSON.
29 July 2017: demo has been updated to require a minimum version of FileMaker 16.02 as per my exchange with John Renfrew in the comments section.
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 mainly 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.
In an upcoming article, 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
12 thoughts on “JSON + Virtual List”
Nice and clear.
Thanks for saying so Dan.
Thank, Kevin! I only asked for this yesterday. You really got busy! One question: what is the purpose of storing the found count? Were you thinking you were going to use it in your loop and then didn’t, or was there some other purpose that I missed?
Good catch, and guilty as charged. My initial plan was to use GetNthRecord to walk the Sales rows virtually, but that didn’t work because I needed to grab related data also. The original plan was to exit the loop when $index + 1 >= $fc. But having changed my approach, the $fc is not necessary, has been removed, and the article and demo file have been accordingly updated.
P.S. You were not the only one asking.
P.P.S. You’re welcome.
Great to see this JSON based virtual list, but I have a question. What advantages do you think this approach offer over the established virtual list techniques?
I’ve seen any number of different virtual list implementations over the years — and have experimented with various approaches myself over the years. So one advantage is standardization, and another is readability.
I’ll touch on some additional advantages next week when I post my article/demo on virtual list reporting + JSON.
Thanks Kevin. That was my question. Look forward to next week
I suppose one possible use case is if you are pulling data directly into FM from an API (e.g. addresses, a list of clients from an eCommerce site etc.) that you don’t necessarily want to keep in FM.
The date calc doesn’t work in 16.0.1 which returns text rather than a number so a more protective calc might be
Let ( [ i = ID ; r = Get ( CalculationRepetitionNumber ) ] ;
GetAsNumber ( JSONGetElement ( $$arrayJSON ; “json[” &i & “].col ” & r ) )
) // end let
You make a very good point. Given that 16.02 is a free upgrade, I’m not interested in adding additional layers of complexity to what is already a somewhat complex subject in the name of backwards compatibility. On the other hand, I don’t want to confuse people running 16.01 who might download the demo, so I’ve decided trap for minimum application version on startup, and have modified the demo accordingly.