FileMaker system tables

Every FileMaker Pro database file includes these system tables:

  • FileMaker_Tables

  • FileMaker_Fields

  • FileMaker_BaseTables

  • FileMaker_BaseTableFields

  • FileMaker_ValueLists

  • FileMaker_ValueList_<valueListName>

For ODBC applications, these tables are included in the information returned by the catalog function SQLTables. For JDBC applications, these tables are included in the information returned by the DatabaseMetaData method getTables. The tables can also be used in ExecuteSQL functions.

FileMaker_Tables table

The FileMaker_Tables table contains information about the database tables defined in the FileMaker Pro file.

The FileMaker_Tables table includes a row for each table occurrence in the relationships graph with the following columns:

  • TableName - The name of the table occurrence.

  • TableId - The unique ID for the table occurrence.

  • BaseTableName - The name of the base table from which the table occurrence was created.

  • BaseFileName - The FileMaker Pro filename for the database file that contains the base table.

  • ModCount - The total number of times changes to this table’s definition have been committed.

Example

Copy 
SELECT TableName FROM FileMaker_Tables WHERE TableName LIKE 'Sales%'

FileMaker_Fields table

The FileMaker_Fields table contains information about the fields defined in the FileMaker Pro file for all table occurrences.

The FileMaker_Fields table includes the following columns:

  • TableName - The name of the 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 table’s definition have been committed.

Example

Copy 
SELECT * FROM FileMaker_Fields WHERE TableName='Sales'

FileMaker_BaseTables table

The FileMaker_BaseTables table contains information about only the source (or base) tables defined in the FileMaker Pro file. Because it doesn't include all table occurrences as FileMaker_Tables does, queries of the FileMaker_BaseTables table may be faster.

The FileMaker_BaseTables table includes a row for each base table with the following columns:

  • BaseTableName - The name of the base table.

  • BaseTableId - The unique ID for the base table.

  • Source - One of the following about the data source for the base table:

    • <Internal> - The table is in the current FileMaker Pro file.

    • <Missing> - The data source is not available.

    • The name of the external data source the base table is available from.

  • ModCount - The total number of times changes to this base table’s definition have been committed.

Example

Copy 
SELECT BaseTableName FROM FileMaker_BaseTables WHERE BaseTableName LIKE 'Sales%'

FileMaker_BaseTableFields table

The FileMaker_BaseTableFields table contains information about the fields defined in the FileMaker Pro file for only the source (or base) tables.

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.

Example

Copy 
SELECT * FROM FileMaker_BaseTableFields WHERE BaseTableName='Sales'

FileMaker_ValueLists table

The FileMaker_ValuieLists table contains information about the value lists defined in the FileMaker Pro file. Relational value lists are excluded from the results.

The FileMaker_ValueLists table includes the following columns:

  • ValueListName - The name of the value list.

  • ValueListId - The unique ID for the value list.

  • Source - One of the following about the source for the value list:

    • Custom - Value list based on custom value.

    • Field - Value list based on field value.

    • External - The value list is in the external FileMaker Pro file.

  • ModCount - The total number of times changes to this value list's definition have been committed. Relational value lists are returned empty.

Example

Copy 
SELECT * FROM FileMaker_ValueLists WHERE ValueListName='SalesType'

FileMaker_ValueList_<valueListName> table

The FileMaker_ValueList_<valueListName> table contains one or two columns that contain all the value list items in the value list specified by <valueListName>.

The FileMaker_ValueList_<valueListName> table includes the following columns:

  • Values - The value from the value list.

  • SecondValues - The value of the second field If the value list is based on a field value and a second field is specified.

Example

Copy 
SELECT * FROM FileMaker_ValueList_SalesType

Note  If there is an error fetching values from the specified value list, a message will display "missing" or "unsupported".