Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In an earlier chapter we discussed how to set up relationships in your FileMaker database. It's actually one of the most important components to setting up a relational database system. So if you haven't watched those videos yet, I strongly recommend you go back and watch them before you proceed with this chapter. Just a little note. Once you have established a relationship inside your FileMaker file, then you have the benefit of being able to start working with some of the relationship based functions in FileMaker, and there are a lot of them. We are going to cover each one of them in this chapter. The first one of these functions that we are going to look at is the concept of using related fields.
If you recall from our discussion on relationships, each relationship between two tables in your database must have one parent and one child. Every relationship is going to be one-to -many relationship, so the child record is on the many side of the one-to-many, and the parent is on the one side. So in our example, as you see onscreen, I have got an Invoice detail record and the relationship between customers and invoice is one customer can have many invoices so that customer is the parent, which is on the one side, and the invoice is the child, which is on the many side.
So right now our context is in the child in the relationship. So once we have a relationship like this in place, which we do here in our example file, it's possible to use a relationship to look at data from one table from the perspective of another. So, for example, if I go into the Invoice Detail layout, you will notice that each of these records represents an invoice or an order. So we have got some information that lives in the order level, like the status of the order, the invoice date, the sales person who generated this, their commission, and some other information about subtotals and rates and that type of thing.
We also notice that we have got a field here that's called Customer ID and inside this field, contains the ID of the parent customer. So what that means is that our tables have a relationship set up, but this record actually has a related parent because its foreign key field is populated. And now you think yourself, well this is an order record. So it would kind of make sense that we had some fields that pertain to the customer somewhere on this layout. So, for example, you might want to go to an invoice record and see what company it's for, what their address is, shipping information, that type of thing.
Well, we could easily just go into the Invoice table and define a bunch of field to that are called company name, company address, company city, company state. However, that process wouldn't be efficient because we'd go in and create these fields that already exit in our solution somewhere else, but the real problem is we would create a risk of redundant data. So what if somebody who is writing up an invoice saw that the customer's address had updated. So they will go and update the fields that are in your Invoice table, the ones I would propose that we could have created.
But that's not going to go link back to the parent table. They're just going to be the same address fields. So it would make sense to do that, and that's just a small example of some really big damage and confusion that you can cause if you don't just leave the fields defined within their appropriate table. So the solution to all this is to store one type of information in only one place. In this case, store customer data in the Customer table. This is one of the primary reasons that you would work with the relational database system like FileMaker instead of something like a spreadsheet, for example. So how do we get around this issue if we actually want to have customer information on a layout that's based on Invoice? Well, this is one of the great benefits of having tables related to each other and of course we know that the Customer is related to Invoice.
Since the Invoice is the child in this table, it can pull data from any of the fields in the parent record. So, let me repeat. Because this is the child in a parent-child relationship or one-to-many relationship that means that we can place fields from the parent on this layout. And the reason we can do that is because there is only one parent. So we know exactly what data would show up in that field. I like to refer to this concept as borrowing from a parent. So, if we go back into Layout mode, let me show you how easy it is to do this.
So, let's grab from our toolbar here the Field tool, and we will drag that down to the Email and release, and now instead of choosing a field from the current table, which is the default, notice what happens when I click that dropdown. You see I have got all the other related tables and then the unrelated tables. Now, I can choose any one of these related tables and borrow fields from them, and then the fields that I place on here will display the data that's actually stored back in that parent record. I can't do that for anything that's listed under Unrelated, but we don't have any of those in the solution.
So let's pick Customers, and I will pick AddressEmail. So, now we see the AddressEmail field entered and what's interesting if you look real closely is that we have got two colons that precede the name of the field. That way visually you can see the difference between a native field for this table and a related field. Now, I am going to hold down my Option key in Mac and drag in the field, which is going to allow me create a new field. And you see it's already going to the Customer context. I am going to pick CompanyName, and I am just going to do a couple of more of these again, holding down the Option and dragging, Street, City, State, almost done, and Zip.
So, now you see they all are indicated as related fields and I didn't have to define any of these inside the Invoice table. So, let's go in the Browse mode and take a look at what happens. Save our changes. And now you see what we have got here is data that's coming in, but where is that data coming from? Well, let's go into Customer detail, we can find all Radley & Friends contacts, and we see we have got Boo Radley from Montverde, Arkansas, and if you go into the Invoice detail, you see, yeah Montverde, Arkansas ordered by Boo Radley.
So, now what happens if we change the parent? So if we go in and pick really any one of these, let's say Pernell Inc., now all this information changes. That's because I just changed the foreign key field again. This is all relationship stuff, but if I changed the foreign key field value to another parent ID. That means now these fields are going to be related to another parent record. So now you see them all update. So this is a great way to populate field or display field or even print values in fields without having to define them inside of your table.
So having a relationship between two tables means that you can share data both upstream from the parent, because there will just be the one related parent record and also downstream to the many related records in a child table. This allows you to have one location to store a piece of data and that one piece of data can then be shared throughout the database via these relationships that you are going to set up.
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.