FileMaker 2023 – “Audit log”

As we saw in this detailed review of what’s new in FileMaker 2023, one of the two flagship features, along with Perform Script on Server With Callback, is what Claris calls “Audit Log.”

There is so much to know about this feature that we preferred to dedicate a full post to it.

First of all, let’s agree that even if Claris’ marketing didn’t resist the temptation to call it that, this feature is not a real audit log, and this for several reasons.

• First of all for a simple reason: this feature does not log anything. It does allow a developer to build an audit log on top of it.
• Then an audit log, to be worthy of its name, must meet certain requirements of inviolability (it must not be possible to make a modification without it being logged, it must not be possible to modify an entry in the log…). Even if we will see that this is the best FileMaker could offer, the new functionality does not meet these criteria.

Does this mean that you should pass by without looking? Not at all! The new feature is very exciting, but as Camus said, “to name things wrongly is to expose oneself to criticism” (or something like that). And exposing yourself to criticism from an external auditor who was sold an “audit log” that wasn’t an audit log… that’s a situation I’d rather avoid.

So no, the audit log is not an audit log. So be it. What should we call it then?

This is the “technical” name of the feature. This is the name under which it is found in FileMaker Pro, and it seems to me to be much closer to reality.

Window transactions show up as a script trigger in the file options, and will apply to all transactions.

Once enabled, every transaction (or almost every transaction) will trigger the script.

What is a transaction ? A transaction is not particularly what Claris called a transaction in version 19.6.
A transaction is simply the act of creating, modifying, deleting a set of records (including a single record) and committing. It already existed before 19.6, even if 19.6 allowed to write transactions more easily, or to make some actions “transactional” when it was not possible before.
Note that the modification of a global field, which is not strictly speaking a data modification, does not cause a window transaction, (except of course if the modification of a global field leads to a modification of another field, which is standard, by means of an calculated result auto-entry).

In short, you have understood what a data transaction is. During a transaction you can :

• create new records
• modify records
• delete records

Window transactions are the closest thing FileMaker has to a data transaction, or more precisely to an event that takes place on a data transaction.

But these are window transactions. So you still need a window, even a virtual one.

In other words, direct interactions with the data layer are excluded from these transactions:

• Data API (unless it executes a script)
• OData (unless it runs a script)
• PHP/XML (unless running a script)
• ODBC
• Truncate table script step
• A file is defined as an external data source of an interface file in which window transactions are enabled (active trigger), but data is modified from another file.

That’s why even if a log system was provided, this feature would not allow a real “audit log”.

As we have already mentioned, the configuration interface can be found in the file options (File Menu)

On peut sélectionner le script qui sera activé quand on valide une transaction, et indiquer facultativement le nom hard-codé d’une rubrique. Comme pour les autres déclencheurs au niveau du fichier, seul un script du fichier en cours peut être sélectionné. Dans ce cas ci, c’est un peu dommage, mais on comprend bien la raison.

Quand une transaction est validée, le script sera déclenché et recevra automatiquement un paramètre en JSON tel que :

{
   "fileName":
   {
       "tableName":
       [
             [ //(for each record):
                  modification type ("New", "Modified" ; "Deleted"),
                  record ID,
                  "optional parameter"
             ];
             [ 
                  modification type ("New", "Modified" ; "Deleted"),
                  record ID,
                  "optional parameter"
             ]
        ]
   }
}

As you spotted at first glance because you are now JSON experts, the modified records are elements of an array, with 3 pieces of information so you need to know the order: the type of modification (New, Modified, Deleted), the record ID, and the optional parameter, which will be present but empty if you didn’t set it.

The other thing you immediately noticed is that there is no trace of modified data or field names.

The content of the optional parameter comes from the field that you can choose in the file options.

If you did not specify a field name and there is a field named OnWindowTransaction, this field will be taken into account instead.

So at the time of the transaction commit, for each record created, modified, or deleted (in this last case the field is evaluated before deletion), the field -which can of course be calculated- is evaluated and its content passed as an optional parameter for this record.

JSON

Note that if the content of the field is a JSON, it is not rendered as a string (“stringified”). To be very precise, in order not to waste too much time validating each JSON, which would greatly slow down transactions with many records, FileMaker considers that if the content of the field starts and ends with { and } or [ and ], it is a valid JSON.

There are nine ways to edit multiple records in one operation with FileMaker.

Some of them are perfectly handled by the window transactions, others are not.

Let’s eliminate the ones that are not handled first, but keep them in mind if we really want to talk about audit logging:

• External requests directly on the data layer (Data API, OData, PHP/XML, ODBC). In the case of script execution (and therefore the existence of a window), window transactions will take place.
• Truncate table script step.
• Modification of the data schema (you can delete a table, create a calculated field or modify a calculation formula, validate the modification, then change the type of field to make it a standard field, and the data has been modified without a window transaction.
• Let’s add a variant of this: file creation by conversion. If, for example, you drag an Excel file onto the FileMaker icon, you will obtain a file with a table and data, even before you have been able to configure any triggers or scripts.

Handled methods are :

Non-transactional methods :

• Imports (Import records, Importer folder, Importer from ODBC source) can create or modify data (or both at the same time).
• Delete all records (or Delete found records if you have made a selection beforehand)
• Replace field contents and Relookup Field Contents, for they are in fact the exact same thing.

Transactional methods:

• Transactions through relationships, as they have existed since FileMaker became relational (FileMaker 3, released in 1996): you can modify the main record (the one displayed) as well as create, modify and delete related records (deletion requires a portal object).
• FileMaker 19.6 transactions, which are the same thing except that :
  • they can be done while changing context (no need for a relationship anymore)
  • they can include other types of mass transactions (import, delete all records, replace field contents)

Importance of using “19.6” script transactions for bulk transactions

For transactional methods, it is quite obvious: a transaction will cause one and only one window transaction (all created, modified and deleted records will be in the script parameter)

As for the other three methods (import, delete all, replace), it is very advantageous to also include them in a transaction.

Indeed, as opposed to our FM AuditLog Pro (which will obviously be updated very soon to take advantage of this new feature) which managed to combine the different real transactions into one logical transaction, the window transactions exactly reflect the internal workings of FileMaker.

For example, when you delete all the records, FileMaker deletes them 100 by 100. When you import, it depends on the format of the file (in increments of 25 for the csv format, but in alternating increments of 25 and 1000 for the .mer format (a csv with the column headers), in increments of 500 for Replace field content…
In short, that’s all I had discovered while developing FM AuditLog Pro, but this “internal popote” is surely not interesting for the user and we would have preferred that an import or a deletion be summarized in one transaction. But this is not the case, the OnWindowTransaction script will be triggered as many times as there are internal transactions. It is therefore very important to encapsulate these operations in a script transaction.

Tip : if the user himself can perform an import, a deletion of the found set, or a field contents replacement, we recommend to use the custom menus to replace these commands, with associated scripts such as:

Open transaction
   Import records
Commit transaction

There remains the marginal case where the user is already in the case of a transaction with a paused script. If we want to avoid error 3 for nested transactions (an error that is not a problem but it is less pretty and we like it to be pretty), then we can write :

If [ Get ( TransactionOpenState )]
   Import records
Else
   Open transaction
      Import records
   Commit transaction
End if

Data API, OData, XML

As far as these modes of interaction with FileMaker are concerned, there is the possibility of giving preference to scripts. In order to guarantee the validity of an audit log (in the strict sense), it can be ensured in the security settings that these modes of interaction can only modify records if a script is running.

not isEmtpy ( Get ( ScriptName ))

Unfortunately, this comes at the cost of reduced performance.

We can really regret that the script step Execute FileMaker Data API has read-only access. It would be so much easier to convert the API calls into scripts…

Since the beginning, two events have been distinguishable in the way of modifying data: drag and drop and Replace Field Contents.

Indeed, these two events have the particularity of being able to operate on records not previously opened, to modify the records, and to keep them “closed”, without triggering an OnRecordCommit event.

Well, that’s great news: window transactions can now capture these events. If the active record is not open when you start a replace action or drag content onto a field, then a window transaction will be triggered after the event.

In terms of data integrity, this is a big improvement!

One of the difficulties encountered when setting up a script to log transactions is to disable the script after recording the transaction.

Remember, all transactions taking place in a file window trigger the script.

Workaround: write at the beginning of the script an exit condition like

If [ Get ( LayoutName ) = <theLayoutOnWhichILogTransactions>
   Exit Script
End If
The rest of the script

An annoying bug

The version released today has a very annoying bug. We hope that it will be fixed in a future update.

When running the onWindowTransaction script, the Close Window is simply ignored. It does not return an error but has no effect. – update: this is fixed in 20.1.2, released on Jun-7.

If you’ve read this far, you’ll be rewarded, because beyond the technical aspects, the big question is: “what to do with this new feature?”

As you will see, the potential is enormous .

Examples of use:

• Of course, keep track of the changes. This makes sense, but it should not be forgotten
• Updating related records, or even “views”. It’s an eternal challenge with FileMaker -without table triggers (OnUpdate)- to update related records. For example, if I update the amount of a payment, I want the corresponding invoice to be updated, as well as the customer record (to know the balance). From now on, I can definitely detect that a record in the PAYMENTS table has been created/modified/deleted and update the corresponding records.
• It is easy to imagine view tables that replace list views and that synthesize the information that the user needs to see, without calculation and without links, in order to optimize the scrolling or sorting speed.)
  For example, a record in the CUSTOMERS table, which is linked to n invoices, n orders, n payments… can have its counterpart in the CUSTOMERS_VIEW table, with standard (non-calculated) fields that allow for very fast lists. It is indeed relatively easy to develop a logic that causes such a record to be refreshed as soon as a payment, an order or an invoice is modified in a transaction.
• With a tool like FM AuditLog Pro 3.0 in preparation, it will not only be possible to roll-back transactions as with FM AuditLog Pro 2.0, but it will also be possible to re-execute transactions on another server, for example in a scenario where users work on several continents, each on their own server, but where changes need to be rolled-back to a central server.

So, I hope that these few hints have helped you see the potential of this new feature and that you enjoyed this article enough to share it on the social networks.