Creating a "self" relationship

Standard Relationships in FileMaker are going to be between one table occurrence and another table occurrence. Now in the examples that we reviewed so far in this course, we have been making relationships between Table Occurrences that are based on two different tables. For example, you can see the Customer Table related to the Invoice Table, and a couple other examples here of those or the Products Table related to the Products Invoice and so on. But when it comes to this complex, or like I like to call them, query relationships, we can have one table occurrence be from one table, and as we see here another table occurrence from the other, so two different contexts.

And we saw that in the previous examples, where we were using the Dashboard relationships. So we have the Dashboard Table is one context and the Invoices Table is another. But in this movie I'm going to show you how you actually create a relationship between two table occurrences that are both based on the same table context. That's what we refer to as a self relationship. Sound like an odd idea, but let's say in our example we've got this relationship based on Invoices and over here in this corner, I want to put a portal on here that helps our customer service or our order entry people see all the different Invoices from the same customer.

So that way the customer service people can see when the last order was, what their frequency is and different tendencies and stuff like that. So in order to do that if you think about it, the context is going to be the Invoice Table, but the context of the portal is also going to be the Invoice Table. So I need to create a relationship between Invoice and Invoice. So let's go into our Relationship graph. We already have a relationship that's based off of Invoices. So what I need now, is one that's also based off of Invoices. I am going to create a new one, I am going to pick Invoices, and I am going to call this one INVOICES because that's the contact where we are starting from, and this is the current contacts where we are going to, and we'll say samecustomer.

invoices_INVOICES_samecustomer. So here's my Relationship, make it a little bit larger and we'll make it green, and now this is really where the trick comes into play. In this technique, if you remember, our goal will be, we will have a layout that's based on the Invoice Table Occurrence, and in a portal, show all of the other Invoices that have the same customer. So what we are really saying is that the records that will show up in the portal must have the same customer ID as the selected Invoice record that we are on. So we have got this field here called CustomerID and if I just simply match it to CustomerID, that means that if I'm on a record with CustomerID18, it's going to show me all the Invoices that also have CustomerID, the CustomerID, here we go.

CustomerID18, show me all the Invoices that have the CustomerID foreign key value of 18 as well, pretty simple stuff. So CustomerIDfk will be our match field on both sides of the relationship. So now let's place a portal based on this relationship on to the layout. Here we go. I'll draw a portal and let's pick the relationship that we just created, which is Invoices_INVOICES_samecustomer, and I'll do a vertical scrollbar, I'll pick a couple of fields.

So let's say the Date, the Grand Total, Payment Status, and the Ship Date. Just for this exercise I am going to put the InvoiceID on there as well. So now we see all those different fields, and of course we can go in and we can add some field columns if we want to, but let me show you something when we get into Browse mode. First of all we see that we need to resize this, so just a quick discussion on resizing; I can pick the portal and you'll notice the appearance over here, it's locked in the upper left-hand corner. I need it be locked in to the bottom left-hand corner.

So let me pick that, do the same thing, I'll hold down the Shift key for all these fields, and I'll change them from the upper left-hand corner to the bottom left. Let's just quickly look from a formatting standpoint how that changes. So there we go. Also, we've got a question mark in the one field, so what that means is that we just need a little bit more space to show that value. InvoiceID can be a little bit smaller, get rid of that Z that was unintentional and let's make Grand Total bigger, and format it as Currency.

All right, now let's take a look at what we have got. Great! So as we go from record-to-record, we can see all the different Invoices that were created by the samecustomers, so here is all the other Lightningfast one and we can drive what other information that we need. Now here's a one thing that's kind of interesting though is that you'll notice the first record in each of these is the same record that we were on. It's a little bit redundant information. These are Invoices from the samecustomer. We want to kind of remove that from the context. So we can just do one quick update to the relationship, which is common in these self relationships. And that is where we are going to say, if the InvoiceIDs match, we don't want to show it.

So what we will do to add the additional criteria is say show me everything where the CustomerIDs are the same, but where the InvoiceIDs are not. So what that will do is show me all of the Invoices, except for the one that we are actually on. So let's see how that changes. Now we see we just have the one and here the top one is no longer the one that we are on, that's enforced all the way throughout where there are other related Invoices. So FileMaker's Relationship graph allows you to create all sorts of query type relationships between table occurrences. You can even create relationships between two table occurrences that are based on the same table.