JSON Design Pattern: Enhancing Record Creation in FileMaker

By Matt Navarre

For the next few months, I'll be posting a Design Pattern each week on the topics of AI, JSON, and other best practice / key areas of FileMaker. Why? Because I love this platform and sharing easier and better ways to do things.

Fields are precious. The fewer in a table the better. The longer you work with FileMaker the more you will see the reasons and benefits of narrow tables.

There is a practical JSON design pattern that I’ve been using lately in the FileMaker classes I teach. This approach allows you to store comprehensive metadata when a record is created (or any time), giving you much more information but with no extra fields. In fact, the field count will go down. Here is the YouTube video with the demo.

The Challenge: Limited Default Metadata

When you create a new table in FileMaker, it automatically includes fields like ID, CreationTimestamp, AccountName, and ModificationTimestamp. While useful, these fields often don’t provide enough context for more complex applications.

The Solution: Storing Metadata in a JSON Field

To capture more detailed information, I add a single JSON field to my table. This field stores various metadata elements upon record creation, such as:

• UTC time

• Server time

• Local date and time

• Script name (if a script was running)

• Account name and username

• Privilege set name

• Record ID and record number

Implementing the Pattern

Here’s how I set it up:

1. Add a JSON Field: Create a field named JSON in your table.

2. Create a Script: Develop a script that runs upon record creation. This script should gather all the desired metadata and store it in the JSON field.

3. Use JSONGetElement: To display specific metadata on layouts, use the JSONGetElement function to extract the needed information.

Benefits of This Approach

• Flexibility: Easily add or remove metadata elements without altering the database schema.

• Clarity: Provides a clear and structured way to store and access metadata.

• Maintainability: Simplifies future updates and debugging processes.

This JSON design pattern has been a game-changer in my FileMaker development, offering a scalable and efficient way to handle metadata. I hope you find it as useful as I have.

Feel free to reach out if you have questions or want to share how you’ve implemented this pattern in your projects.