Change Tracking Strategies

Change tracking is essential for determining what data needs to be synchronized between systems, providing the foundation for efficient integration that only processes modified data.

Why Track Changes?

Tracking changes enables:

• Efficient synchronization: Only transfer modified data, not everything
• Conflict detection: Identify concurrent modifications to the same data
• Audit trails: Maintain a history of what changed, when, and by whom
• Recovery capabilities: Ability to roll back to previous states
• Debugging: Easier troubleshooting of integration issues

Change Detection Methods

Timestamp-Based Detection

Using modification timestamps to identify changes:

• Record last sync time before starting sync
• Find records modified since last sync using timestamp field
• Process only the modified records found
• Update the last sync timestamp when completed

Version Number Detection

Incrementing counters to track changes:

• Find records with version number higher than last synced
• Process those records for synchronization
• Update last synced version to the highest version seen

Change Logs

Explicitly logging all data modifications:

• Create a change logging system with triggers
• Capture whether it's a new record or update
• Store record ID, table name, change type, timestamp
• Store old and new values for comparison
• Track sync status for each change

Checksum/Hash Detection

Using data hashes to detect any changes:

• Calculate hash of important fields
• Compare with previously stored hash
• Update hash and flag for sync if different
• Store modification timestamp

Change Data Capture (CDC)

Using database transaction logs to capture changes:

• External CDC tools monitor database logs
• Changes are captured in real-time, even outside of application logic
• Captured changes are sent to integration processes
• More common in enterprise database systems

Record-Level Change Tracking

Record-level tracking is the most straightforward and efficient approach for most integration scenarios:

• How it works: Flag entire records as modified without tracking specific field changes
• Implementation: Add a modified flag and timestamp to each record
• Advantages: Simple to implement, minimal storage requirements, clear logic
• Best for: Most integration scenarios where knowing which record changed is sufficient
• Limitations: Doesn't specify what changed within the record

FileMaker Change Tracking Implementation

AutoEnter Calculations

One of the most straightforward ways to implement change tracking in FileMaker is through AutoEnter calculations. This calculation will flip to one when the record is modified, except when a script is running that $IsUpdatingThisRecord is true. Then it will flip to zero.

Let(
[
  x = GetField("")
];
  Case(
   $IsUpdatingThisRecord ; 0;
   1
  )
)

Script Triggers

For effective change tracking in FileMaker, two key script triggers are particularly useful:

1. OnRecordCommit: This trigger executes a script when a record is committed, making it ideal for detecting and logging changes to individual records. You can capture the original record state and compare it with the new state to determine what changed.

2. OnWindowTransaction: Introduced in FileMaker 20.1, this trigger performs a script after a transaction is successfully committed. It creates a JSON object containing information about all operations within the completed transaction, including file name, base table name, record ID, and operation details. This makes it especially powerful for comprehensive change tracking across multiple records in a single transaction Claris FileMaker Pro Help.

Integration with External Systems

Polling for Changes

External systems poll FileMaker for changes:

• Accept a "last polled" timestamp parameter
• Find changes since that timestamp
• Return changes as JSON data
• Include current timestamp for next polling cycle

Push Notifications

FileMaker pushes changes to external systems:

• Format changes as JSON
• Send to webhook endpoints
• Use authentication tokens for security
• Handle response to confirm delivery

Common Challenges

High-Volume Changes

When dealing with many changes:

• Batch processing to improve performance
• Prioritize critical changes
• Use compression for change data
• Implement change collapsing (combine multiple changes to same record)

Bi-directional Sync

When changes flow in both directions:

• Carefully track change origin to avoid loops
• Implement "last writer wins" or more sophisticated conflict resolution
• Use vector clocks or version vectors for distributed ordering
• Consider synchronization locking for critical periods

Deleted Records

Tracking deleted records:

• Implement soft deletes (status flag instead of actual deletion)
• Create delete tombstones (special records noting a deletion)
• Use "purged" status for truly deleted records
• Set retention policies for deletion records

Best Practices

1. Automate change tracking: Don't rely on manual flags or user actions
2. Track metadata: Include who, when, why with change data
3. Use efficient storage: Don't store unchanged data
4. Implement retention policies: Don't keep change history forever
5. Document change semantics: Define what constitutes a "change"
6. Test edge cases: Verify tracking during unusual scenarios
7. Monitor performance impact: Change tracking adds overhead
8. Backup change logs: Protect this critical integration data