Accessing Base Tables in Claris FileMaker 2023

/3 Comments/in , , /by

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:

Changes to Querying the FileMaker System Tables in FileMaker Pro 19.4.1

/8 Comments/in , , /by

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.

Using ExecuteSQL to Query the Virtual Schema/System Tables

/12 Comments/in , , /by

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.

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.

FileMaker Pro v12 introduced the new ExecuteSQL function, which allows you to perform an SQL query on your FileMaker database. The query can can combine the results of two queries, include dynamic parameters, and lets you specify relationships independent of the relationships graph. It is presently limited to the SELECT statement, which is similar to doing a find using the native FileMaker Pro commands, but is not tied to a particular layout or relationship.

I’m going to discuss one particular use of the ExecuteSQL function in this post: the ability to query 2 “virtual tables” for information about the schema of your FileMaker Pro v12 files. The ability to execute SQL statements has been available via the external plug-in API since FileMaker Pro v7.0v3 was released, and many plug-ins have included this functionality, including MMQuery and the BaseElements plugins. Plug-ins that utilised the SQL functionality have also had the ability to query 2 hidden “virtual tables” for information about the schema of your FileMaker database, including Table Occurrences and Field Names. Now that we have the ability to perform native SQL queries using the ExecuteSQL function in FileMaker Pro v12 we can also query these “virtual tables”.

The 2 “virtual tables” that you can query are FileMaker_Tables and FileMaker_Fields.

FileMaker_Tables returns the following columns:

1. TableName
2. TableID
3. BaseTableName
4. BaseFileName
5. ModCount

Here’s some ExecuteSQL function examples that illustrate how you can query FileMaker_Tables (note that the table occurrences also work with external file references that appear on the graph of the current file. I’m using the Event Management starter solution from FileMaker Pro v12 to illustrate what data is returned as well):

ExecuteSQL ( “SELECT * FROM FileMaker_Tables” ; “” ; “”  )
// Returns TableName, TableID, BaseTableName, BaseFileName, and ModCount  for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda,1065093,Agenda,Event Management,46
Assignees | Contributors,1065094,Contributors,Event Management,115
Contacts,1065098,Contacts,Event Management,140
Contributors,1065097,Contributors,Event Management,115
Events,1065089,Events,Event Management,137
Guests,1065092,Guests,Event Management,62
Guests | Contacts,1065095,Contacts,Event Management,140
Tasks,1065091,Tasks,Event Management,89
Venue Looked Up,1065107,Events,Event Management,137

ExecuteSQL ( “SELECT BaseTableName FROM FileMaker_Tables” ; “” ; “”  )
// Returns list of all underlying Base Tables for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda
Contributors
Contacts
Contributors
Events
Guests
Contacts
Tasks
Events

ExecuteSQL ( “SELECT DISTINCT BaseTableName FROM FileMaker_Tables” ; “” ; “”  )
// Returns a unique list of all underlying Base Tables for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda
Contacts
Contributors
Events
Guests
Tasks

FileMaker_Fields returns the following columns:

1. TableName
2. FieldName
3. FieldType (the SQL data type, not the FileMaker data type)
4. FieldID
5. FieldClass (Normal, Summary, Calculated)
6. FieldReps
7. ModCount

Here’s some ExecuteSQL function examples that illustrate how you can query FileMaker_Fields (once again using the Event Management starter solution from FileMaker Pro v12 to illustrate what data is returned):

ExecuteSQL ( “SELECT * FROM FileMaker_Fields” ; “” ; “”  )
// Returns TableName, FieldName, FieldType, FieldID, FieldClass, FieldReps and ModCount  for all Table Occurrences in the current FileMaker Pro v12 file

returns:

Agenda,EVENT ID MATCH FIELD,decimal,2,Normal,1,4
Agenda,Agenda Item,varchar,3,Normal,1,2
Agenda,Date,date,4,Normal,1,9
Agenda,Explanation,varchar,5,Normal,1,5
Agenda,Time,time,6,Normal,1,7
Agenda,Time Frame Order,decimal,8,Calculated,1,16
Agenda,Time Frame,varchar,9,Calculated,1,12
Assignees | Contributors,EVENT ID MATCH FIELD,decimal,1,Normal,1,5
Assignees | Contributors,Email,varchar,2,Normal,1,19
Assignees | Contributors,Phone,varchar,3,Normal,1,10
Assignees | Contributors,Contributor | MATCH FIELD,varchar,4,Normal,1,26
Assignees | Contributors,Role,varchar,5,Normal,1,1
Assignees | Contributors,Assignment,varchar,13,Calculated,1,19
Assignees | Contributors,Photo | Container,binary,14,Normal,1,5
Assignees | Contributors,Assignment Status,decimal,16,Calculated,1,3
Assignees | Contributors,Photo Placeholder,varchar,18,Calculated,1,3
Contacts,First,varchar,2,Normal,1,2
Contacts,Last,varchar,3,Normal,1,2
Contacts,Contacts | MATCH FIELD,varchar,4,Calculated,1,26
Contacts,Job Title,varchar,5,Normal,1,2
Contacts,Company,varchar,6,Normal,1,5
Contacts,Work Email,varchar,7,Normal,1,4
Contacts,Work Phone,varchar,8,Normal,1,2
Contacts,Mobile Phone,varchar,9,Normal,1,2
Contacts,Fax,varchar,10,Normal,1,1
Contacts,Website,varchar,11,Normal,1,1
Contacts,Address 1,varchar,12,Normal,1,3
Contacts,City,varchar,13,Normal,1,3
Contacts,State,varchar,14,Normal,1,3
Contacts,Postal Code,varchar,15,Normal,1,3
Contacts,Country,varchar,16,Normal,1,3
Contacts,Address 2,varchar,17,Normal,1,4
Contacts,CONTACT ID MATCH FIELD,decimal,33,Normal,1,7
Contacts,Initial,varchar,34,Calculated,1,32
Contacts,Sort Selection,global varchar,39,Normal,1,4
Contacts,Sort List Key,varchar,40,Calculated,1,4
Contacts,Photo | Container,binary,41,Normal,1,4
Contacts,Title,varchar,42,Normal,1,1
Contacts,Home Phone,varchar,43,Normal,1,1
Contacts,Home Email,varchar,44,Normal,1,5
Contacts,Notes,varchar,46,Normal,1,2
Contacts,Address Short,varchar,48,Calculated,1,8
Contacts,File Name Placeholder,varchar,49,Calculated,1,4
Contacts,Result Label Plural,varchar,50,Calculated,1,2
Contributors,EVENT ID MATCH FIELD,decimal,1,Normal,1,5
Contributors,Email,varchar,2,Normal,1,19
Contributors,Phone,varchar,3,Normal,1,10
Contributors,Contributor | MATCH FIELD,varchar,4,Normal,1,26
Contributors,Role,varchar,5,Normal,1,1
Contributors,Assignment,varchar,13,Calculated,1,19
Contributors,Photo | Container,binary,14,Normal,1,5
Contributors,Assignment Status,decimal,16,Calculated,1,3
Contributors,Photo Placeholder,varchar,18,Calculated,1,3
Events,EVENT ID MATCH FIELD,decimal,1,Normal,1,11
Events,Event,varchar,2,Normal,1,10
Events,Date,date,3,Normal,1,2
etc . . .

ExecuteSQL ( “SELECT * FROM FileMaker_Fields WHERE TableName=’Tasks'” ; “” ; “”  )
// Returns TableName, FieldName, FieldType, FieldID, FieldClass, FieldReps and ModCount  for the “Tasks” table occurrence in the current FileMaker Pro v12 file

returns:

Tasks,EVENT ID MATCH FIELD,decimal,2,Normal,1,5
Tasks,Due Date,date,5,Normal,1,3
Tasks,Task,varchar,6,Normal,1,11
Tasks,Status,decimal,7,Calculated,1,21
Tasks,Assignee | MATCH FIELD,varchar,11,Normal,1,4
Tasks,Status Sort Order,decimal,12,Calculated,1,32
Tasks,Completed,varchar,14,Normal,1,2
Tasks,To Do,decimal,17,Calculated,1,5
Tasks,Overdue,decimal,18,Calculated,1,6
Tasks,Event,varchar,20,Calculated,1,1

ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName=’Tasks'” ; “” ; “”  )
// Returns a list of FieldNames   for the “Tasks” table occurrence in the current FileMaker Pro v12 file.The list of field names is in field creation order

returns:

EVENT ID MATCH FIELD
Due Date
Task
Status
Assignee | MATCH FIELD
Status Sort Order
Completed
To Do
Overdue
Event

ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName=’Tasks’ ORDER BY FieldName” ; “” ; “”  )
// Returns a sorted list of FieldNames for the “Tasks” table occurrence in the current FileMaker Pro v12 file

returns:

Assignee | MATCH FIELD
Completed
Due Date
EVENT ID MATCH FIELD
Event
Overdue
Status
Status Sort Order
Task
To Do

ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName='” & Get ( LayoutTableName ) & “‘ ORDER BY FieldName” ; “” ; “”  )
// Returns a sorted list of FieldNames for the table occurrence of the current layout in the current FileMaker Pro v12 file. I’m on the “Events” layout in this example

returns:

Address
Agenda Item Label Plural
Agenda Item Number | All
Agenda Item Number | Current
City
Contributor Number | All
Contributor Number | Assigned
Country
Date
Description
EVENT ID MATCH FIELD
Email
Event
Guest Label Plural
Guest Number | All
Guest Number | Attending
Layout Selector
Notes
Phone
Postal Code
QuickFind | iOS
Result Label Plural
Sort List Key
Sort Selection
State
Task Label Plural
Task Number | All
Task Number | To Do
Time Frame
Time Frame | Sort Order
Type
Venue

There’s very little official documentation about these virtual tables that I could find – if I’ve made any errors please let me know and I’ll update this post. I’ve created a couple of custom functions for 2 of the queries that I use the most:

ListBaseTables
ListFieldsFromTable