The FileMaker relationship graph can be used to create query relationships in your custom apps. Watch how to add multiple criteria in Edit Relationships to create queries.
- [Narrator] If you've watched videos on relationships or seen a class or a presentation that I've done before, you might have heard me talk about the concept of query relationships. And that's because the idea of relationships and the way that they're presented in FileMaker can be one of the more challenging aspects of learning FileMaker. This is known at all levels within the FileMaker organization, and I'm sure in the future there will be engineering improvements to help make this a little bit easier to understand. In the meantime, I would like to draw a delineation between the concept of a base table relationship, and what I refer to as a query relationship.
I'd like to give you examples of both of those here because I think it's really important to understand that distinction, so that you can make sense of what you're seeing in the relationship graph. And the other thing I'd like to mention, is that if you feel you need a refresher, some background information on relationships in FileMaker Pro, please first watch Relational Database Design with FileMaker Pro. It's a video that's on this website. It will serve as a great primer for this whole section of videos that you have here, and it's highly recommended. So please, again, Relational Database Design with FileMaker Pro. And then I'll look forward to you coming back, and learning a little bit more about some of these concepts.
So, with that said, relationships in FileMaker have some really powerful uses. Let me show you the distinction of what I'm talking about when I talk about a base table relationship, and then when I talk about a query relationship. So if I go into Manage Database, and we're going to go into the relationship tab. So first of all, a relationship is a one-to-many relationship between two tables. Okay? That's what really should be in the relationship graph. The reason that a relationship graph was presented in a visual fashion in the first place, is so that you can represent your Entity-Relationship diagram, which of course only has one-to-many relationships in it.
Beyond that, there is no visual upside whatsoever in the programming of FileMaker databases to see boxes that represent relationships. Especially when you get into what really are queries. The fact that we have to force a query into looking like a box, and fit it into an Entity-Relationship diagram, frankly has caused nothing but consternation and added complexity to learning what a relationship and a query really is in FileMaker. So let me do my best to try to explain to you my theory here. So first of all, I've tried to add some color coding, as you can see.
The red color represents any table occurrence that you see here that's based on the customer table. Green for invoice, products are in orange, and then the purple color is the join table of product invoice. If we ignore the relationships that you see here in the bottom, for the time being, what we have is a one-to-many relationship between one customer having many invoices, and one invoice can have one customer. One product can have many product invoices, and one invoice can have many product invoices.
The product invoice's join table is created as a result of resolving the many-to-many between invoices and products. Now, those are relationships. That's how we spec'ed it out in the data modeling process. Let's just call a relationship anything that you can define in the data modeling process. Now the reason these are called table occurrences is because you can have more than one occurrence of a table in your relationship graph at the same time. Pause for reflect. So let's look down here and see why are these two table occurrences green? Well, there are also table occurrences that are based on the invoices table.
It looks as though there's three tables here, but there's actually just one. As a matter of fact, if you go into the Manage Database, you'll see that we have a total of five actual tables. Five tables, that's it. But we have one, two, three, four, five, six, seven table occurrences. By that, we realize that these boxes actually aren't tables. So, it gets a little bit confusing when we start to add boxes beyond the number of actual tables that we have in the database. And that's why they're called table occurrences. Now why would I have created another box that represents an invoice? Well, if we look at the relationship between customers and invoices, we're joining the PK and the FK, it's going to evaluate our one-to-many relationship, so we're saying, in a portal for example, show me all the invoices that are related to the customer record that I'm currently on.
Or more specifically, any time the value in the customer ID PK field is equal to any value in the customer ID FK field in any record in the invoices table, that means that those records in the invoices table are related to that one record in the customer table. This is the rule that will define when records are related to each other, when these two tables are concerned. Let's say we want to have a portal on a customer record that shows us all the invoices that are unpaid, and we want another portal for all the ones that are paid.
Well, as it turns out, we actually have that. We're on this customer, and we have a portal right here that shows us... if we look in layout mode we can see all the invoices, and then these are all the paid invoices. Okay? So the paid invoices, or unpaid invoices, we look at the way that the relationship is set up here we're going to see something really interesting. We have the PK and the FK, that's fine, that's our Primary Key and our Foreign Key matching. That's a base table relationship, but we've added an additional criteria here.
So if we remember the rule of the relationship actually says when this value equals this value, then the records are related. But now we've added an And to this. So we're saying, when the value in the unpaid match field equals the value in the status, and the PK and the FK are equal to each other, now we have a match. As far as relationships are concerned, and databases in general, this is not a relationship. This is what we call a query.
For those of you familiar with queries in other languages, like SQL, where Q stands for Query, we would actually call this a Statement, where we would say, I would like all the records from such-and-such table where the payment status value equals unpaid, and the customer ID value matches the FK value. That's what we call a query. So we have to kind of force that a little bit in FileMaker. It's still very powerful in FileMaker. This is not a pejorative statement against the FileMaker platform. This is just a way that I am hoping will make a light bulb go off when we're talking about multiple match fields, or query relationships in FileMaker.
So in this case, the key is what's in that unpaid match field, field. This is kind of where it gets a little hack-y, frankly, for FileMaker because I have to put a value in this field that says unpaid. So every record in the customer table is going to have the same value in it, unpaid. This could have been a calculation, that's another technique is instead of having a text with the global, we could have just made this a calculation field because a calculation that just says the words unpaid means every record in the database has that value. Now it's a little hack-y I say, just because I have to define a field in the parent to be used as a match in this query relationship.
But at the end of the day, I have one query for paid, and one query for unpaid. Both of them have an unpaid match field, and there's a paid match field as well. The same general concept here. So what that means is that I can now draw a portal in the case of a portal, where I am only displaying the values that match, not just the related invoices. Let's go to a record, here we go, this is a good example.
You see here that we actually have two related records. We have the unpaid record, and the paid record. These are just the one-to-many relationships between the customer record and the invoices. These are the matching invoice records. Over here, we're only showing the one that's paid, because we're using the query relationship to filter this portal. And if you look at these unpaid versus paid calculations, we're referencing that same query relationship. And the benefit that we get by doing that is we're saying, don't just give me all the related invoices, go look at the criteria that we added in the query relationship, and filter all the invoices through that query.
So what we're really saying there is, give me a count of all the invoices that are related to the record I'm on, where status equals paid. So for those of you that are already familiar with queries, this approach will probably make a lot more sense. For those of you that are still trying to learn it, I'm trying to help instill a foundation in you that will help you learn the concept of relationships in FileMaker, and I think the easiest way to do it is to think of the relationships that you set up in your ERD as part of your base modeling exercise.
Those are base table relationships, and any other relationship with more than one set of match fields is what's called a query relationship.
- Using calculations in field options
- Using multiple criteria in relationships
- Advanced reporting
- Securing your custom apps
- Sharing your apps with FileMaker Server
- Configuring FileMaker Server
- Performing scripts on FileMaker Server
- Deploying FileMaker WebDirect
- Integrating with non-FileMaker databases
- Communicating with iBeacons
- Working with JSON