Door 15 Information about tables and base tables

Today, I would like to introduce you to a few functions that do not directly affect SQL queries, but can still be very useful. In order to perform SQL queries, we need to know the database structure of our database. Which tables do we have and what are the names of the fields in the corresponding tables? I would now like to find this out together with you. Our plugins have included the relevant functions for years.

Let's start with the functions provided by the names of the tables that exist in the database. You may be wondering: functions? Why more than one? This is because we need to distinguish between tables and base tables. Base tables are the actual, physically existing tables within a FileMaker database. Each of these tables contains the actual records and fields, regardless of how often or under what name they are used in the relationship graph. These base tables are queried using the MBS function FM.QueryBaseTableNames. The function does not require any additional parameters if you want to know the base tables from the database in which the corresponding function is called. If you want to have the table names from another database, you need the optional parameter in which you can specify the name of the desired database.

Then we have the FM.QueryTableNames function. In contrast to the base table, this function also counts table occurrences, which are only references to the base tables, and displays them in the list. The parameters for this function are identical to those for the previous function.

FileMaker also has functions for querying base table names and table names. While FileMaker's own BaseTableNames function and the MBS function FM.QueryBaseTableNames produce the same results, there is a difference between TableNames and FM.QueryTableNames. When we delete a base table in a database, the table occurrence remains in the relationship graph even though no table is assigned to it. FileMaker returns such table occurrences without a base table as tables, whereas the plugin sorts out these table occurrences.

Let's take a look at what values the individual functions output when used on our database. This is our relationship graph.

Because we're working with SQL here all the time, we don't have any relationships between the tables. The dark gray tables are table occurrences of tables that once existed but have now been deleted, so these table occurrences no longer have a base table. The blue table is a table occurrence with the base table Movie as its basis.

So what happens now if I first apply the BaseTableNames and FM.QueryBaseTableNames functions to this database?

Set Variable [ $BaseTableFM ; Value: BaseTableNames ( Get(FileName) ) ]
Show Custom Dialog [ "FM -  BaseTableNames" ; $BaseTableFM ]
Set Variable [ $BaseTableMBS ; Value: MBS("FM.QueryBaseTableNames"; Get(FileName)) ]
Show Custom Dialog [ "MBS -  FM.QueryBaseTableNames" ; $BaseTableMBS ]

The results are identical here.

The situation is different for TableNames and FM.QueryTableNames. Here, the MBS function does not display table occurrences that are not assigned to a base table. They are still listed in the FM function.

Set Variable [ $TableFM ; Value: TableNames ( Get(FileName) ) ]
Show Custom Dialog [ "FM - TableNames" ; $TableFM ]
Set Variable [ $TableMBS ; Value: MBS("FM.QueryTableNames"; Get(FileName)) ]
Show Custom Dialog [ "MBS -  FM.QueryTableNames" ; $TableMBS ]

For us, it's not just the table and base table names that are interesting, but also the field names in each table. Here, too, we have two functions: FM.QueryFieldsForBaseTableName and FM.QueryFieldsForTableName. As the name suggests, there is again a distinction between base tables and tables. We can see this difference when we query the fields of the Movie_Copy table occurrence. The FM.QueryFieldsForTableName function provides us with the corresponding field names, while FM.QueryFieldsForBaseTableName returns an empty text because it is a table occurrence and not a base table. When calling both functions, we first specify the table name in the parameters and then, if necessary, the file name of the database.

Set Variable [ $BaseFieldNamesMBS ; Value: MBS("FM.QueryFieldsForBaseTableName"; "Movie_Copy"; Get(FileName)) ]
Show Custom Dialog [ "MBS -  FM.QueryFieldsForBaseTa…" ; $BaseFieldNamesMBS ]
# 
Set Variable [ $FieldNamesMBS ; Value: MBS("FM.QueryFieldsForTableName"; "Movie_Copy"; Get(FileName)) ]
Show Custom Dialog [ "MBS -  FM.QueryFieldsForTableN…" ; $FieldNamesMBS ]

That brings us to the end of today's session. I hope to see you again tomorrow.