Using ExecuteSQL to Query the Virtual Schema/System Tables

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