Connecting Portals to JSON Arrays

Have you ever wished you could connect a portal to a JSON array? Portals and JSON arrays seem like they should be a natural fit, but FileMaker doesn’t offer us an obvious way to connect one to the other.

(Why would you want to do this? One use case would be to provide dynamic scrollable selection criteria for a report.)

At any rate, today we’re going to take a look at a little proof-of-concept I threw together to enable portals to display and edit data in JSON arrays. In a real-world implementation, the JSON would likely be sitting in a $$variable, which, among other things, would help make the technique multi-user friendly. Here, in the interest of simplicity, I’ve opted to store the JSON in a regular text field. A couple benefits of doing so:

• You will see changes made in the portal immediately reflected in the JSON, and vice-versa.
• As you navigate from record to record within the demo, the portals will reconfigure themselves to accommodate the corresponding JSON.

(Yes, it’s possible to accomplish the preceding with variables as well, but the aim here is to keep things simple).

Demo file: connecting-portals-to-json-arrays.zip

Let’s take a look at what’s going on. The first thing to notice is that the three portals correspond to the A, B and C nodes in the JSON object, and that the item at index position zero in each array corresponds to the label above the first column of each portal.

These labels are “pseudo-calcs”, i.e., single-segment button bars that don’t perform a script, but have been drafted into service as calculation objects. (More on this below.)

If we switch to record #2, the contents of the A/B/C nodes have changed and the portals reflect this.

Here are table definitions…

…and here are field definitions. In the picker table, the “json” field contains the source JSON for the record, and the other three fields will be discussed below.

The virtual_portal table consists of a single serial number field beginning with 1, and currently has 500 records. If you ever need to display more than 500 rows in any of the portals in this demo, you will need to add more records to this table. Note that for the portals to display their contents correctly, records in this table must have a sequential value in ID starting with 1, and there can be no gaps. Since there are 500 records, the IDs are 1-500, and the next serial value for ID is 501.

Here is the relationships graph…

…and here is the picker layout in layout mode.

Note: Groups A/B/C each have a hide condition attached so that they will only display when there is something in the corresponding JSON node. (To demonstrate this, records 3/4/5 intentionally have nodes missing.)

As you’ve probably guessed, Groups A, B and C all behave similarly, so let’s just focus on Group A.

Here is the pseudo-calc definition for the portal column 1 label:

Explanation: display the “zeroth” (i.e., the first) element in the “A” array.

What about the portal? As you might expect, there are several interesting things going on in there.

Within the row are five items: two pseudo-calcs, a global “text” field (stacked on top of the column 2 pseudo-calc, and used for editing note text), and, at the far right, two stacked calculated fields (to display checked and unchecked check boxes).

To make the point that these fields are used for display and data entry purposes only, and are not connected with the virtual_portal table in any meaningful way, I have defined them in the picker table, rather than in the virtual_portal table.

Also note that the stacked pairs in columns 2 and 3 have hide object conditions so that only one item in each stacked pair is visible in any given row.

Here’s the definition of the column 1 pseudo-calc inside the portal.

Explanation: derive a row from the JSON corresponding to the id number in virtual_portal, and then display the “zeroth” element from that row.

The definition of the column 2 pseudo calc is identical, except, as you might expect, it targets a different column in the array. Here it is, revealed from beneath the global “text” field which I have temporarily hidden.

Speaking of the global “text” field, it has three script triggers attached, and these triggers are responsible for:

1. pulling a value from the JSON
2. pushing that value back to the JSON (if it changed)
3. clearing the field

And, finally, here’s the filter logic for portal A.

Explanation: display a row for each array row except for the “zeroth” row (which, as we saw earlier, contains a single element, and appears above the portal as the column 1 label). Since we can disregard the zeroth row, the ID number can be used to directly target the corresponding array row.

A few final thoughts:

1. This is a proof-of-concept. Use with caution and a healthy dose of common sense.
2. I have not tested this in WebDirect or FileMaker Go.
3. This is a work in progress, and I am open to suggestions for improvement.
4. On Windows, if one types fast enough into the notes (global “text”) field, one can out-pace the OnObjectModify trigger and end up with some keystrokes not being recognized. This is not a problem on the Mac, only on Windows. The obvious workaround would be to instead go with an OnObjectSave trigger. I went with OnObjectModify in this demo purely for the enjoyment of seeing the JSON update in real time… and because, having already coded to address a couple platform-specific issues, I was, until recently, under the impression the problem had been resolved — as things stand now, you have to type very fast for the problem to occur.
5. If one were so inclined, the editable global text field trick could be repurposed to allow editing of virtual list data. Food for thought. Hmmmm….