Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The relationships in FileMaker Pro can and should be used to implement the basic primary key to foreign key relationships that are indicated inside of an entity relationship diagram. And so far in this title we've already used the relationship graph to plot our tables in our database and also to create the relationship between those tables. As a matter of fact, if we open up our relationship graph, which is under File > Manage > Database and then Relationships, it actually looks exactly like our entity relationship diagram, as you can see under File > Define Databases and the Relationship tab.
However relationships that are set up in FileMaker Pro have many powerful uses beyond just establishing the underlying structure of a database. This is probably the closest thing to an intermediate topic in this entire title, so I don't want to scare you off. If you're comfortable with your understanding of relationships thus far, please continue. But if you just barely hanging on at the moment, you might want to stop here for now and come back after you get a little bit more practice. But otherwise let's move forward. You can always use relationships to view a tailored subset of related records or to drill down through several layers of data to form complex queries.
If you're coming from other database platforms, you might be familiar with the concept of queries and actually this might help you understand how FileMaker relationships work. For example, earlier in this chapter, we had a movie that talked about filtering a portal to only show related invoice records for the active customer, but only those related records that had a shipping status of shipped. This is a similar concept to the concept of a query. So although in that case we used the tool for portal filtering to just simply visually filter those layout objects, we can actually set up a relationship between two tables that has that criteria built-into it.
And we can do all of this from the Relationship graph. So first of all, all of these four boxes that we're looking at, the Customers, Invoices, InvoiceLineItem and Products, these are all what are called table occurrences in FileMaker. A table occurrence is actually what it sounds like. It's an occurrence or instance of a database table on the graph. So why bother throwing the word occurrence on there? Well that's because any one table can be represented by any number of table occurrences on the relationship graph. So in this example, and again just to clearly state our goals here, we want to create a relationship that when placed in the context of customers, let's say we want to create a portal on the customer layout, and when we use this relationship it's only going to show us related invoice records that have the status of Shipped.
The first step in order to do this is to create a new table occurrence based on the invoice table, so in this case a new child. And we do that here in the bottom left- hand corner, where we choose the button here that says Specify a New Table. So here we're going to choose Invoices and you see FileMaker puts a 2 after this. That's because every table occurrence must bear a unique name, so you'll always be prompted to name each table occurrence as you add it to the graph. But you don't have to name by number. We can name it something totally different and something that maybe describes what's happening in this relationship.
So we'll call it Invoices Shipped, based on the Invoice table. So here we have two table occurrences, both based on invoices, and the reason I have two of them is because I can't create two relationships between these two table occurrences. I can only have one discreet relationship between any two table occurrences. If I want to change the relationship, I have to create another Table Occurrence. Its kind of the tough concept to wrap your head around, but once you understand that, then you'll know when you need to create new ones and you have a little but more flexibility when you're working in your database.
So a single base table may appear many times in the relationship graph and participate in several different relationships and when I say base table, I'm talking about the underlying table. So notice that Invoices, there's still one table here. So that's what I'm referring to as the base table, even though there are two table occurrences. I didn't create a table, just a new table occurrence. So a single base table may appear many times in relationship graph and participate in several different relationships. And we see here we've got one that's called Invoice Shipped and one that's Invoices.
And so why are we doing this? Well we want to do this because we want to create a relationship with different properties between Customer and Invoices. So we already have this one here that plainly shows us all invoice records, where the value in the primary CustomerID field matches the value in the foreign key ID field. That's how that's going on in this portal that we were seeing earlier. So in plain English, it's saying, show me all my related invoices if you're on a Customer record. And we want to start there with this new one we're creating. So if you remember we create relationships between table occurrences by clicking into the primary key, holding down our cursor, and then releasing it.
And we're going to go primary key to foreign key. Now right now these are the same relationship, but we're not done yet. That's why we're creating another one. So now let's double-click the arrow between these two. You'll see this brings up a new window called the Edit Relationship dialog. And it gives us all sorts of information about the relationship. It tells us the parent table, child table, what the two match fields are, allows us to pick different match fields if we'd like to and change them, or you also see there's a button called Add. And that's really the whole concept here is that we can add additional match fields or additional criteria.
It means show me all the invoice records where the value in fkCustomersID also equals 1530. But now in addition to that, we're going to add another field. So if we scroll down to Customers, we've got a field here that's called ShippedCalc. Now this is the key to creating a filtered or multipredicate filtered relationship like we're doing here. What I've done is I've gone into the Customer table and simply defined a field that's a calculation, and the whole calculation is "Shipped".
So that means every single record in the parent table is going to have the word Shipped on it. I'll show you what that looks like when we're out of this dialog, but the idea there is that no matter what parent on, the parent is going to have their own primary key and then the word Shipped inside this field. So we want this field to match up with ShipmentStatus. We're going to hit add now that I've gotten both selected. So now what we're saying is, in the portal, show me all the records where in this case the number 1530 appears in the foreign key field and also we've got ShippedCalc.
That's giving us the value Shipped. We could see that onscreen . It would just say for every record. So every record that has 1530 in this field and Shipped in the ShipmentStatus will be a related record. So now let's hit okay and see this in action and first before we leave, I want to show you in the Customers table, ShippedCalc, simple calculation, text, just the word Shipped wrapped in there. That's going to give us the same value for every single customer record. They are going to say Shipped.
So now in Layout mode, let's drag select this portal with the fields in there. And you see they are all selected, and I'm going to hit Copy, and I want to go into the Shipped tab that we've created here. Now click inside here. And I'm going to hit Paste. Right-click, Paste. And to I'll move that and to right about this area there. Now you see some of those didn't come through. I'm going to teach you little trick. When you paste more than one layout object into a tabbed panel, you can get some kind of mixed reaction. It's just kind of a weird feature in FileMaker.
So what I'm going to do I'm going to group them all together. What I'm going to do is I'm going to try bringing them forward first. So what I'm going to do is I'm going to click outside this area so they're not selected anymore, and I'm going to just select the area where those fields were and say to Bring to Front. It's a strange feature in FileMaker, just something that you'll have to be used to, but if you've ever paste multiple layout objects into a tabbed panel you do have to go through this. One of the things we could've done is group them before we copy them and pasted them and then ungroup them.
But now we've got the same portal, but let's double-click on the portal and let's change it to Invoices Shipped. Now here's something interesting. It says under Related Tables it's showing the same table twice and that's because we have two table occurrences based on this table. We're going to pick Invoices Shipped, but now we've updated the portal. We also have to update the field. So you'd see here Invoices Shipped, Invoices Shipped and two more, Invoices Shipped.
So now let's go into Browse mode, saving our changes, and you look and you see all the different records that we've got here, but when we click on the Shipped one it's only showing us the ones that are shipped. Same thing here we've got two, one that says Shipped, one that says Processing, and then this only shows the ones it's shipped. So now the concept is we could also create duplicate calculations if we want. Let's so into Manage > Database and then Customers, we've got InvoiceTotal.
Let's say Duplicate, and let's quickly change it to InvoiceShippedTotal, hit Change, hit Options. Now we're going to use a different related field. We're going to pick the Invoice Shipped version of InvoiceTotal. So what's happening is it's filtering this down to just the InvoiceTotal values related to this parent but the ones I've shipped in there.
Now, we hit OK and if we go in the Layout mode, we can click into this portal. We can click into that tab panel and shift click the label and the field, drag them in here, fix the body, go back in our Browse mode and save our changes. And now we see the total for all these, which is 58,000 for all of them, and now we're going to Shipped, and we see the total is just 49,000 ,which actually matches that if we have formatted that properly.
So here we created a new table occurrence between the two tables and then set up the relationship between the Customer table occurrence and the Invoice one with different criteria. Actually, we added additional criteria to it. This is just the tip of the iceberg with complex relationships, but it's a good example of the things that you can do between two different tables in FileMaker solution. And this is certainly a more intermediate topic and hopefully we haven't scared you away from some of the relationship concepts here, but if this made a little the sense to you, there is a whole other level you can go in to with working with filter table occurrences.
So I urge you to play around with this concept a little bit and build this into your own solution and see if you can get this to click.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.