Context & Relationships

Hello there

This post is somewhat different from my usual ones as I’m including a download link to a chapter from my upcoming book (FileMaker™ & You, Volume 2) at the end of the intro. The topic was inspired by a good friend, and fellow FileMaker™ developer, Mike Bielinski from the National Renewable Energy Laboratory (NREL). Mike was saying that he knew a quite a number of people who had difficulty understanding the concept of context and relationships.

Now while this post has been written for FileMaker™ newbies, seasoned developers may also find it of interest, especially if they ever need to explain this stuff to clients. I hope you find it of interest and I would, absolutely love to get your comments and feedback.

In FileMaker™, there are, almost always, multiple tables each containing specific information. Each table can have multiple table occurrences (TO’s) and each TO can be connected to other TO’s, including self-joins where the TO is connected to another TO of the same table, most often using a constant or cartesian join so that all records in that table are visible in a portal, commonly used to navigate between records.

The relationships between tables is contextual and, in FileMaker™, context is everything but it’s a, somewhat, difficult concept to grasp at first and even many professional developers (including myself), struggled with it in the early days of FileMaker™ 7.

In this article we’re going to explore both context and relationships. When you understand both of these, you will be well equipped to design and develop a relational database. However, before we dive in, we need to take a minute to explain what a relational database is and how it differs from a flat-file database (which is how FileMaker™ began).

Let’s say that you have a Clients table and an Invoices table. The client table, as does every other table, has a field which is referred to as a primary key and this is set, automatically, with a calculated value of get(UUID). The primary key is, generally, an alphanumeric string of 36 characters (including dashes) and is as unique as it is possible to be. (The chances of it being duplicated are somewhere in the region of 1 in 82 trillion).

The primary key is also rarely, if never, seen and the user has absolutely no ability to modify or delete it. In a second table, there is, what is referred to as, a foreign key. This foreign key is related/connected to the primary key in the main table.

In a flat-file database, information passes one way and one way only. So, when you create an invoice and enter the primary key, for the client, into the foreign key ClientID (in the Invoices table), client information is looked up (copied over) into the Invoices table resulting in two important things; one of which is a massive duplication of data, and the second is that client data in the Invoices table could be edited, but those changes, i.e., a new address, will only be in that one invoice and all other client information, whether it be in the Client file itself, or other invoices for the same client, will be unchanged. (If you did change it in the Client record, you could update all of that clients invoices via a re-lookup but this is not something we really need to be concerned with).

To read the entire chapter, click on the link below.

Leave a Reply

Website Apps