Using ExecuteSQL to Query the Virtual Schema/System Tables

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

10 replies
  1. Daniel Wood
    Daniel Wood says:

    Thanks for this article Andrew, this is going to be prove very useful to me! I was after a way to get all of the field names of a table, not just those on a layout ala the FM design functions, this is perfect, thanks!

    Any idea how I might be able to go about retrieving a list of table occurrence names in a solution that are related to a specified table occurrence, and exclude those which are not?

    Cheers!
    Daniel

  2. Andrew Duncan
    Andrew Duncan says:

    Hi Daniel,

    If I’m understanding your question correctly you should be able to use something like this:

    ExecuteSQL ( “SELECT TableName FROM FileMaker_Tables where BaseTableName = ‘Contacts’ ” ; “” ; “” )

    Using the Event Management starter solution this returns:

    Contacts
    Guests | Contacts

    Let me know if I have misunderstood your question.

    regards,
    Andrew

Trackbacks & Pingbacks

  1. […] [For more information see Andrew Duncan’s superb article: Using ExecuteSQL to Query the Virtual Schema/System Tables ] […]

  2. […] van de veldnamen van de L-tabel gaat met een truukje dat sinds FileMaker 12 beschikbaar is. Op dit blog van databuzz staat beschreven hoe je met ExecuteSQL informatie kan opvragen over de structuur van de database. […]

  3. […] [詳細については、Andrew Duncanのすばらしいブログ記事(Using ExecuteSQL to Query the Virtual Schema/System Tables)を参照してください] […]

  4. […] Using ExecuteSQL to Query the Virtual Schema/System Tables – Andrew Duncan […]

  5. […] list of tables from a massive file to aid in the coordination of a data migration. I ran across an excellent article from databuzz that describes a semi-hidden feature of Filemaker 12; the ability to use ExecuteSQL() to query the […]

  6. […] one of the FileMaker virtual tables – FileMaker_Fields – which I first learned about from a post by Andrew Duncan. Here’s the query that I […]

  7. […] one of the FileMaker virtual tables – FileMaker_Fields – which I first learned about from a post by Andrew Duncan.   Here’s the query that I use: […]

Comments are closed.