Stathis Askaridis, Sep 4, 2020, Claris Engage 2020

Table of Contents

1. Introduction
2. Understanding FileMaker Transactional Operations
   • What is Record Commit, Revert, and Record Locking?
   • How Transactional Operations Work
   • Key Functions to Monitor Record State
3. Implementing Safe and Transactional Edits
   • Breaking Down the Record Editing Workflow
   • Creating Globalized Script Steps
   • Transactional Editing with Save and Cancel Functionality
4. Configuring the Save/Cancel Add-on
   • Installing and Using the Add-on
   • Step-by-Step Configuration
5. Introduction to FileMaker Audit Logs
   • What is an Audit Log?
   • Determining What to Audit
   • Stakeholders and Audit Log Usage
6. Creating a Native Audit Log in FileMaker
   • Key Pieces of the Audit Log Schema
   • Implementing Audit Log Tracking
   • Handling Large Data Capacities
7. Developing Record Rollback Functionality
   • Challenges of Rollback Implementation
   • Rebuilding Record States
   • Step-by-Step Guide to Rollback
8. Deploying the Audit Log Solution
   • Understanding the Audit Log Schema
   • Practical Demo: Record Tracking and Rollback
   • Advanced Use Cases
9. Summary and Key Takeaways
10. Resources

Introduction

In this blog post, we will delve into a comprehensive exploration of Audit Log and Transactional Edits in FileMaker. The focus will be on creating a native, configurable audit log that uses transactional operations to maintain data integrity, secure critical operations, and provide rollback capabilities.

This post is inspired by the session conducted by Stathis Askaridis from SICOA, where he presented a robust solution for a safe, user-friendly audit log leveraging FileMaker’s transactional capabilities. We’ll break down each component step-by-step to guide you through creating an audit log solution that works as a “built-in time machine” for your FileMaker application.

Understanding FileMaker Transactional Operations

FileMaker’s transactional operations form the foundation for creating a reliable audit log system. The three primary concepts to grasp are Record Commit, Revert, and Record Locking.

What is Record Commit, Revert, and Record Locking?

Record Commit is when changes to a record are saved and made accessible to other users. It’s what happens when you save your edits.
Revert restores a record to its state before editing began, effectively discarding any unsaved changes.
Record Locking is FileMaker’s mechanism to ensure that no two users edit the same record at the same time. When a user opens a record, FileMaker automatically locks it.

How Transactional Operations Work

In FileMaker, transactional operations are essential for maintaining data consistency. Here’s how each action fits into the transactional model:

1. Committing a Record: When a user saves changes, the record is exited, data is updated, and other users can view the changes.
2. Reverting a Record: Restores the data to the state before any modifications, making it a robust cancel mechanism.
3. Record Locking: Prevents concurrent modifications by locking the record during an edit, ensuring only one user edits at a time.

Key Functions to Monitor Record State

To handle these operations effectively, you can use the following functions:

• Get(RecordOpenState): Checks if a record is new (1), edited but not committed (2), or committed (0).
• Get(ModifiedFields): Returns the fields that have been modified in the current session.
• Get(RecordOpenCount): Shows how many records are currently open for editing.

Implementing Safe and Transactional Edits

To achieve reliable and user-friendly data operations, we need to break down the data entry and editing workflow and implement a transactional approach.

Breaking Down the Record Editing Workflow

A typical data entry process includes:

• Editing a Record: User modifies data, but changes are not yet committed.
• Saving or Cancelling: User chooses to save changes, triggering data validation, updating counters, or creating records in other databases. Alternatively, the user cancels, reverting any changes.

Creating Globalized Script Steps

Creating globalized scripts allows you to manage transactional operations consistently across your FileMaker application. These scripts centralize operations like:

• New Record
• Delete Record
• Commit Record
• Revert Record
• Delete Portal Row

By using global scripts instead of specific steps scattered throughout the solution, you reduce redundancy and centralize control.

Transactional Editing with Save and Cancel Functionality

You can create a Save/Cancel functionality using a custom button bar that triggers different scripts. This provides a clear choice for users between saving and discarding changes. The Save/Cancel dialog works in harmony with FileMaker’s OnRecordCommit trigger, creating a smooth transactional workflow.

Configuring the Save/Cancel Add-on

To simplify the transactional editing process, Stathis introduced the Save/Cancel Add-on, a drag-and-drop module for FileMaker.

Installing and Using the Add-on

1. Download the Save/Cancel Add-on from the resources provided.
2. Place the add-on in the appropriate folder:
   • Mac: ~/Library/Application Support/FileMaker/Extensions/Add-On Modules
   • Windows: C:\Users\[YourUsername]\AppData\Local\FileMaker\Extensions\Add-On Modules
3. Restart FileMaker, navigate to your solution, and open Layout Mode.
4. Click on the Add-ons tab, then click the + button to add the Save/Cancel functionality to your layout.

Step-by-Step Configuration

1. Drag the Save/Cancel module onto your layout.
2. Link the custom scripts to the buttons by opening the Script Workspace.
3. Configure Script Triggers like OnRecordCommit to use the custom scripts, ensuring transactional integrity.

Introduction to FileMaker Audit Logs

Audit logs are essential for maintaining a detailed, chronological record of all critical data operations. They help track who did what and when, enhancing security and accountability.

What is an Audit Log?

An audit log is a security-relevant, chronological record of events and changes within your FileMaker solution. Events can range from record creation, modification, and deletion to schema changes and access level alterations.

Determining What to Audit

Not all events are worth tracking. Consider the following criteria:

• Data Sensitivity: Track changes to sensitive or confidential data.
• Business Impact: Log operations that significantly affect business processes.
• Compliance Requirements: Log events that are required for audits or industry compliance.

Stakeholders and Audit Log Usage

Different stakeholders benefit from an audit log:

• IT Professionals: For tracking performance and data integrity.
• Managers: To monitor user access and data changes.
• Auditors: Ensuring compliance with internal and external standards.

Creating a Native Audit Log in FileMaker

Key Pieces of the Audit Log Schema

To build a comprehensive audit log, focus on the following data:

• Access Info: Account name, privilege set.
• Event Info: Script name, timestamp, record ID.
• Data Info: Table, layout, and field changes.
• Record State: Tracks whether the record is new, modified, or deleted.

Implementing Audit Log Tracking

Use the following techniques to implement an audit log in FileMaker:

1. Create an Audit Log Table to store events.
2. Use custom functions and scripts to capture record changes.
3. Use the Get(ModifiedFields) function to identify and log changes.
4. Implement JSON formatting for storing complex data.

Handling Large Data Capacities

For larger deployments with multiple users, keep the audit log payload minimal to ensure performance. Use the following strategies:

• Minimal Payload: Log only modified fields.
• JSON Storage: Use JSON to store structured data in the audit log.
• Compression: Use custom scripts to compress audit log data if needed.

Developing Record Rollback Functionality

The audit log allows for rollback, a powerful feature that lets you restore records to a previous state.

Challenges of Rollback Implementation

Rollback requires access to the record’s history, which can become complex if you only log modified fields. A solution is to rebuild the record state by applying all logged modifications sequentially.

Rebuilding Record States

1. Initial Record: Start from the record’s creation state.
2. Modification History: Apply changes one by one until reaching the desired rollback point.
3. Final State: Reconstruct the record to the selected state.

Step-by-Step Guide to Rollback

1. Log the initial record state on creation.
2. Track field modifications using Get(ModifiedFields) for each change.
3. Use a script to reconstruct the record by sequentially applying changes.
4. Allow users to preview changes before committing a rollback.

Deploying the Audit Log Solution

Understanding the Audit Log Schema

The schema should be lightweight yet comprehensive. Fields should include:

• Event ID: Unique identifier for each logged event.
• Timestamp: Date and time of the event in UTC.
• User Information: Account name and privilege set.
• Table and Layout: Context of the change.
• Modified Fields: JSON-formatted data indicating what changed.

Practical Demo: Record Tracking and Rollback

1. Create a New Record: Log the event and store initial data.
2. Modify the Record: Track changes and update the audit log.
3. Delete the Record: Mark it as deleted in the audit log.
4. Rollback: Use the stored data to restore to a previous state.

Advanced Use Cases

• Related Records: Track changes in related tables.
• User Actions: Log user interactions like exports or imports.
• Automated Tasks: Use the audit log to trigger notifications or automated tasks.

Summary and Key Takeaways

In this extended guide, we explored:

• The importance of transactional edits for data integrity.
• How to create a Save/Cancel mechanism in FileMaker for safe data management.
• Building a configurable audit log to track critical operations.
• Implementing rollback functionality to restore data states seamlessly.

A well-structured audit log not only provides accountability but also enhances the trust users have in your system by maintaining a high standard of data integrity.

Resources

• FileMaker Transactions by Todd Geist
• Deep Dive Scripting and Audit Log by Wim Decorte
• Claris Community
• Files Download