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 a Related Table
4.2. Creating a Related Table
Now that you've got the basic information stored for your leases, you realize that you can centralize your data if you record rent payments in the same database as the Lease Documents. Your first thought might be to create a field or two (Date Paid and Amount Paid) to record each payment, but you quickly realize that since some leases last 36 months you'll have to create 72 fields, and then place them on your layout. Worse yet, for your 12- and 24-month leases, most of those fields are empty.
If you start signing 48-month leases, you have to create a whole new slew of fields and start rearranging your layout again. And what if your tenants make more than one payment each month? It'd be so much more efficient if each record could create only the fields it needed.
You're on the right track—instead of adding fields in the Lease Agreement table, what you need is a set of new records in a related table (see the box on These Terms Are Relational for a definition of table and other terms you'll need to know for this section). That's where a relational database comes in. You need separate tables to store each type of information. Because when you think about it, monthly payments aren't really part of a Lease Agreement table. Date Paid and Amount Paid data pertains to a specific Lease Agreement, but it doesn't belong with the name of the tenant or the PDF of the lease agreement itself. What you need is a new Payment table, where you can add 12 records (one per month, of course) to the 12-month leases, or 24 records to the 24-month leases. With a separate table, even if you start offering 5-year leases, you'll never have to add more fields or stretch a layout to accommodate a change in the way you do business.
And you don't even have to create a new file for your new table. FileMaker lets you put dozens, even hundreds, of tables into the same file. There's an art and science surrounding how to figure out which tables you need and how to relate them to one another. You'll learn that in Chapter 5. For now, you'll learn about the tools you need to create a related table and enter monthly payment records on the Lease Agreement layout.
4.2.1. Understanding the Elements of a Relationship
Now that you've decided to store lease information in the Lease Agreement table and Payment information in a new Payment table, you need to make sure payments match the right Lease Agreement record? First, you start with a unique identifier called a key field, which uses the auto-enter field option to create a serial number that's unique for each record in the table. Then you use the Relationships graph to match the two tables' key fields. Finally you create a special layout object, called a portal, that lets you view, create and edit payment records on the same layout where you store data about each Lease Agreement.
4.2.2. Creating a Key Field with an Auto-Enter Serial Number
To ensure that Lease Agreements and Payments records match properly, you need a unique identifier in the Lease Agreement table. One of FileMaker's field options, called Serial Number, automatically assigns a unique number to each record when it's created. Here's how to create a key field, and then apply an Auto-Enter Serial number option to it:
Choose File→Manage→Database, and then if it's not active, click the Fields tab.
The Manage Database window appears, with the Lease Agreement table's fields in a list.
Up To Speed: These Terms Are Relational
Before you dive into creating a relational database, you'll find it helpful to review some vocabulary and learn a few new terms:
A database is a collection of tables, layouts, and other features that forms an organized system.
A table holds information about one kind of thing, like your lease agreements and payments or in another database: people, products, leases and payments..
A record is the collection of data in a table that described one thing. It's like a single row in a spreadsheet.
A field holds one attribute of something: lease duration, the date a payment was made, the person's first or last name, the order date, the color of a product, or the supplier's address. It's like a single column in a spreadsheet.
An attribute is an individual characteristic. For example, a bicycle might have several attributes: color, size, style, and price. In a database, each of these attributes gets its own field. Remember the term "individually significant" from the box on A Field = Individually Significant Data?
An entity is one kind of thing. If you track information about your Customers, the concept People is an entity, so is a Payment or an Invoice or an Invoice's Line Item. Remember though, the individual thing itself isn't the entity. The person Steve Jobs isn't an entity, neither is the April 2010 payment for a specific lease. If this is confusing to you, don't get hung up on it. Focus on this: Each entity (People) gets one table in a database and each instance of an entity (Steve Jobs) gets one record in that table.
A key field uniquely identifies each record so that each related record knows which record it matches, like a Social Security Number for a person. You'll create a key field using FileMaker's Auto-Enter Serial number option in this chapter, and learn about their finer points on Section 5.2.4.
A relationship describes how the records in two tables match each other (Section 4.2.3.1).
The most common relationships are called parent-child relationships. That's because one parent record can have many children, but each child record can have only one parent. So in your database, the Lease Agreement is the parent record and each Payment record is a child.
In the Field Name field, type agreementID.
This field name may seem odd (no spaces and a mix of upper- and lower-case letters), but it's one of many naming conventions used by developers to help them quickly identify fields that they've created to make the database work. In this naming convention, the field name starts with the name (or one-word abbreviation) of the table for which it is the key field. The "ID" at the end confirms that that the field is used as a key.
From the Type pop-up menu, choose Number.
Key fields are most often number fields. (See Section 5.2.4 for more information on choosing and creating a good key field.)
Click the Create button, and then click the Options button.
The Options for Field "agreementID" window appears.
Turn on the "Serial number" checkbox, and then click OK until you're back on the Lease Agreement layout.
A serial number appears in the agreementID field for each new record as it's created. See Figure 4-5.
From now on, every record you create in the Lease Agreement table will be assigned a unique number that you can use to create relationships to other tables. However, the records you've already created don't have serial numbers yet.
Figure 4-5. Auto-enter serial numbers are most often created as a record is created ("On creation"), but if you choose "On commit" instead, FileMaker doesn't generate the number until the first time the user commits the record. Usually, you use this option when your database has multiple users (as you'll learn on page 730). For instance, a user might create a record, but then delete it instead of filling out its data. In that case, there would be a gap in the serial numbers, because the numbers assigned to deleted records are never reused. The next value is a running tally of where you are in the database. When you first set up an auto-enter serial number field, the next value is always "1" unless you change it. If you're importing data from another source, you should find the highest serial number in the imported data, and then set your field's "next value" to one number higher than the imported data. And while the most common "increment by" value is 1, you can make the numbers increase by any other number you choose instead.
4.2.2.1. Using Replace Field Contents to add serial numbers
When you create a key field after data's been entered, you don't have to go to each record and manually enter a serial number. You can use the Replace Field Contents command (Section 2.3.2) to add in the missing serial numbers and reset the field option's "next value" counter at the same time. Since you have to have a field on a layout in order to use the Replace Field Contents command, you may have to put the agreementID field on the Lease Agreement layout if isn't already there.
In Browse mode, choose Records→Show All Records.
Every record has to have data in its key field in order to relate to another table, so make sure you aren't looking at a found set of just some of your records.
Warning:
If you're using this tutorial on a database where some records have serial numbers and some don't, find only the records without serial numbers in this step. In that case, you wouldn't want to replace the serial number in any record that already had one.
Click in the agreementID field.
If you don't click into a field first, the Replace Field Contents command will be dimmed.
Choose Records→Replace Field Contents.
The Replace Field Contents window appears. See Figure 4-6.
Select "Replace with serial numbers", and then turn on "Update serial number in Entry Options", if it isn't already selected.
The first option tells FileMaker how you want the numbers created and the second option ensures that you don't have to find the new highest number, and then return to the Manage Database dialog box to change the "next value" setting for the agreementID field manually after the replace is done.
Click Replace.
Serial numbers are created in all the records of your database.
Flip through the records to see the serial numbers. You can go back to the Manage Database dialog box and check the auto-entry options for the agreementID field to see that its "next value" has been updated. Create a new record to see the next value appear automatically in the agreementID field.
Tip:
Because it can be cumbersome to create a key field and populate it with data months or even years later, it makes sense to create a key field in every table you create even if you have no immediate plans to relate the table to any other table. That way, you're ready to go when needs change.
Figure 4-6. In the Replace Field Contents dialog box, the highlighted button is not the one that does the action you've just set up. The Replace command can't be undone, so FileMaker is saving you from unintentionally destroying good data if you hit Enter too soon. Instead, Enter cancels the replacement and leaves everything as it was. Replace Field Contents is lifesaver when you have to retrofit a table with a key field after you've created records. However, replacing data after you're created relationships between tables is risky—if the value in the key field in either table changes, the child record gets disconnected from its parent.
4.2.3. Creating a New Table
Your Payment table needs to store information about each monthly payment for a specific Lease Agreement. The Payment table's attributes are the date the rent was paid and the amount paid. You also need a key field for hooking up Payments to the Lease Agreement table. And since it's good practice to create a key field in every table, just in case, you'll also add a paymentID field.
Two key fields in one table? It may sound crazy, but it's not uncommon for a table to have 10 or more key fields that let it relate to that many other tables. The first key field you'll create (called paymentID) uniquely identifies each Payment record, and could be used when you figure out a reason to link the Payments table (as a parent) to another table. (See Section 5.2.4 for information on primary and foreign keys.) The second key field (agreementID) will hold the value that matches the value in the key field of a specific record in the Lease Agreement table. That's how a Payment record (the child) matches up with the proper Lease Agreement record (the parent).
Choose File→Manage→Database, and then click the Tables tab.
This tab is where you create, edit and manage your tables.
Click the Fields tab.
You're viewing the field list for the Payment table. FileMaker's smart like that and switched to the selected table for you. But if you need to, you can switch between tables using the Table pop-up menu above the list of fields.
In the Field Name field, type paymentID, and then select Number from the Type pop-up menu. Click Create.
It's a good habit to create a table's key field first thing. That way, you won't forget to do it.
Click the Options button and make the paymentID field an auto-enter serial number.
All your Payment records will have serial numbers because you've created a key field right at the beginning, before you create any records. And since you have no records yet, the next value should be "1".
At the bottom of the Options window, turn on "Prohibit modification of value during data entry".
This prevents users (even you, the developer) from changing the data in a field. Protecting the data in a key field is critical to keeping your records properly related to one another.
Note:
You didn't want to prohibit modification of the agreementID field when you created it because you still had to use the Replace Field Contents command to get the data into the field. Now that you know how critical this step is, don't forget to go back and make that selection in the Lease Agreement table.
Use the skills you learned earlier in this chapter to create these fields, with the following types:
Date Paid (Date)
Payment Amount (Number)
agreementID (Number)
Click OK when you're done. Because you're still viewing the Lease Agreement layout, you don't see evidence of your new table yet. But it's there.
4.2.3.1. Viewing the new table's layout
If you click the Layout pop-up menu (in either Browse or Layout mode), you'll see a new layout called Payment. FileMaker created it for you when it created your new table. And much like the boring layout you got when you created the Lease Agreement table, there's nothing there except the standard lineup of layout parts and your newly created fields. In Browse mode you can see that unlike your first table, this new table doesn't have any records yet. If you click the layout to see the fields' borders, you'll see the warning message in Figure 4-7.
Figure 4-7. When you see this message, just click OK. But don't bother creating a record. You'll do that later—from the Lease Agreement layout. Every time you create a new table, you'll also get a free layout just like this one. That's because tables need a place to show their fields. But once you start relating tables to one another, you'll often find that these automatically created layouts are just so much fluff. You can delete these utilitarian layouts (In Layout Mode, choose Layouts→Delete Layout), and then start from scratch using the Layout Assistant to get something more decorative.
You could dress this layout up any way you want to. For instance, you might turn it into a list layout so you can report on your payments. But since you'll be creating Payment records and entering data from the Lease Agreement table, just note that the layout's here, and go back to the Lease Agreement layout.
4.2.4. Creating a Relationship Between Two Table Occurrences
You just saw that when you create a table, and then add fields to it, FileMaker makes a bare bones layout for that table. It also makes a Table Occurrence for the new table on its Relationships Graph. That graph is found on the only tab of the Manage Database window that you haven't seen yet. And true to its name, that's where you create the relationship between the Lease Agreement and Payment tables.
Note:
Your Relationships graph can have more than one instance of any table, and each instance is a different view into the table. Each instance of a table is called a table occurrence. It's important to know whether you're referring to the table itself or an occurrence of the table. A word of caution though, FileMaker isn't all that consistent about using the term in its own windows and help files, so it's not your fault if you're confused.
Also true to its name, the Relationships Graph is a visual representation of your file's tables and how they relate to one another. And you create relationships in perhaps the easiest way possible: you drag from one table to another to create a line. Here's how to create a relationship between two tables, using their key fields.
Choose File→Manage→Database, and then click the Relationships tab. Or use the shortcut Ctrl+Shift+D (⌘-Shift-D)
You'll see two table occurrences: one for the Lease Agreement table and the other for the Payment table. You need to draw a line between the agreementID fields in each table, but that field's not visible in the Lease Agreement table occurrence.
Click and hold the tiny triangle at the bottom of the Lease Agreement table occurrence.
That scrolls the field names so that you can see the one you need. Or you can drag the bottom border of the table occurrence to make it tall enough to show all its fields.
In the Lease Agreement table occurrence, click the agreementID field, and then drag to the agreementID field in the Payment table occurrence. Release the mouse when it's pointing to the proper field.
As you drag, you'll see a line with a box in the middle (Figure 4-8 top). When you release the mouse, the two key fields jump above a new divider at the top of each table occurrence (Figure 4-8 bottom).
Double-click the box in the middle of the relationship line.
The Edit Relationship window appears (Figure 4-9). The window is divided into halves, showing Lease Agreement on the left and Payment on the right.
On the Payment side of the relationship, turn on "Allow creation of records in this table via this relationship" and "Delete related records in this table when a record is deleted in the other table".
This setup is typical of the child side of a relationship. Here's how to think about it: You'll be creating Payment records from the Lease Agreement layout, so the relationship's options need to allow record creation. And if you delete a Lease Agreement record, there probably isn't much use for the Payment records, so that second option deletes Payment records that would otherwise be "orphaned" when the parent record is deleted.
Click OK until you return to the Lease Agreement layout.
Or whichever layout you were viewing at the start of this tutorial.
If naming fields in both tables with the same name seemed confusing as you were doing it, you've just seen why it's a good idea. In a large or complex database, some tables can have many key fields for relating to other tables. But if you use the same name in the child table as the key field in the parent table, it's very easy to find the proper key field, and then drag a line between the two tables.
Figure 4-8. Top: As you drag, the two selected key fields stay
highlighted to help you stay oriented. The pointer has changed
shape to show that you're creating a relationship. Notice that the
line is straight on the Lease Agreement end and has a "fork" at
the Payment end. This crow's foot helps you know which is the
parent and which is the child side of the relationship. But how
does FileMaker know which is which? Lease Agreement's agreementID
field has the Auto-Enter Serial number option turned on, which is
standard operating procedure for a field that's meant to be used
as a parent table key field.
Bottom: When you release the
mouse, the two key fields are listed at the top of each table
occurrence with the relationship line drawn between them.
Translated into plain English, this relationship means "For each
Lease Agreement record, match all the Payment records where the
agreementID value equals the value in the Lease Agreement's
agreementID field."
- Create BookmarkCreate Bookmark
- Create Note or TagCreate Note or Tag
- DownloadDownload
- PrintPrint