JSON, Level: Intermediate, Version: FM 16 or later

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.


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

12 thoughts on “JSON + Virtual List”

  1. 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?

    1. 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.

  2. 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?

    1. Hi Paul,

      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.


    2. 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.

  3. Kevin
    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[1] ; r = Get ( CalculationRepetitionNumber ) ] ;

    GetAsNumber ( JSONGetElement ( $$arrayJSON ; “json[” &i & “].col ” & r ) )

    ) // end let

    1. Hi John,

      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.

      Thank you,

Leave a Reply to Kevin FrankCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.