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.

2017-07-27_152145

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

2017-07-30_124959

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

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

2017-07-27_182751

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…

2017-07-27_171907

…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…

2017-07-27_173459

…and when it completes, the virtual list table reflects the data in $$arrayJSON.

2017-07-27_173701

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:

2017-07-28_100543

The definition is identical for all three repeating “cell_” calc fields; only the result type differs.

2017-07-28_100731

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

    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?

    Reply
    1. Kevin Frank Post author

      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.

      Reply
  2. apjcs

    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?

    Reply
    1. Kevin Frank Post author

      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.

      Kevin

      Reply
    2. Dan Rosenstein

      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.

      Reply
  3. john renfrew

    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

    Reply
    1. Kevin Frank Post author

      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,
      Kevin

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s