Back in July 2012 I wrote an article about a new feature in the FileMaker Pro 12 Platform – the ability to query the hidden System Tables for information about the schema of your FileMaker Pro files using the new ExecuteSQL function. That article – Using ExecuteSQL to Query the Virtual Schema/System Tables – has become one of the most popular articles on our site and continues to be one of the most visited articles each month.
Every FileMaker Pro database file includes two system tables: FileMaker_Tables and FileMaker_Fields. Using the ExecuteSQL function to query these tables has a number of practical applications, including:
- return a list of all unique underlying Base Tables for all Table Occurrences in the current FileMaker Pro file (see this custom function)
- return a sorted list of all Field Names for specified Table Occurrence in the current FileMaker Pro file (see this custom function)
You can get further details on the results of these queries in the previous article and also the Claris FileMaker SQL Reference. There hasn’t been any changes to this functionality since FileMaker Pro v12 was released until this week with the release of Claris FileMaker Pro 19.4.1. The FileMaker Pro 19.4.1 Release Notes include mention of a change to allow you to perform faster SQL queries about fields defined in a FileMaker Pro file by using the new system table named FileMaker_BaseTableFields.
Previously to get a list of base table names you would use the following query:
ExecuteSQL ( "SELECT DISTINCT BaseTableName FROM FileMaker_Tables" ; "" ; "" )
This would retrieve the value from the BaseTableName column from the response, which includes all table occurrences in the current file. You can now speed this up by using the following query:
ExecuteSQL ( "SELECT DISTINCT BaseTableName FROM FileMaker_BaseTableFields" ; "" ; "" )
which returns the same response but is a faster query as FileMaker_BaseTableFields includes only the source (or base) tables. An example of the result when using our fmSMS solution is a simple list of all the base tables for the file:
The FileMaker_BaseTableFields table includes the following columns:
- BaseTableName – The name of the base table that contains the field.
- FieldName – The name of the field.
- FieldType – The SQL data type of the field.
- FieldId – The unique ID for the field.
- FieldClass – One of three values: Summary, for summary fields; Calculated, for calculated results; or Normal.
- FieldReps – The number of repetitions of the field.
- ModCount – The total number of times changes to this base table’s definition have been committed.