Ordering Fields The order in which Manage Database displays fields determines the order of any other dialog displaying fields. This can be troublesome when trying to locate a field in a long list. Just remember to go back to Manage Database and change the sort order to field name so the fields are alphabetical. In addition, if you host a FileMaker file on a FileMaker Server, the default settings for the Manage Database field order will be the same as they were when you last used it in single-user mode.
Context is everything! Haven’t you ever had something you said taken out of context and then tried, desperately, to figure out how to get yourself out of the hole you just, inadvertently, dug for yourself?
The same goes with relationships. In life, as in FileMaker, they can be tricky and, if you don’t know what you’re doing, you can get yourself in a whole mess of trouble.
This article is really for newbies to FileMaker; anybody who has been working with FileMaker for a while will, or should, know this stuff so it’s very basic but, hopefully, it will have some value.
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 used, most often, to navigate between records. (This is going to be less common with the introduction of Master Detail layouts, in FileMaker 17 but more about that in another article).
The relationships between tables is contextual and, in FileMaker, context is everything but it’s a somewhat, difficult concept to grasp, at first, and 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 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).
Let’s illustrate this so that you fully understand it.
In our database, we have created two tables, Clients and Invoices, and have related them via the ClientID:
We’ve entered all of the fields that we need, for the time being, into the Clients table.
Then we copied those fields into the Invoices table, added the field ClientID and then set all of the other fields to lookup (copy) the value from the Clients table. This is the way that a flat file database works.
We’ve now got a list of clients:
But we have no invoices yet so we’re going to create a few.
The field on the far left is set as a drop down list or a pop-up menu and when you click into it, it displays all of the clients (from the clients table). Although you will only see a name, the field itself is set to enter the ClientID.
When you make a selection, the program copies over all of the information from the Clients table to the Invoices table.
In this example, we have sorted the records by the client name and you’ll see that we have 4 invoices for Boy Scouts of America.
I’m now going to change the address in the 1st record to a different street and city. You’ll notice that only the top invoice has changed with all the other three now having information that is no longer correct.
So not only do you have more data than you want but it’s now inconsistent.
So let’s take a slightly different look at our data. In this screenshot, the fields in pale green are from the Clients table. (You’ll notice that the first address is different from that stored in the Clients table but that address is now correct so I want to update all of the records accordingly. To do so, I’m going to edit the green fields (and it doesn’t matter which record I edit.
In the second screenshot, you’ll notice that all the green fields match in every record but the white fields, i.e., the looked up information remains the way they were entered.
So this example, in its entirety, is how a flat file database works. Because the data is only flowing in one direction, i.e., downwards, we have the potential for errors and it is a task, albeit not a difficult one, to update records.
In a relational database, information is displayed from the source table so not only is there no duplication of data, but changes made anywhere are reflected everywhere. This means that if you edit the clients address, either on an invoice or in the client record, the address is instantly changed on all other invoices for that client and in the client record itself. In other words, information passes in both directions.
To change the database functionality from that of a flat file, we need to delete all of the client fields from the Invoices table.
When we select a client, in the Invoices table, all we are doing is entering the clientID which then displays all of the information from the Clients table allowing us to view the same, correct, data every time and also allowing for edits to that record to be made in either location.
So, with that all said and done, we’re now back to context. Data can only be used from a table if that table is related to the correct TO. This might sound confusing but bear with me (and I’ll put it into context ). Continuing with our example, you have a clients and an invoices table. For the invoices table, there are 2 TO’s); one called just Invoices which is not related to any other TO and the second one is called Client_Invoices (the naming convention telling you that the TO is related to the Client table).
You now go to a layout based on the Invoices table and want to show the client name for each invoice, but you can’t because there is no relationship from that TO to the Client table; in other words, you are out of context.
To fix the problem, you simply have to change the TO, that the layout is based on, to Client_Invoices . Now, you are in context and can see the Client Names. (You also have to make sure that the fields on the layout also reflect/are from the TO of that layout).
With calculations, you have to select the TO that the calculation is starting from otherwise the calculation won’t resolve/work. Thus, in developing, you have to always be aware of what the context is and, when something doesn’t work, the first thing to check is if the context is correct.
In the above illustration, you can see that I have two options but I am using Client_Invoices as the starting point for my calculation. (If you’ve been paying attention, as I’m sure you have, you may have noticed that I’ve created a calculation field to get the Client Name into the Invoices table, thus duplicating data which we’re trying to avoid. In this instance, I’ve done that because I want to sort a portal of invoices by the client name and, to do so, I have to have that name stored in the Invoices table. It’s also set as a calculation rather than looking up the value as if the name changes, the invoice records will instantly change to the updated value).
Let’s now move onto relationships which are the connections between TO’s. A relationship can be Cartesian, a term created by the philosopher René Descartes (1637), and which means all records in one table are related to all records in another. The Cartesian join symbol is an x and it’s very commonly used. Personally, and this is just me, I have a calculated field, called One, with a value of 1, in every table, and I use that field in a relationship where one = one. (I also use that field extensively to test if there are related records and then act accordingly).
A relationship can have multiple criteria using the following parameters, =, ≠, >, ≥ (>=), <, ≤ (<=).
Bear in mind, though, that the more criteria you have, the less related records will exist and the longer the relationship will take to resolve; we are talking about fractions of seconds, but it still takes more time than usual; however, you may not notice or be aware of it. (If you are working on a hosted database with large numbers of records, then you will, definitely, be aware of it).
Let’s say that you have a relationship from Clients to Invoices where you want to see all invoices for the previous year, and yes, you could do a search for invoices within that date range, but I prefer to use the relationship. In the clients table, you might create a calculated field (c_lastYear) to give you the previous year,
and in the Invoices table you create another calculated field (Year) to give you the year of each invoice, i.e., year(date).
The relationship would be:
If you wanted to create a relationship to show all records for a specified date range, you would create 2 global fields, G_StartDate and G_EndDate and would relate those fields to the Date field in Invoices. That relationship would look like:
and then you would set a script trigger on exiting the G_EndDate field to go to the related records.
If you wanted that relationship to be for a specific client, you would modify it as follows:
By the way, this relationship is one of the most useful you will ever find so stick it in your memory bank.
We’ve now covered context and relationships, albeit briefly, but there should be enough here for the novice developer to grasp and move forward.
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
I want to thank you for writing this article. It has helped me to better understand some issues I have been having in redesigning a solution I wrote over 20 years ago in FM 5. I've been arguing with myself for months on having a separate table for addresses vs storing them in the client record. I guess I was still stuck in flatland.
That's so nice of you to leave a message of thanks. You might also want to peruse this article titled Wrangling Relationships.
Hi, I am surprised to introduce a "flat table" approach as it is really not a relational topic. I understand it just as a basic primer to explain a relational database. Fine, so far. But the example is a bit misleading for beginners as it lacks the importance of related and copied data in a relational database - especially when building an invoice example. For invoices it often is NOT a choice to relate e.g. address data from a clients table (also like prices from a product table). Just because an invoice has to stay as it was when printed/sent-out. If a later change of address (or price of a product) does lead to a change of the data in an invoice - it is changing the historic contexts.
Personally I would suggest that such an example should use a case where this is not of interest or it should get extended of this concept, too.
Martin, thanks for your comment. I do take your point about the invoice address but don't necessarily agree, I always store a copy of each invoice as a pdf in a documents table so can go back and review it if necessary. Also businesses quite often change addresses so it's a normal thing. The main thing is that the prices do not change which is why those are looked up at the time of creation and never updated.
Finally, the purpose of the example was illustrative more than anything and the focus of the article being context and relationships which are, for newbies, often hard to understand.
Kind regards and thanks again.
(303) 856 5778
Cool. Thank you for taking the time to respond.
Thank you for the tip. Although I've been "in love" with FM since 1990, and try to digest from any tip or sample that crosses my path; it never ceases to amaze me how there isn't a stop to learning. I wish I knew more but I don't. On this article you mention creating a calculated NAME field (I paid attention but did not see the calculation).
And I have a question if I may, is it possible to make a relationship work out of empty fields? I just saw an example of a file that does not use portals to create related records, but for my life I do not follow its context (and it looks so simple!). Gracias, again.
The calc field name is simply upper(lastName)&”,”&firstname. Thus John Brown would result in BROWN, John which you can sort by instead of sorting by last Name and then by first name.
Re the second question, it’s a very old trick and completely outdated. It was a way of avoiding having ghost rows (not a record but looked like it was one) when you are creating related records via a relationship that allows that. Now, you allow the creation of related records via the relationship. In every table, you have a calculated field, one with a value of 1. In the portal you have the fields that you want but only one of them is showing with the others hidden until the record is created. Let’s say that you have a portal of people called People. In the portal you have three fields and a trash can. The field that will show is firstName and the other two fields and the trashcan are hidden when People::one # 1. In the portal, all the user will see is the records that have already been created and on the next row, just the one field for the first name to be entered. So you type John in that fields and tab and immediately the other fields, and the trashcan, appear because People::one now = 1.