Database Reference
In-Depth Information
If you start signing 48-month leases, you'll have to create a whole slew of new 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 have only the fields it
needed, and no more.
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 re-
lational database comes in. You need separate tables to store each type of information. Be-
cause 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 accom-
modate 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.
Understanding the Elements of a Relationship
Now that you've decided to store lease information in the Lease Agreement table and pay-
ment 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 records from
a related table. In this case, you'll use a portal to show payment records on the same layout
where you store data about each lease agreement.
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 Num-
ber
, 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:
