Kevin Frank, May 13, 2024, FileMaker Hacks
Source: https://filemakerhacks.com/2024/05/13/onwindowtransaction-json/
Demo file: on-window-transaction-json-v2.zip
Credentials: admin / admin
Minimum version: 20.2 [for both client and server]
Note: This demo file is optimized for Perform Script on Server (PSOS), and is intended to be hosted on FileMaker Server or FileMaker Cloud.
Introduction
Recently a colleague mentioned that they were running into a performance issue trying to load a complex found set (customers, invoices and line items) as an array of objects into a $$variable. It was simply taking too long across a WAN connection, and contributing factors included unstored calculations referencing related unstored calculations, and the rendering taking place client side as opposed to server side.
We’ve explored rendering a found set as JSON here previously…
…but both of the above involve constructing the JSON on the fly at runtime. My colleague was hoping for something faster, and today we’re going to look at a fresh approach.
Disclaimer
This is a proof-of-concept that I am sharing freely with the developer community. It may contain bugs or not behave the way you think it should. Use at your own risk, and with a healthy dose of skepticism and common sense. And, needless to say, treat it as a starting point and modify it as you see fit.
Architecture and Structure
The demo features a basic invoicing system for a winery, and includes these tables.
In every table the primary key is named ID, and the tables are related in the standard primary-to-foreign key manner, with lineitems serving as a join table to accommodate the many-to-many relationship between invoices and products.
The JSON to be harvested will take the form of one object per customer, in an array representing the current found set…
[ {object} , {object} , {object} ]…with each object corresponding to one customer and all their orders.
How Is This JSON Produced?
Each of the above tables includes an as_json unstored calculated field. To facilitate the JSON creation, the demo uses some custom functions…
- JSON.StandardFieldObject (JSON Custom Functions for FM, part 3)
- JSON.GetValueType (Thinking About JSON, part 4)
- JSON.ArrayFromList (JSON Custom Functions for FM, part 1)
…this last one making it easy to roll line items up into the invoice JSON, and again to roll invoices up into the customer JSON. Of course one could do this without using custom functions, but they streamline things both in terms of readability and code creation.
Here are the as_json field definitions:
In the original implementation, this summary list field in customers…
… produces a list of objects representing the found set…
{customer object}
{customer object}
{customer object}
etc.…which is converted via simple substitution into an array of objects where each object represents one customer and their order history.
[
{ customer object },
{ customer object },
{ customer object },
etc.
]Update 4 June 2024: with today’s release of FileMaker 21, the array of objects can instead be generated via the new JSONMakeArray function.
But Isn’t That Slow?
- Yes, as originally implemented… if we’re retrieving a lot of records and/or have a slow connection.
- No, in terms of what we’re going to do with it.
The original implementation can be slow, but we can trust it to be an accurate reflection of our found set. Wouldn’t it be great if we could have the accuracy without sacrificing performance? Something that would behave like an unstored calc, except stored, and not a calc?
Shadow Table to the Rescue
In case you aren’t familiar with the term, a shadow table has a one-to-one correspondence with records in a parent table, allowing you to have fields that appear to reside in the parent table, but that can be written to at any time, i.e., whether or not the parent record record is locked.
For example, imagine a business that does a huge volume buying and selling products — inventory levels are constantly changing. They have a requirement that the qty_on_hand field in the products table be stored (i.e., lightning fast to query) and always correct. By moving that field into a shadow table, where records can only be edited via scripted processes, you can ensure the field is updated in real time even if a product record is being edited (i.e., is locked) when its quantity on hand is being updated.
The only way a lock should ever occur in the shadow table is if multiple scripted processes attempt to update a particular record simultaneously — in which case the lock will be momentary and the scripted processes will be smart enough to accommodate that possibility, either by executing in a queue or re-trying till the lock has cleared.
And this holds true both in the preceding hypothetical case, as well as in today’s demo, where we’re going to store customer JSON in a shadow table…
…creating and updating records as necessary to keep them in sync with help from OnWindowTransaction (a.k.a. OWT).
And this in-syncness isn’t just about changes to customer records. Invoice modifications and deletions will trigger updates, and if a product name changes we will want that change to be reflected in the JSON for every customer who purchased that product. [It’s debatable whether changing a product name should cause retroactive updating of the historic invoice record, but in this case, assume that we want to see the current product name always and everywhere.]
Note, however, that customer deletions do not need to trigger an “update” since the shadow record will be deleted along with the customer.
At any rate, if we do this correctly, the contents of customers::as_json and its shadow field will always be identical. The former will be the engine that populates the latter.
If the file is hosted, the updating of the shadow table will take place server-side and will be unnoticeable to the end user. If PSOS is not an option (because the file is running locally or because you unchecked the PSOS box) then the update process will execute from inside a card window — dimming the background to make clear how long it takes.
[In the real world you probably wouldn’t use a technique like this in a standalone file; but I didn’t want to make hosting the file a requirement to see the technique in action.]
Rendering the Found Set as a JSON Array
When the user wants to retrieve a JSON representation of the found set (to pass to an API, for example) it will come from the stored records in the JSON table, and this retrieval can take place either client side or server side. For large amounts of data in a hosted environment server side retrieval is faster — though client side retrieval can be surprisingly performant if the amount of data being retrieved isn’t too large.
What does OnWindowTransaction Do?
OWT fires a script any time a record is committed or deleted in any table in a file where OWT has been enabled. If you are using Open/Commit Transaction or any other transactional process, the script will fire when the transaction is completed.
Note: Since the JSON table lives in the same file as customers, invoices, etc., we have code in place to ensure we don’t fall into an infinite trigger vortex when records in the JSON table are created or updated. We can’t stop OWT from firing, but we can nip it in the bud for tables other than our “big four” (customers, invoices, lineitems, products).
You can learn more about OWT in these informative write-ups by Wim Decorte and Alec Gregory.
Achilles’ Heels
Under normal circumstances you can expect OWT to always fire, which is why many developers are using it to build audit logs. Note that I say “under normal circumstances”. Here are some ways things could get out of whack (there may be others I haven’t thought of):
If you write to the file via Data API or OData endpoints, SQL (using a plug-in), or ODBC/JDBC, or if you invoke Truncate Table, OWT will not fire… in which case you will need to code proactively to ensure data integrity. The same would hold true if you delete a field containing data mirrored in the JSON table.
If you are logged in with [Full Access] credentials you can also circumvent OWT by terminating the triggered script via the Debugger.
And of course you need to ensure the integrity of the JSON table itself. Under no circumstance should end users be allowed to manually create, modify or delete records in this table.
With these limitations in mind, let’s proceed.
More Information
Here are trigger setup details.
We are calling an “on window transaction” script, and uniquely for the OWT trigger, rather than specifying a parameter, we can instead optionally specify a field name.
The reason we don’t specify a parameter is that OWT creates its own parameter. Let’s assume for a moment that I’ve enabled OWT but have left the field name blank. (I didn’t, but let’s pretend I did.) In this case by default OWT will look for a field named onWindowTransaction, but since that field doesn’t exist in our system no field data will be included.
Continuing with this hypothetical experiment, we can see what a bare-bones parameter looks like by generating a small found set in Customers and replacing the state value with itself.
The “on window transaction” script fires (on a replace, only once, not once per record) and the parameter looks like this:
The parameter is an object with one key per table (if there’s been any action in that table), and the corresponding value will be a 2-dimensional array with each entry representing one record. The first item in the array is the action (“New”, “Modified” or “Deleted”), the second item is the internal ID of the record, and the third entry is empty since (in this hypothetical example) I didn’t enter a valid field name in the trigger setup.
Returning to reality, in today’s demo, I’ve created a special helper field called owt in the four tables we care about; it is a lightweight calculated JSON object that varies from table to table. In customers its definition is…
and because I’ve specified owt as the field name…
…if I rerun the above experiment, the parameter will look like this:
Now, instead of "", the third item in each array is a JSON object as per the owt field definition. In customers and products we only need to pass the primary key (i.e., a single key/value pair); but in invoices and lineitems the owt object will contain two key/value pairs (to accommodate the edge case mentioned below).
What’s The Purpose Of The Parameter?
It enables us to pass information to the “on window transaction” script that creates and updates records in the JSON table.
Things You Can Do With The Demo
You can experiment by checking and unchecking these three boxes on the Customers layout…
…and by exploring the scripts available to you on the Scripts menu.
If the file is hosted on FileMaker Server or FileMaker Cloud you probably want the PSOS box to be checked, and the “wait for completion” option only pertains to JSON “write” operations — to “read” JSON via PSOS the system must wait for completion and that check box will be ignored.
Dev / Nav / Etc.
These are developer scripts and you should feel free to explore and experiment. The “gtrrfs” in some of the script names stands for “go to related records for the found set”. For example if you are in a found set of products or invoices, running “gtrrfs – to customers” will take you to the related set of customers.
Note 1: The only script to be careful with is… you guessed it: “truncate json table”. Make sure you really want to clear out the json table before you run this script. But if you do run it, don’t worry: it’s easy to re-populate the JSON table; see below for instructions.
Note 2: As you experiment with the file, a number of $$variables will be populated with elapsed timings in milliseconds, the JSON retrieved, etc.
If things get too congested, run the “clear $$variables” script to make them go away.
Writing JSON
You can create/update/delete JSON records…
a) by manually making changes in any of the four main tables (changes to a product will only update records in the JSON table if that product appears on an invoice), or
b) by running one of the “write json” scripts indicated by a check mark.
(The unchecked scripts are invoked automatically, and are not meant to be run individually.)
Note: the easy way to regenerate the JSON table is to…
- run the “truncate json table” script
- show all records in customers
- run “customer found set – replace state with itself”
And this might be a good time to mention that Steve Senft-Herrera’s JSONQuery custom function makes various appearances in “on window transaction” and “sub: update json” to construct lists of values from specific columns within 2-dimensional arrays… this is something that could instead be accomplished via the While function or within a scripted loop, but JSONQuery makes it easy, and that’s the route we take here.
Reading JSON
To view the JSON for one customer, go to that customer record and evaluate either of these statements in the Data Viewer… they should return identical results.
To view a JSON representation of a found set of customers, run one of these scripts and view the results on the “current” tab of the Data Viewer.
1. retrieve json from customers – This is the “slow” method my colleague was complaining about… unstored calculations referencing other unstored calculations, and the retrieval taking place client side.
2. retrieve json from json table – this executes client side but is much faster… objects within the array will appear in JSON record creation order.
3. retrieve json from json table – replicating customer sort order – again, this executes client side, but objects within the array will reflect the current customer sort order.
4. psos: retrieve json from json table – replicating customer sort order – this invokes a server-side script, which uses the List() function to build up the list of customer IDs to process, and a “transfers” table (rather than a script result) to pass the data back to the FM client.
5. psos: icr retrieve json from json table – replicating customer sort order – almost identical to the preceding except it uses “Insert Calculated Result” rather than List() to build the list of customer IDs.
[See “Tip #7: A faster way to incrementally build a large block of text” here for why it might be worth doing. Spoiler alert: in our case the list won’t be large enough for it to make an appreciable difference.]
Why use a “transfers” table? Because PSOS has a 1 million character limit on both script parameters and on script results. The transfers table allows us to bypass that limitation. Why is text being inserted into a container field, as opposed to a text field, in the transfers table? Because container fields can accommodate considerably more data than text fields (for more info see FileMaker Pro 2023 Technical Limits)… though in this case it’s more a matter of principle than a practical concern.
An Edge Case
The parameter passed by OnWindowTransaction does not tell us what the data changed from, only what it changed to. In other words, we see the “after” but not the “before”. Most of the time that’s fine because we aren’t building an audit log and we care about the current (as opposed to the former) state of our data.
But bearing in mind the goal that the JSON table always contain an accurate (stored) version of what we can see via the (unstored) as_json field in customers… what happens if a customer ID on an invoice changes to a different customer ID? Or what if the invoice ID of a line item from an invoice belonging to customer A is changed to a different invoice ID that belongs to customer B?
You may say, “that’s unlikely to happen”, but I can think of several reasonable scenarios where it could, and at any rate, I wanted to accommodate that possibility. For example, let’s say you create and commit an invoice with lots of line items for customer A, but then a few minutes later you realize you meant to do it for customer B. Rather than delete and redo all that work, you instead change the value in invoice::id_customer, repointing the invoice from customer A to customer B.
This turns out to be a bit complicated because when you commit that invoice, the parameter generated by OWT isn’t going to tell you what the original id_customer value was, only what it is now… which is fine in terms of updating customer B’s JSON, but we also need to update customer A’s JSON… which we can do if we can somehow get customer A’s ID loaded into the $custlist variable in the “sub: update json” script.
Fortunately the original linkage of the invoice to customer A is preserved in customer A’s JSON record… see json::key_list (thank you once again JSONQuery) and steps 40-42 of “sub: update json” for how this edge case is handled.
Closing Thoughts
As mentioned at the outset, this is a proof-of-concept and I’m guessing it will continue to evolve. Ideally the technique can accommodate the twists and turns of a complex Open/Commit Transaction routine as well as more straight-forward scenarios.
If you have suggestions for improvement, find a bug, or notice something I have missed, I hope you’ll post a comment here.
