FileMaker Pro Turns 7

Estimated Time To Read This: 5 – 8 minutes

Just over 8 years ago, we announced the new features of a just-released FileMaker Pro v3.0. This past March saw the introduction of FileMaker Pro 7. In comparison, FileMaker Pro 7 boasts more new features over version 6 than version 3 did over 2. As if new features weren’t enough, many developers are also finding themselves grappling with a paradigm shift in how to think about databases. Some changes, and the new language that’s required to describe them, are so different it’s been dubbed “7-think”. To see a list of the new features and specifications, visit http://www.filemaker.com/products/fm_overview.html. One of the more difficult concepts with this new version is the new table structure, relationships and table occurrences.

Multiple Tables per File
A table is a database element that is made up of fields (columns) and records (rows). A FileMaker Pro 7 file can store (theoretically) a million tables per file whereas in FileMaker Pro 6, a table is a file. Nomenclature of database elements therefore is very important; the number of tables in your solution is not necessarily the same as the number of files in your solution. When designing your solution, you must now consider which tables will reside in which files or indeed how many files you will need. Some solutions may only require one file with all the tables within it; others may have multiple files each with one or multiple tables. Factors that affect this decision range from security features to data movement to storage of value lists.

Table Occurrences and Context
Context is very important in this new version. Context refers to FileMaker’s ability to “see” data in a related table using the path you define in the relationship graph (I’ll discuss the graph shortly). Different paths and contexts are made possible using table occurrences (TO) that point to a base table but can be named however you like. It’s actually the table occurrence you will use when building layouts, defining scripts and just about everything else in your file. You can have as many table occurrences as you want and the only rule is that any given relationship path cannot form a circle (TO-A relates to TO-B which relates to TO-C which cannot relate back to TO-A but could relate to TO-A2 where A2 is another table occurrence of Table A). An example of this can be found in the sample file for this issue, RelationshipSample.fp7. In figure 1 below, Contact and ContactOfLog both point to the Contact table.

Fig. 1

ContactOfLog was necessary to show the name of the contact that owned the log when that log record is viewed from either Company or Contact. The reason for this is that the data view wants to go in one direction: in this case, from Company to Contact to ContactLog; to come back to Contact from ContactLog would be going in reverse. Since a relationship from ContactLog to Contact would produce a circular relationship, a new table occurrence is used.

Relationships
As you can see from the graph in Figure 1, defining relationships has changed a lot since version 6. It’s now graphical and the relationships are bidirectional. That means only one relationship needs to exist between two table occurrences for one to see the other’s data. In version 6, a relationship would have to be created in Company pointing to Contact to see the related contacts for a company. In order for a contact record to see it’s company’s data, another relationship would need to be created in Contact pointing back to Company. A relationship in FileMaker Pro 7 exists between any two table occurrences. In order to create a self-relationship, another table occurrence of the same table is used to relate to. FileMaker Pro 6 and earlier had what is known as equi-join relationships: where a value on one side of the relationship equals the value on the other side. In FileMaker Pro 7, this is extended to greater than, less than, not equal, and cross-product. A relationship can also have multiple criteria called predicates. An example of a self-relationship using multiple, non-matching predicates can be seen in figure 2.

Fig. 2

The definition of the relationship follows in Figure 3.

Fig. 3

The desire is to show, on a contact’s record, a portal of coworkers that work for the same company as the current contact but we want to leave the current contact out of the list. The first predicate in the relationship will find all the contacts that work for the same company; the second predicate will eliminate the current contact. As you scroll through the contacts in the sample file, you’ll see how the portal automatically hides the current contact from the list of coworkers.

Lots to Think About
It should be pretty obvious that there are a lot of changes in this new release of FileMaker Pro. For many of us, it’s like returning to that time eight years ago when another groundbreaking version had just been released giving us lots to think about and many challenges to overcome.

Downloads:

FMPTurns7SampleFile.zip(102KB)
Steve Hearn