JSON + Virtual List

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.

One nice thing about FileMaker being on a yearly release cycle is that there is always something new to learn and write about… and, having recently attended a pair of highly informative sessions on the topic of JSON (JavaScript Object Notation) at Devcon 2017 — thank you Todd Geist and Anders Monsen — it is clear to me that JSON + virtual list is a compelling combination.

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.

Introduction

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.

An Example

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[1] renders “col 1”
• cell_text_r[2] renders “col 2”
• cell_text_r[3] renders “col 3”
• cell_num_r[4] renders “col 4”, and
• cell_num_r[5] 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 Conclusion

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