Relational Databases

The essence of a relational database is its multiple tables. But every database you've worked with up to this point has had just one table. Now you need to learn how multiple tables affect the FileMaker concepts you're familiar with: Editing records, finding records, and building layouts.

7.4.1. One Table Occurrence, One Found Set

The most important thing to understand when using a multi-table database is that each layout sees the entire database from a different perspective. You'll want to do a little exploration to see this concept in action. A layout is attached to an occurrence on the graph, and that's how it sees the world. This means when you're looking at a record on the Customers layout, you're seeing a customer. If you switch to table view (View View as Table), youll see a list of customers. You won't see payments at all. To see those, you need to use the Layout pop-up menu (or choose View images/U2192.jpg border=0> Go to Layout images/U2192.jpg border=0> Payments) to switch to the Payments layout.

Since each table holds different data, the concept of a found set changes a little as well. Whenever you initiate a Find, the layout you're on determines which table FileMaker searches. Your newfound set is associated with that table occurrence too. Just like when you had only a single table, the found set stays the same until you perform another find, or you tell FileMaker to show you all the records for that table occurrence (Records images/U2192.jpg border=0> Show all records).

For example, if you find the six customers from Texas, then switch to the Expenses layout, you won't have six records in your found set anymore. Instead you have a separate Expenses found set. Switch back to Customers and you'll see the six Texans again. FileMaker remembers one found set for each table occurrence. It also remembers the current record for each table occurrence, so if you switch to a different layout, then come back, you'll still be on the same record that was active when you left that layout.

Of course you can have more than one layout attached to the same table occurrenceCustomer List and Customer Detail, for example. The found set and current record are associated with the table occurrence, not the layout, so a find on the Customer List layout will affect the found set on the Customer Detail layout.

---

Tip: If you want more than one found set or current record in the same table occurrence, you can use multiple windows, just like you learned in Part 1.

---

If you want to see two kinds of records side by side, you can create a new window (Window images/U2192.jpg border=0> New Window), then switch one of them to a different layout.

7.4.2. Viewing Related Fields

Having each table occurrence completely segregated from the rest on its own layout is a good starting place, because you will probably want separate lists of all your entities, but it's hardly ideal for a fully functional database. After all, if you wanted to view everything separately you could have created individual databases and saved yourself all the trouble of making an ER diagram.

So think of the layouts FileMaker made for you as starting points. It's time to put those relationships to work. For starters, you'd like to show the customer name when you view a Job record. But the Job table doesn't have a customer name field. Instead, you'll use the fields from the Customer table and let FileMaker find the correct related data using the relationships you defined.

1. In the Jobs layout, choose View Layout Mode.

   In the status area, use the Field tool and drag a new field onto the layout, somewhere below the existing fields.

   As soon as you release the mouse button, FileMaker asks you what field you want.

2. In the Specify Field dialog box, click the Table Occurrence pop-up menu (see Figure 7-30).

   Here's where you venture into unfamiliar waters. This menu lets you pick any table occurrence on your graph.

   

   Figure 7-30. The Specify Field window has always had a Table Occurrence pop-up menu; you've just been told to ignore it until now. This menu shows a list of all the table occurrences on the graph. FileMaker is smart enough to know which ones are related from the current layout's perspective. It breaks the list into two groups: related tables and unrelated tables. Right now you don't have any unrelated tables because the entire graph is connected in one group.

    
3. Choose Customers from the pop-up menu, then, from the field list, select the First Name field. Click OK

   FileMaker adds the Customers::First Name field to the layout. It just says "::First Name" on the layout, but it does belong to the Customers table occurrence.

   ---

   Tip: If you forget which table occurrence a field comes from, just double-click it. The Field/Control dialog box reappears, and you can read the source table from the "Display data from" bit. Just make sure you dismiss the dialog box without making any changes. To be safe, click Cancel.

   ---

4. Change the field label FileMaker created for the new field to Customer First Name. Then, add the Customers::Last Name field to the layout too.

   Change its name, too, so you can remember what these fields are for.

When you're finished, switch back to Browse mode, saving your layout changes when prompted.

7.4.3. Adding or Editing Data

In Browse mode, the new field doesn't look all that impressive yet. That's because you don't have any data in your database. You'll really see the magic of relationships once you add some, starting with some customers. Open your database and go to the Customers layout.

1. If the status area shows there are no records, choose Records New Record.

   first, you will need a new Customer record.

2. Name this customer Kesuke Miyagi by filling out the First Name and Last Name fields. Then, create a second customer record with the name Daniel LaRusso.

   Notice that FileMaker has already filled in the Customer IDs for youC00001 and C00002. Time to add a job or two.

3. Switch to the Jobs layout, and, if necessary, create a new record. In the Name field, enter Paint the Fence.

   Now you have one job record, but it isn't attached to a customer yet, so the Customer First Name field is empty.

   ---

   Note: If you try clicking in the Customer First Name field right now, you'll get another surprise: FileMaker won't let you click in the field. It looks like a field, it smells like a field, but it doesn't work like a field. You're seeing the First Name field from the related Customer record, but there is no related customer record. No record, no field: No click.

   ---

4. In the Customer ID field, type C00001, and then press Tab.

   Presto! As soon as you leave the Customer ID field, FileMaker goes to work finding the correct Customer so it can show you his name. Like magic.

7.4.4. Editing Related Data

To see how editing works in a relational database, add another Job record. Using the same steps as above, name it "Wax the Car," and attach it to Customer C00001 as well. It should then show the same customer name. Now try this:

1. Change the value in the Customer First Name field to Mister.

   Now the Wax the Car job is attached to "Mister Miyagi." But who's the Paint the Fence job attached to? Kesuke Miyagi or Mister Miyagi?

2. Switch to the first Job record using the Book icon in the status area.

   Drum roll… Paint the Fence is Mister Miyagi's too.

3. Switch to the Customers layout and then switch to the first record.

   Ta da! The customer record itself also now says Mister Miyagi.

If you don't think that's cool, you need your geek level adjusted.

Now you've seen a relational database in action. From a Job record, you can view data from the related Customer record. You can even edit that customer data directly from the Job record. The power of a relational database is all about working with related data like this.

---

Tip: If you want to protect your data so it doesn't get changed out of context, and set the related field's behavior so that it can't be entered in Browse mode. Switch to Layout mode and choose Format Field Behavior (Section 6.3).

---

1. In the Customer layout, switch to Layout mode. Drag the body part a few inches down.

   The Customers layout is a full one. You'll need more room for your portal.

2. Click the Portal tool in the status area.

   You can see what it looks like in Figure 7-31.

   

   Figure 7-31. The Portal tool is the only Layout mode tool you've never used. It works like the Shape and Button tools. Select it first, and then drag a rectangle on the layout. FileMaker creates a new portal inside that rectangle. You can also use this picture as a guide to show how much extra space you should make for your portal to live in.

    
3. Drag a large rectangle in the space below the Customer fields (you can use Figure 7-31 as a guide, but don't worry too much about the size; you can always resize the portal later).

   When you release the mouse, FileMaker displays the Portal Setup dialog box. It's displayed in Figure 7-32.

4. Make sure the "Show related records from" pop-up menu is set to Jobs.

   You're telling FileMaker to show data from the Jobs table occurrencein this case all the Jobs for the customer.

5. Turn on the "Sort portal records" checkbox. In the Sort dialog box that appears, add the Name field to the Sort Order list, and then click OK.

   When FileMaker shows Job records for a customer, they'll be sorted alphabetically by job name.

   

   Figure 7-32. In the Portal Setup dialog box, you can choose which table you want to show records from and how those records should be sorted. You can also decide which related records to show and adjust the look of the portal. See the box on Section 7.4.4.2 for details on all these options.

    
6. Turn on "Allow deletion of portal records.".

   With this option turned on, you can delete records in the Jobs table right from the Customers layout, saving yourself a trip to the Layout pop-up menu.

7. Turn on "Show vertical scroll bar.".

   A portal has a fixed size on the layout. If you have more records than fit in the allotted space, two things can happen. It can either ignore the additional related records, or you can put a scroll bar on the portal. With a scroll bar, you'll be able to view all the related jobs, no matter how many exist.

8. Turn on "Reset scroll bar when exiting record."

   If you've scrolled way down in a lengthy list of related records, the first records don't show up anymore, naturally. By selecting this option, you're telling the portal to go back to the first record anytime you click out of (or exit) a portal record.

   ---

   Note: Some people really hate seeing the portal pop back up to show the first record. They want the portal to stay scrolled even if they click out of that related record. To appease these folks, leave the option turned off. But be ready to reassure these malcontents that sometimes FileMaker has no choice but to redraw the data in the portal, and when that happens, the portal pops back to related record number one. That's just how it is.

   ---

9. Click OK.

   The Add Fields to Portal window take the Portal Setup window's place. From here, you get to pick which fields from the job table should be displayed in the portal.

10. From the Available Field list, select the Name field, and then click Move.

    FileMaker adds Jobs::Name to the Included fields list. Click OK again.

Your layout now has a portal. It looks just like the one in Figure 7-33.

Figure 7-33. Portals are much more involved than any other layout object. They live in a rectangle on the layout like any other object, but the have intricate parts within them. Every portal is made up of rows. The more rows a portal shows, the more related records you can see at one time. A row can have any other layout object inside it. In this example, the portal row has just one field. Each portal also shows what table occurrence it's associated with, and which rows it shows. Finally, if you ask for it, a portal has a scroll bar so you can scroll through lots of related records.

 

7.4.4.2. Editing fields through a portal

Switch to Browse mode to see your portal in action. If you look at Mister Miyagi's record, you'll see both his jobs listed in the portal. Like any related field, you can click into either Job Name field and edit the data therein. When you do, the Job record itself is updated.

You can also delete records through the portal. Click the Paint the Fence row in the portal. When you do, you'll either enter the field itself, or select the portal row. Figure 7-34 shows the difference.

When you're on a portal row, the Delete Record command takes on new meaning. If you have an entire portal row selected, FileMaker assumes you want to delete the related recordthe one you've selectednot the record you're sitting on. If you're in a field on the portal row when you choose the command, FileMaker instead asks you which record you want to delete: The Master record or the Related record. Figure 7-35 shows each message.

Figure 7-34. When you click a portal row, one of two things happens. If you click onto an enterable field, you'll go right into that field. You would say you're on the first portal row, and in the Jobs::Name field. If you click in a portal row somewhere other than on a field, you select the portal row insteadthe entire row highlights. You're still on the first portal row, but you're not in any field this time.

Figure 7-35. Top: If you select a portal row, the Delete Record command deletes the related record.
Bottom: If you're in a field on the portal row when you choose the command, you get this message instead. This time you get to pick which record is deleted. Click Master to delete the Customer record, and Related to delete the Job record.

If you're not on a portal row, the Delete Record command works exactly like it does on a layout that has no portals.

You've covered a lot of ground in this chapter. You now have a full-fledged relational database, and a good idea of how it works. But manually typing arcane customer numbers into foreign key fields and bouncing from layout to layout to add a job is probably not your idea of efficiency. In the next chapter you'll learn how to harness all the power of the FileMaker features you've learned so far: Value lists, field formats, buttons, and additional relationship options all combine to make data entry a breeze, even in a complex database with several tables.

UP TO SPEED
Portal Power

In Figure 7-32, 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, select it in Layout mode and choose Format Portal. (A double-click on the portal itself will get you there, too.) Portals work a lot like repeating fields. The main differences are:

• Portals can have a scroll bar. Got 326 items on that invoice? No problem.
• Each row in a portal can hold more than one field. In addition to the Job Name, you could show the Job Start Date if you had one. Adding objects to a portal is a breeze: Just drag them into the first row in Layout mode. When you add a field, FileMaker will show its correct value on each portal row. You can also add lines, shapes, pictures, and even buttons. You'll do some of this in the next chapter and you'll learn how to troubleshoot when the data isn't showing up correctly.

In addition to these differences, portals have more options affecting the way they look and work. As you've already seen, you can tell FileMaker to sort the records in a portal. Whenever you view the Customers layout, for example, the job list is sorted alphabetically.

This has no effect on the sort order of the records on the Jobs layout. It just sorts the Jobs portal in the Customers layout. You can even put another Jobs portal on a different layout and set it to use a different sort order.

You can also assign an "Alternate background fill" to a portal. This works just like its counterpart in the Part Setup dialog box. When you turn it on, every even-numbered portal row will have a different background color and pattern. You can make every other row green, for example. The odd-numbered rows will have whatever background color you assign to the portal itself on the layout.

Finally, portals have the same partitioning power as repeating fields. Normally, a portal starts with the first related record (which record that is, exactly, depends on the sort order assigned to the portal). If you change the "Initial row" value, though, the portal will skip some rows. For example, if you put 5 in this text box, the portal will show rows five through 14 instead of records one through nine.

If it suits your needs, you can put the same portal on your layout more than once, and give it different initial rows. Your layout could show the first five jobs in one column, and jobs six through ten in a second column, for instance.