Audit logging in FileMaker

OnWindow Tranaction functions enable Audit logging

In FileMaker 2023, Claris® introduced the ability to do audit logging within the FileMaker platform, greatly reducing a system’s code footprint when compared to current approaches. In this blog post we will look at the new OnWindowTransaction script trigger, which, when used with other native FileMaker scripts, can create an audit log that is easily implemented, efficient, scalable, and flexible.

What is an audit log?

Simply put, an audit log is a record of all data activity in a system over time, While the use cases for audit logs are diverse and often bespoke – ranging from data integrity to analytics to compliance – any audit log starts with tracking what data has changed, when it was changed, and who changed it.

Until now, Claris® FileMaker has not had a native auditing capability, relying instead on third party add-ons such FmDataGuard. Claris® FileMaker 2023 resolves this by having the capability built natively within FileMaker which makes it easy to design correct audit logging for each bespoke application.

OnWindowTransaction is a file-level script trigger added in Claris FileMaker 2023. In many ways it operates like the other file-level script triggers introduced in earlier versions of FileMaker, (OnFirstWindowOpen, OnLastWindowClose, OnWindowOpen, OnWindowClose), but utilises additional functions.

Key features of the OnWindowTransaction script trigger

When using the OnWindowTransaction script trigger, there are several things to consider:

    • OnWindowTransaction triggers after every commit that takes place in any window and it covers every record change, not just those that take place in a script transaction
    • OnWindowTransaction has access to a JSON object of metadata about the records in the triggering transaction which includes the affected Record ID and the type of action, logged as New, Modified, or Deleted.
    • OnWindowTransaction has read access to the contents of every single field in every table. That field can technically be of any type (Text, Number, Date, Time, Timestamp, Container) but the field contents will be returned as either text or JSON (if the field contains a valid JSON object or array). It can also be any class (Normal, Calculated, Summary).
    • OnWindowTransaction has the Continue This means it will run after a currently running script rather than interrupting it.
    • OnWindowTransaction is not currently supported for OData or Data API endpoints that modify record data or changes made via xDBC. However, it is supported in scripts run via OData or the Data API.

Audit process

There are four basic steps which take place in the audit process which need to be considered when designing your audit log:

    1. A transaction completes.
    2. FileMaker collects audit data for each record in the transaction, including data in the OnWindowTransaction field if it exists in the table.
    3. The OnWindowTransaction script trigger fires.
    4. The triggered script processes and stores audit data in the AuditLog table and, if necessary, schema data in the SchemaLog table. These tables are stored either within the solution file, which makes the log convenient and simple. Or externally, using a wide variety of suitable options including a different FileMaker file, a different database such as Oracle, MySQL, PostgreSQL, mongoDB, or MSSQL, a cloud service such as AWS CloudWatch, or a structured data file (such as a CSV file).

To understand more contact us using the form below or check out the information of the Claris support page regarding Audit logs here