Over the last few postings we’ve been looking at using JSON with Virtual List… and prior to today, our focus has been on JSON structures using name/value pairs. Well today we’re going to switch our attention to basic JSON array structures.
Apart from using JSON arrays instead of name/value pairs, today’s demo is quite similar what we saw in JSON + Virtual List, as well as in JSON + Virtual List part 2, and to avoid repetition, what follows will assume the reader is familiar with the material in those articles.
As before 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 like so:
The basic idea:
- Loop through the records in the Sales table, encoding data into a JSON object with help from JSONSetElement
- Display the virtual list table, using JSONGetElement to decode the data into calculated repeating fields
So, what exactly is a JSON array? For our purposes today, it can be defined as a series of comma-separated values enclosed in brackets, and here is the JSON object with five arrays that appears in today’s demo.
To the left of each array you can see its name (0,1,2,3,4) corresponding to the first five rows of the virtual list table (where ID number begins with 0, not 1). And since JSON uses a zero-based index, individual array items are parsed like so:
Explanation: get the zeroth item from array number 3.
Here are field definitions for the virtual list table:
The definition is identical for all three repeating “cell_” calc fields; only the result type differs.
Incidentally, there’s a minor bug specific to JSON arrays, and it occurs under very specific circumstances. Note that the data in the highlighted field has been entered as “.8”, i.e., without a leading zero.
When the “Generate Array” routine is run, note the emptiness of the ID 3 row.
The virtual list table can’t render it, because the corresponding array is missing.
Now add the leading zero into the field…
…and voila, no more pesky problem.
What the heck is going on? There seems to be a bug in JSONSetElement where numbers greater than zero but less than one are not rendered unless you prepend a leading zero. This occurs not only with manually entered data, but also as the result of calculations, e.g., 2 – 1.2 = .8 (not 0.8).
Now remove the leading zero, and this time run the “Protected” routine…
…and this time the array generates properly…
…because the routine prepends zeros to cost and price as a defensive measure (but only when necessary).
Finally I want to mention an interesting behavior associated with JSONFormatElements when applied to JSON objects containing arrays. Add the word “Orchestra” to “Modesto Philharmonic”…
…and note that JSONFormatElements now renders that particular array vertically.
At the risk of stating the obvious, this is merely a formatting quirk and in no way interferes with JSONGetElement’s ability to locate and extract the data.
(Also, since we’re running the “unprotected” version of the routine, and since when these screen shots were taken cost for row 4 contained “.8” and not “0.8”, array number “3” is empty.)
My first thought was that if any array item exceeded a certain number of characters, that would tip the array from horizontal to vertical, but the tipping point seems to apply to the overall length of the array, rather than to an individual item. For example, without removing the “Orchestra” from the customer name, change the cost and price values in that row to 1 and 2 respectively…
…and the array returns to the horizontal.