Join Cris Ippolite for an in-depth discussion in this video Understanding portals, part of Relational Database Design with FileMaker Pro.
- One of the primary benefits of relating tables together is the ability to share live data between them. We've already looked at how we can place related fields on a layout so that a child table can borrow values from any related parent table. This of course eliminates the need for redundant data inside your database. But thus far, we've only been looking at using related fields on a layout that has the context of the child table in a relationship. If we look at the relationships that we have in our database by going under the File, Manage, Database and choosing the Relationships tab, we'll see that here's the Orders table.
That's the table that this layout is based off of and we've placed some related fields that we're borrowing from the parent table of Customers, but we also see that Orders has a relationship down to OrderLineItems, so what we want to do now is put some related fields from the OrderLineItems table on here and you notice that we've already got a section for this in our layout, we've got some text headers for the different columns, Product ID, Product Name, Part Number and so on, so why don't we place some related fields from OrderLineItems below each one of those columns? I'll go under Insert, to Field, and I want to make sure that I'm choosing from the drop-down list here, OrderLineItem.
We see that that's selected. And so now what I'm doing is I'm choosing from fields that are inside the child table. So Orders is the parent to OrderLineItems, so I'm picking a field, let's say the ID field. And we don't have to choose the label. Make that a little bit smaller. And let's do one more underneath Product Name. Insert Field, and we'll go to Product Name. So now, just like we've got related fields from the parent table, to Orders, which is Customers, we've now got related fields from the child table to Orders, which is OrderLineItems.
So let's go into Browse Mode and see what we get. Well we've got some data showing up but it's only one product. So what happens if we have more than one line item on this order? Should we add some new fields? Copy the same field? Actually no, neither of those will work. The problem here is, what we're seeing is that the order table is the one or the parent in the relationship between Orders and OrderLineItems, so that means there could potentially be many related line item records to this order. And since the Filemaker field can only display one value at a time, we won't be able to use a related field.
It would always appear that we only have one related record and that's just simply not reliable. Instead, what we could really use is some type of a field or a layout object that would display a dynamic list of related child records regardless of how many there were. Well, we actually do have a special kind of layout object in Filemaker, and it's called a Portal. Let's go into Layout Mode and I'll show you how we use a Portal. First, let's get rid of our first attempt. Now look at our tools in the Status toolbar on the top of the page, you'll notice that there's one right here that's called the Portal tool.
And when you click on this option, like many other layout objects, you'll see that our cursor turns to a crosshair. So what I want to do is draw a box about this size to make sure that it fills up all the area below those different column headers, so when I release it, we see a window pop up, that's called the Portal Setup. We also see that in the layout, we've got what appeared to be multiple rows. So really, this kinda like having one related field but the field will then repeat itself for every related record that it has.
This is really what we're looking for, instead of using related field, we're gonna use this Portal layout object. So that way if we have 10 different related line items on one order, this will show 10 different records. But if we only have two on another order, it'll only show two different records. This is exactly what we're looking for. Now inside the Portal Setup, we do have some decisions to make. You'll notice a drop down that's very similar to the one that you see when you're inserting a field. This shows you the related tables that you can choose from because a portal is only used in related tables and I should clarify this point, you will only use a portal on a layout that has the context of the parent in a relationship, because what it's used to do is show child records inside the context of that parent layout.
Since we're in a layout that's based on the Order table, we know that the OrderLineItem is a child to the Order table, so we'll select that option. We also have a couple of other Portal Setup options that we'll get to in later movies, but I'm gonna show one here that's called the Show vertical scroll bar option. What that will do is show a vertical scroll bar on the right hand side of our portal. So even though we've only got four different rows here, if we end up with 30, the user can just scroll through. That way we don't have to take up all that space on the layout. So let's hit OK, and now the next window is asking us what related fields we want to put on there.
Since each of these rows in the Portal is going to be like a related record, what we're really doing is choosing the fields that are gonna show up on each record. And we're gonna pick fields that are gonna match the column headers that are already on the layout. So first we've got Product ID and Product Name. Now, Product ID and Product Name are actually fields that come from the Product table. This Portal row will have the context of OrderLineItem which is related to Products, so therefore, we can borrow from the parent in that case as well. We'll see by simply clicking down to the Products option, allows us to choose from all the related Product fields.
So let's choose the Product ID first and click Move and move that over. These are all the fields that are gonna appear on this Portal row, and let's also choose Product Name. And so the reason this will work, the Portal row has the context of OrderLineItem but any one OrderLineItem record only has one related Product record as a parent. So that way, we'll only get one piece of data in each one of these fields, and that's exactly what we're looking for. So we've got Part Number, moving Part Number over, and a couple more, Taxables, same thing here.
But now Quantity is specific to the OrderLineItem record so let's go back to OrderLineItem, and choose Quantity. You can see by the prefixes that we've got Products, Products, Products, Products and then OrderLineItem. These are the tables where we've defined those different fields. And we'll finish up by adding Price, and Extended Price which both come from the OrderLineItems. So we've got Products fields and OrderLineItem fields. The Products fields are being borrowed from the parent of the OrderLineItem and the OrderLineItem values are coming from the actual OrderLineItem record that we're seeing inside the Portal.
So now when we hit OK, we notice that we've got a vertical scroll bar, and we notice that only the first row, which was the only white row, the rest of them are grey in color when we're looking at them, we notice that that row is populated with all the related fields. You only have to fit the related fields into the first row of a Portal for them to show up and then when look at it in Browse Mode it will repeat itself for all the different related values. You also need to make sure to keep your fields within that first row because if they bleed over into another row or outside of the Portal, they're no longer considered part of the Portal and that can mess up your layout.
If you need more space, you can just grab one of the handles and make the Portal larger, but we seem to have the right size here. Let's go into Browse Mode, saving our changes and take a look at what we've got. Now, there's no magic here, the exercise file that I'm using happens to already have related data showing up in it so that we can display the related values properly. So what we see here as we go through each order is that the amount of related records changes. Some orders don't have any related records. Some only have four or five, and then some have many of them, which we can see by scrolling through the Portal using the vertical scroll bar.
So as you go from record to record, all related child records will appear dynamically. Portals will help you most when you're designing layouts that are based on a parent table in a relationship. It provides extremely useful visibility of child records from the context of a parent and it's gonna be something that you're gonna use on almost every database solution that you create.
The course applies to versions of FileMaker Pro from 7 through the most current version.
- Reviewing relationships types
- Diagramming relationships
- Resolving many-to-many relationships
- Determining which tables need key fields
- Defining tables in FileMaker Pro
- Using the Relationship Graph
- Using multiple match fields in one relationship
- Using global fields to filter portals
- Creating self relationships
- Creating aggregate functions using relationships
Skill Level Intermediate
Q: The exercise files for this course do not work on a Mac. It downloads executable files. Can you provide a workaround?
A: The files provided for this course were made pre-FileMaker Pro 12. The system does not recognize the .fp7 files immediately as something that can be opened with FileMaker Pro 12 and instead shows them as Unix Executable Files.<br /><br />The solution is quite simple. Just double-click the file, at which point the Mac will prompt you to choose an application to open the<br />file with. Select FileMaker Pro 12 and it will then convert the file from a .fp7 file into the new FileMaker Pro 12 file format, which is .fmp12.
Q: This course was updated on 5/12/2015. What changed?
A: We moved a video from the "Using Relationships" chapter of <em>FileMaker Pro 13 Essential Training</em> into this course, where it will remain evergreen.