Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In FileMaker Pro 11 Essential Training, Cris Ippolite demonstrates the principal features and functions of this popular database software, including creating tables and relationships, managing fields and records, and working with layouts. The course shows FileMaker developers how to find, sort, and share data as well as how to create reports, calculations, and scripts. It also covers brand new features in FileMaker Pro 11 such as the Inspector tool, charting, and portal filtering. Exercise files accompany the course.
So far in this chapter, we've been talking about relationships. We've discussed using relationships between the parent and in the child table in order to borrow related fields. If you haven't watched that movie yet, I would recommend that you go back and watch that movie because this one is going to be kind of continuation of that concept. So, we've looked at how we can place related fields on a layout, so that a child table can borrow values from another related parent table, thus eliminating the need for redundant data in your database. This, of course is one of the true values of having a related database system.
However, we've only been looking at using related fields from the context of the child table. So, for example, in the Invoice Detail, we've got all these related fields added to our layout. Well, what about if we are on the parent? Can we borrow related fields from the child? Well, let's see what happens when we use related fields from the context of the parent. If you open up our Exercise File and navigate here to the Customer Detail. Let's go into Layout mode. You'll notice that we have a Tab control here, and we are going to click on the one called Financial.
So, let's try, applying the same method for using related fields on this layout, as we had in the previous movie. But remember this time we are the parent or the one side of the one-to-many. Before, we're on the child or the many side of the one-to-many. So, if go on and insert a field, and we get the Specify Fields Window. You see we can choose from many related tables, and we are going to choose Invoices because that's the child in this relationship. Since Customers has a relationship to Invoices, we are going to select _pkInvoiceID.
If you recall from when we define these fields, this is the naming convention that indicates the unique serialized value in the Invoice table. So, what we are going to get is a unique Invoice ID. So, we hit OK and let's move this one down over here. Now, let's see what happens when we go into Browse mode. Save our changes and go into Financial. You see that we do actually see a value in this field. So, what does this mean? Well, if you think of it, this field value isn't meaningful.
I say it's not meaningful because there are potentially many related values in the child table. So, in English that means that this contact at Radley & Friends could potentially have hundreds of different invoices that they are related to. But since the FileMaker field can only display this one piece of data, all we are seeing is one of them and that really is born out of the fact that we are in the parent context. So we are in the one, and it's related to many and a field can only show one. So, really a normal field is not the right mechanism for us to be able to show related data because of the context that we are in right now.
This is an important concept. So, what we really need is a special type of a field or maybe a different kind of a layout object that allows us to see many different iterations of a field, possibly one for every related value that we have in the child table. Well, we actually do have a special kind of layout object. If we go into Layout mode and back into the Financial tab, I'll show you. If we go into the Layout Tools, you see right here we've got a tool called the Portal tool.
When I click on it, looks like many other layout tools. I can drag an area on screen and release and up pops something called the Portal Setup. Now, right away you can see as we are looking at it we've got multiple rows. So, really it's kind of like having one related field, but the field will repeat itself for every related record that it has. So, that way if we have 10 different related invoices, each one of them can have their own row. So, this is perfect.
This just what we need. We will take a look at how we set these portals up though first. First, we are going to choose related records from and in this case, we have to choose from whatever table the Customer table is related to. The reason is because we are in the context of Customer. This layout is based on Customer. As you can see here Current Table ("Customer"). So, we were looking to display values from the child table or the Invoice table, so we'll keep that selected. You see that we've got a couple of options here.
The first one is the Sort portal records, which means when we are on this layout, viewing related records in this portal, how do we want them to sort? Well, let's click that, and we see the familiar Sort Records dialog. So, let's say we want to view them by InvoiceDate. So, InvoiceDate in ascending order, meaning the very first invoice to the last one from top to bottom and we hit OK. Now, this next option we are going to get back to this. This is a pretty big deal. This is new in FileMaker 11, but we'll get back to that one.
A couple of these. Do we want users to be able to delete portal records? Well, what that means is that if you click on one of these rows and hit your Delete key then you're going to actually delete the invoice record in the Invoice table. Sometimes that make sense to do that, but we are going to leave that off for right now. How about we do a little cosmetic thing here. Show a vertical scrollbar. That way we can have 11 rows, but if there's 30, then they can just scroll. So, that way we don't have to make that thing any bigger. The way that we determine how many rows it is initially is by filling in this information here.
The initial row is going to be the first related record, and it's going to show us 11 total related records. If you want to get kind of fancy with it, you can have one portal that shows invoices, related invoices 1-10, and another one that shows 11-20, so on and so on. But we'll just keep it standard for right now. Also, there's a cosmetic thing here. You can choose to alternate the background fill between rows to make it easier to read. Well, let's hit OK and now the next window is asking us what related fields do we want to put on there? Because, for example, we already tried to put the InvoiceID on there, but it didn't really seem to make sense.
So, let's put that in our portal now. Let's take the InvoiceID. And what else might be helpful for us to see? How about the InvoiceDate and maybe InvoiceTotal and how about ShipmentStatus? So, now we've chosen all the fields from the invoice that we want to see in a list. It's going to create a little List View for us here. So, all those fields are going to be put into this first row and then they'll be iterated for every related record. When we are done, we hit OK. Now, you see that we've got only the first row highlighted here.
What's important is that you'll notice that we don't have to put these fields in every row. What's nice about setting up a portal is you can pick the related field that you want to see in a portal, but you only have to place them on that first white row there. That's sort of like your staging area. But you have to make sure that the fields fit with in that row. FileMaker automatically puts those in there for you. But if yours are even nudged up just a little bit like that, it's not to going to work properly. So, you got to be careful about your sizing there. So, now that we have our portal on the layout, let's go into Browse mode,and we can save our changes and click over to Financial.
If we scroll through, we can see all the different related invoice records. We notice, we got a little squirreliness with the line there, but if we go back into Layout mode and hit Financial, we'll see that that's because I moved this field. So, let's go in here and Shift+Select all these, go into our Position and lock them. What's one way we know to get them all in line. Well, select the one that's not in line and of course choose Align. So, here's a perfect example of making sure that everything is on the line properly.
I am going to Browse and save our changes and back into Financial. Now, we see all the different related invoices. So this record here has several of them. If we go to the next record, we see it only has one related. This has two related and so on and so on. So, these portals will update as you navigate to a different parent record. So, this becomes very helpful tool and if while we are looking at this record another user who's logged into this database goes in and creates a new invoice for this contact, it'll automatically show up in this portal the second that they commit the record.
So, we go back into Layout mode. There is something that's new in FileMaker 11 that I wanted to show you. If we go back in to the portal and double-click on the portal, that'll bring up our Portal Setup. You'll notice that we have this option to Filter portal records. Well, it has a Specify button, so we know when we click on it it's going to bring up the Specify Calculation dialog. So, here we are going to put in a calculation that's going to be Boolean, which means something is either true or false.
So, any record that is true based on this condition we are going to enter is going to show up in the portal, but any records that's false will not. So, it's just a way that we can visually filter this portal. So, let's say in this case we want to say anything that's shipped we don't want to have show up in here, because otherwise this list can get really long. So, let's say ShipmentStatus="Shipped". So, this way we only see the ones that are shipped. Now, I hit OK and OK again and back in Browse mode.
Now, we see none of these records have shipped, but here we have three records that have shipped. So, it's a same portal, but these are the only ones that we see that have shipped in this group. We can go in and maybe even make the inverse, if we'd like, to filter portal records. How about we say anything that does not equal shipped, so anything but shipped. Hit Save, and now we see all of these are processing. You see that status there,. Processing, Processing.
So, basically nothing that has the status of Shipped that will show up here. So, the Portal filter is brand new in 11. It's a great feature. But keep in mind that if you have the same portal on another layout, it's not going to affect the relationship. It's just going to affect this particular portal. It's a nice option for adding multiple portals to the same layout, if you want to. One of them can show all of the related invoices. The one next to it, or maybe another tab let's say, can show all of the shipped ones. Another one can show all the processing ones and so on. So, understanding portals is really going to be one of the layout tools that you'll have to have in your FileMaker toolbelt so to speak that will help you most when you're designing layouts in a parent table.
It provides extremely useful visibility of child records from the context of the parent. It's going to be something that you use on almost every database solution that you create.
Find answers to the most frequently asked questions about FileMaker Pro 11 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.