Gemini to Apollo
Similarly, in FileMaker development, it is not uncommon to have different environments dedicated for development and production. Active development occurs on a dedicated server, or offline, where changes are tested before being put into production.
Parallel Development Environments
During Apollo 13, as with all NASA missions, there was a primary crew and a backup crew. When the accident with the oxygen tanks occurred, Ken Mattingly and a team of engineers were required to come up with a solution on the ground. Essentially, this was a Dev environment, taken to extremes, with the Prod environment being the Apollo crew in actual flight.
Similarly, you can have different environments for your FileMaker project, albeit without the extreme conditions or consequences. Dev environments are sometimes required to work out and test various solutions before eventually being put into production with confidence.
When you create a field in FileMaker, there are things that happen under the hood to make it easy to reference throughout the solution. An internal "Field ID" is assigned to each field that you create. You never see this internal ID, but this is how FileMaker knows what field to reference in layouts and scripts.
You might think that fields with the same name would map correctly across different systems, but it is the internal field ID that is used. Therefore, it is critical when deploying any updates, that internal field IDs match.
For example, there can even be multiple developers working on a solution, and if different people are adding fields in parallel environments without consideration of the order they are added in, then the internal field IDs are bound to get out of place. The next time you deploy an update, things can break if fields are not lined up.
Environment 1 | Developer A | Table 1
|Internal Field ID||Field Name|
Developer A created a field, “MiddleName”, which was assigned internal ID 1003. Later in development, the field was deleted leaving a gap in the internal ID numbering sequence.
Environment 2 | Developer B | Table 2
|Internal Field ID||Field Name|
Developer B never created the “MiddleName” field so internal ID 1003 exists in this environment but is associated to the “LastName” field.
Internal Field IDs
There are some internal tables that FileMaker uses to track schema, which is not visible anywhere in a file, except by way of the executeSQL function. By querying these "under the hood" internal tables, we can get information about the defined FileMaker fields, including their internal IDs.
The Solution: Pre-Flight Check
All you need to do to use with your files is to follow the instructions, update the external data sources and reference the one script you copy from this file and paste into the files you want to be able to compare. What happens then is that the necessary executeSQL function is run and returns results to the parent script as a parameter, where we parse through and build results as temporary records in our file. Special thanks to our own Brian Engert for helping optimize the SQL to only return fields for each base table, instead of each table occurrence in a file.
Once you find an issue where fields do not match, what do you do? That is going to depend on what has been done, and where those fields appear in your development. This tool merely identifies where issues are at, what you do about it is something different. Ideally, you identify problems before they become an issue and can correct fields in both environments before they become an issue.
You might also consider implementing the FileMaker Data Migration Tool for options regarding matching on field name during migration, to get environments back to a baseline file state. Read more about the Data Migration Tool.
Also, thank you to Norm Bartlett for reviewing and contributing to this post.
- Gihub Repository: https://github.com/SoliantMike/FM-DocumentFieldIDs
- Gemini 3: https://en.wikipedia.org/wiki/Gemini_3
- Apollo 13: https://en.wikipedia.org/wiki/Apollo_13