More about tables and fields

• Lookup

FileMaker Pro provides “Look-ups” to copy data from one table to another related table. Lookups create a static copy of data in a target field, which doesn’t automatically update if the source data changes. This is useful for historical records but can lead to data redundancy.

• Portal

I assume it’s safe to think of portals as dynamic lookups. In a nutshell, a portal pulls in related data dynamically and allows limited interaction with that data in the context of the parent record.

Accounts, privilege sets, and extended privileges

• Accounts and privilege sets

FileMaker offers a built-in, role-based access control system for managing user roles and privileges.

Each database file contains two predefined accounts: admin and guest; three predefined privilege sets for access levels: full access, data entry only, and read-only. Extended privileges determine the data sharing options that are permitted by a privilege set for a file.

• Access control

It offers a record-level access control. Within a privilege set, you can define conditions to limit access to specific records based on field values. For instance, you might allow a user to view only the records they created, or records with a specific status.

FileMaker also supports calculation-based access. For example, you can restrict editing of a record if its “Status” field equals “Closed”. This is somewhat similar to row-level security in SQL but limited to simpler conditions.

Extended privileges allow you to define whether a user can access the database through FileMaker WebDirect, FileMaker Go (mobile), or via FileMaker’s API. You can also use extended privileges to control whether a user can share their FileMaker session over the network or access the database through a URL.

• Limitations

Accordingly, FileMaker lacks the hierarchical roles that many enterprise systems have, where one role can inherit permissions from another. Privilege sets are standalone, so creating nuanced, layered roles (like a “Manager” with some “Admin” permissions) can become complex and require duplication of privilege definitions.

Its handling of access control also falls short in complex multi-conditions policies. Such as, “Can edit only if user is the owner and the record status is “Open”.

On top of that, FileMaker doesn’t have detailed access logging or auditing features built-in.

In layman’s terms, FileMaker provides sufficient access control for straightforward applications but lacks the depth, flexibility, and security features required for high-security, high-compliance environments.

This raises questions about FileMaker's capabilities and limitations regarding concurrency, versioning, and transaction handling.

Concurrency

What FileMaker can handle:

• Record-level locking: Each record is locked when a user starts editing it. Other users can still view the locked record, but only one user can edit it at a time.

• Automatic lock release: When a user is inactive or disconnected, the lock on that record gets released after a specified timeout.

• Multiple users: FileMaker Server supports up to around 100-250 concurrent users, which is adequate for moderate data access needs.

What it struggles with:

In addition to high-concurrency limitations, FileMaker does not support optimistic concurrency. Its handling is established through a simpler pessimistic locking model. As a result, it might cause delay if multiple users frequently need access to the same records.

Versioning

Cans:

• Manual version tracking: e.g., creating custom fields like last modified date.

• Basic version control through scripts.

• Basic external version control: Git.

Can’ts:

No native support for automatic versioning, change tracking, or schema changes versioning.

Transaction handling

Cans:

• Single-record transaction: as mentioned above regarding record-level locking, each record is individually committed or reverted. This inherently prevents classic dirty reads because no other users can access the uncommitted data.

• Commit and revert model mimics basic transaction-like be behavior on a single-record level.

• Multiple-step operation is not impossible with scripts. The way I phase this strongly suggests that, while possible, I hope I won’t be the one to implement such scripts.

Can’ts:

• No rollback built-in.

• No multi-record built-in.

• No isolation levels. Hence it’s possible to encounter phantom reads. Although, due to the lack of complex, multi-statement transactions, this issue is less noticeable.

The fact that FileMaker minimizes dirty reads issue doesn’t mean there’s no similar behaviors.

For instance, picture a scenario where User A is updating related records, and User B retrieves that data at different points, User B could see an inconsistent view of the related data if the records aren’t committed at once.

Another thing that’s worth mentioning is that, while you can make a FileMaker backup to preserve the entire database structure, layouts, relationships and settings, you’re locked-in. Since your backups are save in a format that most relational databases cannot read directly.

Its schema structure doesn’t directly map to SQL databases, so recreating the schema requires careful planning.

In short, FileMaker is by no means enterprise-level in terms of concurrency, version control and transaction management.