BlogFileMaker

Document Field IDs: Compare Field IDs Between Deployment Environments

By September 20, 2018 September 10th, 2019 No Comments

Gemini to Apollo

Just like trailblazers such as Gus Grissom, working on the Mercury, and later, Gemini projects for NASA, we sometimes require separate or parallel environments in project deployments. Gemini could be viewed as the “Dev” phase that led up to the “Prod” project that followed, Apollo. Development environments are a critical part of projects where experiments and testing can be done without risk to critical resources, allowing bugs to be found and worked out.

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.

stronauts John Young and Virgil I. (Gus) Grissom are pictured during water egress training in a large indoor pool at Ellington Air Force Base, Texas. Young is seated on top of the Gemini capsule while Grissom is in the water with a life raft.

Astronauts John Young and Virgil I. (Gus) Grissom are pictured during water egress training in a large indoor pool at Ellington Air Force Base, Texas. SOURCE: NASA Images at the Internet Archive

Expand image
There are strategies to consider when working in such an environment to ensure successfully implementing updates without issue.
In even larger systems there could be more environs for different purposes, such as development (DEV), quality assurance (QA), and production (PROD). These can occur in sequence or, more commonly, in parallel.

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.

Deke Slayton shows the adapter devised to make use of square Command Module lithium hydroxide canisters to remove excess carbon dioxide from the Apollo 13 LM cabin.

Deke Slayton shows the adapter devised to make use of square Command Module lithium hydroxide canisters to remove excess carbon dioxide from the Apollo 13 LM cabin. SOURCE: NASA Apollo Imagery

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.

The Problem

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
1001 ID
1002 FirstName
1004 LastName

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
1001 ID
1002 FirstName
1003 LastName

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

Astronaut Roger B. Chaffee is shown at console in the Mission Control Center, Houston, Texas during the Gemini-Titan 3 flight.

Astronaut Roger B. Chaffee is shown at console in the Mission Control Center, Houston, Texas during the Gemini-Titan 3 flight. SOURCE: NASA Gemini SHuttle Mission Gallery

Expand image
I built a small FileMaker file to automate the process of getting a list of all Tables and their Fields and internal Field IDs and some scripting to show where there are conflicts. You can get the file free from here: https://github.com/SoliantMike/FM-DocumentFieldIDs

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.

Now What?

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.

References

If you have any questions or need help with your FileMaker solution, please contact our team.

Mike Duncan

Mike Duncan

Mike is an AWS Certified Solutions Architect as well as a certified FileMaker Developer. In addition to his work, Mike also enjoys pursuing his art, freelance writing, traveling, and spending time with his family.

Leave a Reply