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.
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.
Here's where you venture into unfamiliar waters. This menu lets you pick any table occurrence on your graph.
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.
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.
first, you will need a new Customer record.
Notice that FileMaker has already filled in the Customer IDs for youC00001 and C00002. Time to add a job or two.
Now you have one job record, but it isn't attached to a customer yet, so the Customer First Name field is empty.
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:
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?
Drum roll… Paint the Fence is Mister Miyagi's too.
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).
The Customers layout is a full one. You'll need more room for your portal.
You can see what it looks like in Figure 7-31.
When you release the mouse, FileMaker displays the Portal Setup dialog box. It's displayed in Figure 7-32.
You're telling FileMaker to show data from the Jobs table occurrencein this case all the Jobs for the customer.
When FileMaker shows Job records for a customer, they'll be sorted alphabetically by job name.
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.
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.
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.
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.
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.
126.96.36.199. 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.
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
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:
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.
Part I: Introduction to FileMaker Pro
Your First Database
Organizing and Editing Records
Building a New Database
Part II: Layout Basics
Advanced Layouts and Reports
Part III: Multiple Tables and Relationships
Multiple Tables and Relationships
Advanced Relationship Techniques
Part IV: Calculations
Introduction to Calculations
Calculations and Data Types
Part V: Scripting
Part VI: Security and Integration
Exporting and Importing
Sharing Your Database
Part VII: Appendixes
Appendix A. Getting Help