Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.
- Create BookmarkCreate Bookmark
- Create Note or TagCreate Note or Tag
- DownloadDownload
- PrintPrint
Chapter 4. Adding Power to Your Database > Creating and Using Portals
4.3. Creating and Using Portals
Once your tables are related to each another, you can freely display related fields on layouts. For example, say you want the Date Paid and Amount Paid fields from the Payment table to appear on your Lease Agreement layout. You could simply add those fields to the layout, but you'd quickly find a big problem. There's only a single instance of each field, and the point of a related table is to have multiple records from the child table related to the parent table.
Figure 4-9. The Edit Relationship window lets you define everything about how a relationship works. It's divided in half vertically, with one table's information on the left, and the other table's information on the right. For most people, it's easiest to visualize a relationship when the parent table is on the left and the child table is on the right. If your window is flipped (Payment on the left and Lease Agreement on the right), it's because your table occurrences are flipped on the Relationship graph. To change the display, close the Edit Relationship dialog box and then drag the table occurrences to rearrange them. In practice, it doesn't matter which side the tables appear on because relationships are bidirectional (you'll learn what that means on page 228). Just select your options very carefully.
The problem is solved with a portal, which is a layout object that displays multiple records from a related table. Not only can the portal display related records, you can use a portal to create, edit, and delete related records.
4.3.1. Adding a Portal to a Layout
A portal can display as many related records as you want, limited primarily by the size of your layout and height of the portal. As with other layout objects, you can format portals to match your database, using fills and lines the way you would with other drawn objects. Here's how to create a portal on the Lease Agreement layout:
In Layout mode, drag the bottom edge of your Body part to make room for your portal.
Use Figure 4-10 to judge how much space you'll need.
Figure 4-10. In the upper right, you see the Portal tool. The finished portal, along with related fields and their field labels is at lower left. The portal displays information about its data along its lower-left edge. The related table's name (Payment) appears, followed by "[1…12+]." That means that the portal is tall enough to display 12 rows of related records, starting with the first record. The "+" sign means that the portal will show a active scrollbar when it contains more than 12 child records.
Click the Portal tool to select it, and then drag on the layout to create a portal.
As with all drawn objects, it's usually easiest to start in the upper-left corner and drag to the lower right corner where you want the portal to appear. When you release the mouse, the Portal Setup window appears (Figure 4-11).
From the "Show related records from" pop-up menu, choose Payment.
This is the Payment table occurrence you saw when you hooked up the Lease Agreement and Payment tables in the Relationships graph (Section 4.2.4).
Turn on "Show vertical scroll bar".
A scrollbar lets you see more related records in a smaller space.
Turn on "Allow deletion of related portal records".
When this option is turned off, you can't delete Payment records using the portal and the regular Delete command.
Figure 4-11. Control a portal's options using the Portal Setup dialog box. This window appears when you first create a portal. Double-click a portal to view this window. That's how you check or change the portal's options. Of course, you can also use the Inspector to change a portal's size or position and appearance. Use the "Initial row" box to display data from a related table across multiple portals. If you need to display data that's not suitable for a list, like a large container field with a graphic (which should be tall so the graphic is visible), make several tall, single-row portals and place them side by side. Set the first portal to have an initial row of 1, the second portal to an initial row of 2, and so on. See the box on page 150 for more info.
In the "Number of rows" box, type 12, and then click OK.
FileMaker adjusts the portal object you drew to fit the number of rows you specify, and then opens the "Add Fields to Portal" window.
Warning:
If the layout isn't tall enough to fit the adjusted portal, you'll see a warning message that tells you that the layout size needs to be increased. If you click No in that warning dialog box, the portal will stay the height you drew it. When you're done setting up the rest of your portal, you'll have to increase the layout size manually, and then try to change the row display again.
From the list of Available fields on the left, select and move the Date Paid, Payment Amount, and agreementID fields.
Each field appears in the list on the right as you move it. It isn't necessary to add a related table's key field to a portal. In the real world, you don't usually want to see that data. But while you're learning about portals and relationships, it's very helpful to display the key field so you can see how portals work.
Tip:
Pay attention to the way the field name appears in the Available fields list. You're seeing the fully-qualified field name. That is, the field is represented by its table name, followed by a pair of colons, and then the field name itself. It's kind of like you being called by both your first and last names. This nomenclature helps you keep the Lease Agreement and the Payment tables' agreementID fields straight.
Click OK.
The related fields appear in the portal.
A portal has a few notable characteristics in Layout mode. First, no matter how many rows the portal is set to show, you only see one row of fields, and they're always in the portal's first row. Second, you can tell how high each row is because in addition to the border around the portal, there are lines between each row (unless you've turned lines off for the portal). Notice that the fields fit precisely in that top row. In fact, FileMaker used the automatic format of your fields to figure out how many rows it could fit in the space you drew with the portal tool.
The Payment fields are evenly divided within the width of your portal and their formats probably don't match the rest of the fields on your layout. You may need to adjust the fields' widths, change their text alignment and create field labels if you want your layout to match Figure 4-10.
4.3.2. Resizing and Moving a Portal
Just like any other layout object, a portal shows selection handles when you click to select it. But its selection handles may not be at the edges of the portal as you'd expect. They're at the bottom of the portal's first row, and not at the bottom edge of the portal. If you drag either of the portal's bottom selection handles downward, you change the row height and not the number of rows the portal will display. That's useful where you want to show lots of data in a portal and a single row of fields for each related record won't get the job done. You can change the width of a portal by dragging a handle or by using the Width boxes on the Inspector's Position tab.
If you move a portal, make sure you select the fields inside the portal, too. Because if the portal and its fields move out of sync and the fields overlap the portal's top row borders even a little bit you could get display problems—the fields may not show up at all, even when there are related records. If you delete a portal without deleting the related fields, they still show data (if there are related records), but you see data from just the first related record because without a portal, FileMaker can only show you one related record.
4.3.3. Context
Back in Browse mode, the portal appears, but there's no data in it. That's because the Payment table doesn't have any records yet. If you're thinking about choosing the New Record command, don't act on that thought, because it won't work. At least it won't create a new Payment record. It will continue to work as it always has, by creating a new Lease Agreement record. To understand why, backtrack just a bit to take a look at your layout's setup. Switch to Layout mode, and then choose Layouts→Layout Setup (Figure 4-12).
Up To Speed: Power to the PortalOn Section 4.3.1, you learned a little about how the Portal Setup dialog box works. Now it's time to dig a little deeper. To see the settings for your portal, in Layout mode, select it, and then choose Format→Portal Setup. (You can double-click the portal to get there, too.) Here's how some of the options break down:
In addition to the options in the Portal Setup dialog box, portals have other features you may find useful:
|
Figure 4-12. All layouts are tied to one, and only one, table occurrence. Fittingly, the Lease Agreement layout shows records from the Lease Agreement table occurrence. Why all this harping on the difference between a table and a table occurrence? Because you can create multiple instances of a table on your Relationships graph—that's what a table occurrence is—an instance of a table. When you work with your data (specifying fields, creating portals, writing calculations, and other tasks), you need to use the table occurrence for the current context.
When you created a portal back on Section 4.3.1, you had to specify a table occurrence for the portal to know which records to show. But the Lease Agreement table was created for you when you created the database way back on Section 3.1. FileMaker created the Lease Agreement table, along with a matching layout and table occurrence, because you need all that stuff to get started and it doesn't want to bother you with details while you're being creative. But when you start to create layouts from scratch, you need to tell each new layout which table occurrence to draw its records from.
This concept of where you are and what records you're viewing, called context, is fundamental to many aspects of your database. The context of the Lease Agreement layout is the Lease Agreement table occurrence. In turn, the Lease Agreement layout contains a portal whose context is the Payment table occurrence. Keep context in mind as you're reading the next section, which covers the things you can do with related records using a portal.
4.3.4. Creating Records Through a Portal
Once you've set up a portal on a layout, you can create records without going all the way back to the actual table. On a Lease Agreement record, in Browse mode, click in any field. All the fields, including the ones in the portal, show the dotted line that indicates they're active. You can now tab into the Payment portal's fields, just as if they were a part of the Lease Agreement table. The fields are active even though there aren't any related records because when you defined the relationship between the Lease Agreement and Payment tables, you chose the option that allows related records to be created (Section 4.2.4). View your related table by choosing Window→New Window. A new window appears that's a duplicate of the original. Move the new window over to the side so you can see both windows, and then switch the new window to the Payment layout (Figure 4-13).
Figure 4-13. In the window on the left, the Omar Little record is active, and you can see that the portal shows field boundaries as if there's a new record. But in the window on the right, you can see that the Payment table doesn't have any records yet. Keep your screen set up like this for the next tutorials, so you can watch how and when related records are created, edited, and deleted. This is also a good troubleshooting tip when something's wrong with a relationship and you're trying to figure out how to fix it.
In your original window, in Browse mode, click in the Payment portal's Date Paid field, type a date, and then press Tab. If you've added a calendar pop-up to the field, select a date from the calendar.
As soon as the insertion point moves to the next field, you'll see the record appear in the Payment window. Even though you didn't enter it, the new record's agreementID field now has a value, and it matches the value in the Lease Agreement::agreementID field.
Type a number in the Payment Amount field, and then press Tab again.
The agreementID field in the portal is active. Because it's a foreign key, it's not set up to prohibit data entry. So you can change the ID. But if you do, the Payment record won't be related to the Lease Agreement record when you commit the related record. It might even be related to the wrong parent record now. Luckily you have the Payment window open, and you can just change the key back to match the value in the parent record you're viewing. When you commit the Payment record, it will show back up in the portal.
Use the portal to create a few more payment records just to watch how the process works behind the scenes. Notice how the Tab key travels through the rows in the portal, much like a spreadsheet.
Note:
Normally, you wouldn't put a foreign key field in a portal because if the value is changed, the child record disappears from the portal; it's no longer related to the parent record you're viewing. Plus it's confusing to users, since the value in every record is, as it should be, exactly the same. Still, viewing an ID field in a portal, along with a second window opened to a layout that has the context of the related records, is a good way to learn about relationships and to troubleshoot.
4.3.5. Editing Records Through a Portal
You edit any related record by clicking in the field you want to change, and then typing the new info. Tab to the next row, or if you're on the last row in the portal, click into blank space on the Lease Agreement record to commit the changes (see the box on Commitment, or On the Record).
4.3.5.1. Deleting records through a portal
Unlike the New Record command, the Delete Record command can work on a portal—if you've formatted the portal to allow related record deletion (as you did when you set up the portal on Section 4.3.1). But you have to select a portal row first to set up the context so FileMaker knows which related record you want to delete.
Select the portal row you want to delete, but without clicking in any of the portal's fields.
Click the portal row itself (point between the fields in the portal, and then click). Figure 4-14 shows what selecting a portal without clicking in a field looks like.
Figure 4-14. The Payment record for May 4, 2010 is highlighted. No field in the portal row is active, but a highlight shows on the whole row. Selecting a row this way sets the context for a portal row delete. However, you can't enter or edit data in a portal row that's highlighted this way, because the insertion point isn't in a specific field.
The Delete Related Record Warning appears (Figure 4-15).
Click Delete.
The related record is deleted.
Figure 4-15. Because you can't undo a record deletion, FileMaker gives you a chance to change your mind. Click Cancel to keep the record or Delete if you're sure it's the one you mean to delete.
If you're in a field on the portal row when you choose the command, then FileMaker asks you which record you want to delete: the master record or the related record (Figure 4-16). The terms "master" and "related" are FileMaker-speak for "parent" and "child." If you're not on a portal row at all (neither an active field or a portal row highlight), the Delete Record command works exactly like it does on a layout that has no portals.
Figure 4-16. Just clicking in a field on a portal row doesn't give FileMaker enough information about context, so it asks you which record you want to delete. Once you've made a selection (other than Cancel), you'll get a second window asking if you're sure you want to delete the record. It's easier to highlight the portal row than to interact with two windows.
4.3.6. Performing Finds with Related Data
You can search in related fields just as easily as you can search in the "local" table's fields. So if you wanted to find all Lease Agreements with June 2010 records for instance, just switch to Find mode, click in the Payment portal's Date Paid field, enter your search criteria, and then perform the find (Section 1.5). You get a found set of all the Lease Agreement records that have related records dated June 2010. Each Lease Agreement record will still show all its related records, and not just the ones that match your search criteria. As you flip through your found set, you might think that some records shouldn't be in the found set. But remember, not all the related records may be showing in a portal. So if you think a record showed up when it shouldn't have, scroll down in the portal. You'll find the June 2010 Payment record in there somewhere.
But if what you wanted was a list of only the June 2010 payment records, you have to search using a layout that's set to show records from the Payment table occurrence. That way, your found set will contain just the records that match.
