Changes to Querying the FileMaker System Tables in FileMaker Pro 19.4.1

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.
1 reply

Trackbacks & Pingbacks

  1. […] Update for Claris FileMaker Pro 19.4.1 – a new faster query to return base tables is now available in Claris FileMaker Pro 19.4.1 or later. Details can be found in this article. […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *