Changes to Querying the FileMaker System Tables in FileMaker Pro 19.4.1
Update for Claris FileMaker Pro 20 (2023) – FileMaker Pro v20 includes new functions that return information about the base tables instead of all table occurrences making it much easier get a list of base tables. Details can be found in this article.
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:
Accounts
BulkSessions
Contacts
CountryCodes
DeliveryReceipts
GatewayCodes
Gateways
Interface
MergeFields
Messages
Navigation
Replies
Senders
ServerSideErrors
Templates
Webhooks
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.
Thank you so much for the update! I’ve used your previous article many many times and I’ll be using this new table much more often to speed all the queries up.
These two queries do not actually produce the same result in a file I just tested
The new version misses out the table first created (which I use on the graph as a marker, but which has no fields and no record) where it is included with the original query
Hi John – that’s an interesting discovery I wasn’t aware of! I just tested this and it appears that you need to have at least 1 field defined in the table for the table to appear in the result from the new query with 19.4.1 or later. I’m not sure if this is intentional or a bug: getting the same results with 19.4.2 and 19.5.1. I’ll report this to Claris and see what they come back with.
Hi John,
Claris have replied to my report about this issue which you can read here. Essentially it appears it’s “working as expected” here. I guess the clue is in the name here: FileMaker_BaseTableFields.
regards,
Andrew
For anyone wondering, neither FileMaker_Tables, nor FileMaker_BaseTableFields, will provide any information about a table which is not represented by a Table Occurrence in the Relationship Graph.
Correction:
ModCount – The total number of times changes to this field definition have been committed