Accessing Base Tables in Claris FileMaker 2023

With the release of the Claris FileMaker 2023 platform this month it is now even easier to retrieve a list of Base Tables in your FileMaker solution. Claris FileMaker Pro 2023 – also referred to as FileMaker Pro v20.1 – includes a number of new functions that can be used to replace previous methods that used the ExecuteSQL function to query the system tables.

FileMaker Pro 20 introduced the following new functions:

• GetBaseTableName returns the base table name of a provided field. You can find this function in the Miscellaneous group of functions.
• BaseTableNames and BaseTableIDs return information about the base tables instead of all table occurrences. You can find these functions in the Design group of functions.

With the BaseTableNames function you can quickly generate a list of all base table names in specified file. For example:

BaseTableNames ( "" )

returns a list of base tables in the current file. 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 important point to note is that the BaseTableNames function returns the same list regardless of the table occurrences on the relationship graph. If you delete every table occurrence on the graph:

you will still get a list of all the tables that appear on the Tables tab of the Manage Database dialog box:

This is a big improvement over previous methods that used the ExecuteSQL function to query the system tables which relied on the table occurrences on the relationship graph for the results. Furthermore if you used the new query syntax introduced with FileMaker Pro 19.4.1:

ExecuteSQL ( "SELECT DISTINCT BaseTableName FROM FileMaker_BaseTableFields" ; "" ; "" )

any tables that had no defined fields would not be included in the results. The BaseTableNames function returns all the base table names regardless of the relationship graph or number of fields defined for each table, a big improvement over the the ExecuteSQL query method.

Claris have also made some changes to the ExecuteSQL query options in FileMaker Pro v20.1 as well – using the ExecuteSQL function you can now access a FileMaker_BaseTables system table. The new schema contains the following fields:

• BaseTableName – the table name
• BaseTableId – the table ID
• Source – the source (<internal>, MYSQL, etc.)
• ModCount – the number of times the table has been modified

For example the following query:

ExecuteSQL ( "SELECT * FROM FileMaker_BaseTables" ; "" ; "" )

returns the following:

Accounts,143,<Internal>,51
BulkSessions,132,<Internal>,24
Contacts,130,<Internal>,32
CountryCodes,133,<Internal>,3
DeliveryReceipts,134,<Internal>,22
GatewayCodes,144,<Internal>,8
Gateways,135,<Internal>,71
Interface,136,<Internal>,16
MergeFields,137,<Internal>,3
Messages,141,<Internal>,52
Navigation,145,<Internal>,10
Replies,139,<Internal>,28
Senders,140,<Internal>,8
ServerSideErrors,146,<Internal>,6
Templates,142,<Internal>,8
Webhooks,147,<Internal>,11

If you’re just after the Base Table Name you can use the following query:

ExecuteSQL ( "SELECT  BaseTableName FROM FileMaker_BaseTables" ; "" ; "" )

which returns the same results as the BaseTableNames function.

You can get more information on querying the FileMaker System Tables in earlier versions of FileMaker Pro in our earlier posts on this topic:

• Using ExecuteSQL to Query the Virtual Schema/System Tables
• Changes to Querying the FileMaker System Tables in FileMaker Pro 19.4.1