"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below!



Beginner, Intermediate and Advanced Video Training


Recent Blogs:

Parsing for Features
Parsing for Features

Parsing A Web Form
Parsing A Web Form

Data Parsing
Data Parsing

Anatomy of a Calculation
Anatomy of a Calculation

Seven for Seventeen
Seven for Seventeen




$10.00 Beginner Video Training


Quick Tip:

Merge Field Madness
I'm sure most of you have run into this problem at one point or another. You wish to place a merge field on the layout, but the name of the field extends beyond your available layout space. While the data still displays correctly in browse mode, the length of the merge field covers up nearby objects, making it difficult to work in layout mode. One solution is to shorten the name of your field. If you can't or don't want to do that, try changing the font size of your merge field so the text block fits in your available space. Keep the first less-than sign in your chevron (e.g. <) the font size you want to display the merge data. Even though most of the text in your merge field is a tiny font size, the first merge field character will set the size for the data. Pretty cool. This can also be done with symbols that are surrounded by curly brackets (e.g. {).



Tips & Tricks Videos


Fun Stuff:

Claris and FileMaker CEOs
Bill Campbell  1987 to 1995
Guerrino De Luca  1995 to 1997
Dominique Goupil  1997 to Present



FREE FileMaker 17 Videos



Document Management Videos



Create a Complete Contact Manager







RSS Feed
Context & Relationships
Level: Beginner
Version: FileMaker 17
Category: Relationships
Tuesday, August 7, 2018
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.

Context & Relationships

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.

Context & Relationships

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:

Context & Relationships

We’ve entered all of the fields that we need, for the time being, into the Clients table.

Context & Relationships

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.

Context & Relationships

Context & Relationships


We’ve now got a list of clients:

Context & Relationships

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.

Context & Relationships

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.

Context & Relationships

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.

Context & Relationships

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.

Context & Relationships

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.

Context & Relationships

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.

Context & Relationships

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.

Context & Relationships

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).

Context & Relationships

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.

Context & Relationships

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).

Context & Relationships

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.

Context & Relationships

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, =, ≠, >, ≥ (>=), <, ≤ (<=).

Context & Relationships

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,

Context & Relationships

and in the Invoices table you create another calculated field (Year) to give you the year of each invoice, i.e., year(date).

Context & Relationships

The relationship would be:

Context & Relationships

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:

Context & Relationships

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:

Context & Relationships

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.

Author:
Michael Rocharde
michael@michaelrocharde.com
www.michaelrocharde.com

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window. Thanks so much!

Comments:

Mauricio Navarro 08/08/2018
  Cool. Thank you for taking the time to respond.
Mauricio Navarro 08/07/2018
  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.
Response by:   Michael Rocharde 08/08/2018
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.

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!