FMDiff™   

This site is W3C compliant:
Valid XHTML - Valid CSS
Last modified February 24 2016, 19:25:30 CET.

The Table Dilemma

What are the Tables in a FileMaker File?

Learn the differences between BaseTables, Tables, Source Tables, and Table Occurrences

With FileMaker® Pro 7 a new file structure was introduced. The most obvious change was the ability to put more Tables into one file, as opposed to FileMaker 6 and earlier, where for every record set an extra file was required.

So why and where is there a dilemma?

Well, let's see what we've got:

The first thing a user sees when creating a new file, is the Define Database for "BrandNewTable" dialog, where BrandNewTable is an example for a file name. On the top row there is a tab group with three buttons: Tables, Fields, and Relationships:

The tab Fields is highlighted. Below to the left is a label Table: with a pop-up menu showing the first table selected, which defaults to the file name. The remainder of the page is pretty much the same we already know from FileMaker 6 and earlier.

Tables

Now we select the Tables tab. It contains the one entry for the first Table that has been automatically been generated for the new file.

Here too we have Tables mentioned five times, once even with an explanation: "A table is a unique set of records and fields. A file can contain more than one table."

To sum up: The Tables we've got to know so far exist physically.

Relationships

Now we take a look at the third tab Relationships.

An explanation on the top says: The relationships graph provides access to data in one table from another. If a relationship is defined between two tables (even through an other table), fields in one table can be accessed from the other.

So far, so good. Up to here there's almost nothing wrong, we are still physical in our explanations. But that second sentence "If a relationship is defined..." should make us wary, also when we look just a little bit further down at the picture of the table "BrandNewFile" with that small arrow icon on the top left of the graph, similar to those used with an Alias or Link in the file system, something is different.

We silently have left the physical area and entered a logical one. Nothing is pointing this out. The user still has the impression we're dealing with the same physical table where she/he may define fields. One could even be tempted to use the leftmost icon at the bottom of the dialog to create a new table. But this is not the case.

Table Occurrence

As you already have guessed, we are in fact dealing with a so called Table Occurrence. This becomes only obvious if we double-click on that table, which opens a new dialog:

For the first time a Table Occurrence is mentioned in a FileMaker dialog. Well, not quite correct: the right column in the Tables tab is titled "Occurrences in Graph", but this is no real hint either. With a dialog titled Specify Table I'm tempted to ask: what kind of Table is to be specified, a BaseTable or a Table Occurrence?

To recap: The Relationships tab of Define Database deals with logical tables (Table Occurrences), not with BaseTables.

Source Table

From interpreting the context of the FileMaker Help where it is mentioned in 15 documents, a Source Table is nothing but a synonym for BaseTable, as in Base Table, Defining calculation fields, About relationships, Working with the relationships graph (under Relationships graph buttons), Selecting related tables in the relationships graph, About creating a new table for imported data, FileMaker Pro format, Relationships graph keyboard commands (Windows), Relationships graph keyboard commands (Macintosh), Choosing the evaluation context for a calculation field, Specify Calculation dialog box, Go to Layout script step, and Import Records script step.

Apparently wrong is the Help Glossary with the definition of Table because in FMI's own words this is a Source Table, or even better, a BaseTable.

There are only two kinds of Tables

May I suggest to rename (at least mentally for now) all occurrences of Table (within the Define Database dialog - except for the Relationships tab), and Source Table (within the Help files) into BaseTables.

The Specify Table dialog should also start with "Choose a BaseTable to include in the graph from either this file or another file...". And instead of "Name of Table Occurrence" I suggest "Name of Table (a link to the BaseTable)".

The dilemma goes on if we look at the functions

• TableNames ( fileName ) returns Table (occurrence) Names, not BaseTable Names
• TableIDs ( fileName ) returns Table (occurrence) IDs, not BaseTable IDs
• Get ( LayoutTableName ) returns Table (occurrence) Name, not BaseTable Name. The description is in a sense wrong (you never learn anything about the BaseTable where the records reside) and the example is rather poor and misleading since it doesn't cover the importance of the Layout to Table (occurrence) connection for the Go to Related Record script step.

Go to Related Record

Especially the Go to Related Record script step is, in my opinion, a poorly described and technically incomplete implementation.

What I don't like is that you have to go to a layout that is tied to a Table (occurrence) if you want to utilize a connection from there to an other Table. If you don't already have a layout for that Table (occurrence) you also have to create a layout first and select the Table (occurrence) in the Layout Setup from the "Show records from:" pop-up. What a fuss just to select a few records in an other table!

The Go to Related Record script step Format is defined as

Go to Related Record [
	From table: "<table name>";
	Using layout "<layout name>" ]

What bothers me is that "From table:" in there, and no localized translation makes this any clearer. If I go from here to there, I do not say I go to From. I think that should read either At table:, To table:, or In table: to make sense.

The "incomplete" implementation became obvious by the winding roads you have to take to Go to Related Records: Create a layout, set its Table (occurrence) in the Layout Setup dialog, in the script then change to that layout, before you finally can Go to the related records. And you even have to be on that layout to be able to define that script step, isn't it so?

What I would consider complete could look like this:

Go to Related Record [
	From table: "<table name>"; 
	To table: "<table name>";
	Using layout "<layout name>" ]

Here the "From table" would be given a real meaning. It is the starting point of a relationship (without forcing one to move to a layout with the underlying (start-)Table (occurrence) first), and "To table" would identify the target Table (occurrence). Using such a script step (could even be a button command) would avoid that overhead described above. One script step would be enough!

If you like that idea, make a suggestion to FMI. The more requests, the better the chances to find an improvement in one of the next versions: http://www.filemaker.com/company/feature_request.html

Other issues

Other issues - more or less related to Tables

FileMaker Pro 8.5 Help should have been corrected in respect to the existence of BaseTables. These old definitions have survived from the pre FileMaker 7 aera:

• Defining database fields: There are no database fields, just BaseTable fields.
• FieldType function: fileName is wrong, should be BaseTable name
• GetNextSerialValue function: fileName is wrong, should be BaseTable name

This document is still under construction and not finished yet (and possibly will never catch all related problems). Any thoughts are welcome.