The Five Default Fields: A Closer Look

Watch the video on YouTube

Five default fields are automatically created when you make a new table. These fields represent best practices, especially the UUID, which ensures a unique key for every record in every table in every database in the world (theoretically).

However, the settings on these fields can lead to problems. View it through the lens of your users – who are not experts in obscure error messages.

Importing Data and “Not empty” Validation

The “Not empty” field validation setting can cause significant issues when importing data. If users uncheck the auto-enter options during import, which is a common ... let's say 'mistake' – the five default fields will be blank. This results in errors when attempting to modify a record, leading to a confusing experience where users may not know how to proceed. Most users might simply force-quit the application, frustrated by their inability to resolve the issue. While reverting the record is possible, it’s not an obvious, and the key problem remains: The user can't edit the imported record.

The evil checkboxes - Not empty and Unique value

The forgotton checkbox: Perform the auto-enter options...

The resulting vexing error message

The solution used that you really really don't want users to do.

While the “Unique” checkbox ensures that each value in the table is unique, it also defaults the field to be indexed. This is unnecessary for tables that don’t have child records, and it can slow down record creation as the database grows (but not until record counts are high). I say - let the index be created when it needs to be.

Unicode

Another very interesting issue arises because the default language setting for the primary key is set to Unicode. This means the field is case-sensitive, which can break relationships if there’s a mismatch between the case of characters in the UUID and the corresponding foreign key. Ready for something super weird? It will cause the relationship to break, but only in one direction if the foreign key is not Unicode. Since fields default to your local language, you have to set Unicode manuallly. This was fun to discover and it’s a pretty easy fix. I think maybe setting all primary and foreign keys to either Unicode or the local language will solve this.

Creation and Modification Fields

The creation and modification fields are a good start but I generally use a log to capture every edit, not just the most recent. These four fields will present the same error message as the primary key if there is no value, so it's their potential to do 'harm' that I want to prevent.

One issue I've seen happens when these fields are added via copy-and-paste to a table with pre-existing records. Since the old records won’t have any data in these fields, the whole record become uneditable. Kids – Leave validation boxes unchecked.

Note that these creation and modification timestamp capture the client’s time, not the server’s, which might not align with the requirements of some applications. There are workarounds, such as using calculations to capture the server or UTC time, as demonstrated in tutorials by experts like Matt Petrowsky.

Solutions

To avoid these pitfalls, one approach is to create your own set of default fields and paste them into new tables manually, deleting the automatically generated ones. This ensures that the fields are set up exactly as needed from the start.

It's not a new thing at all, but another solution is to modify the FMDefaultFields.xml file to customize these default fields globally. This method allows for the adjustment of settings, names, and other aspects to better suit your specific needs. Resources from AppWorks, Proof+Geist, and videos by Matt Petrowsky provide excellent guidance on how to safely modify this file and even add other default fields that offer additional advantages.

Thoughts

I’m not advocating for skipping validation altogether. I believe handling it at the script level rather than at the data level is better. This gives you —the developer— full control over record creation and validation all in one place. It's easier to write, debug and manage.

Going back to where I started, I like to avoid creating confusing dialog boxes that users can’t easily navigate. Users should love your FileMaker apps.