FileMaker Pro 9: The Missing Manual

Lookups

Since you have a relationship between the Jobs and Customers tables (Defining the Relationships), you don’t have to enter customer information on each job record. Instead, FileMaker shows the same customer fields for each related job. If you update the customer’s first name, the new name automatically shows on the Jobs layout. This dynamic updating of related data is the essence of a relational database. However, many times you don’t want a piece of information to change; you want FileMaker to remember the way it was at a certain point in time. Lookup fields, which use relationships as a source for a sort of one-time copy-and-paste action, let you take a piece of data from a table and protect it from future updates.

Take a look at the Invoices table, for example. When you create an invoice, you attach it to a job. The job is in turn attached to a customer. When it comes time to mail the invoice, you could easily put the address fields from the Customers table occurrence on the Invoice layout and see the customer’s address. But this method is a bad idea for two reasons:

• It doesn’t allow for special circumstances. If a customer tells you he’s going to be in Majorca for a month and to please send his next invoice there, you have no way to enter an alternate address on just one invoice. You have to change the address in his customer record, send the invoice, and then change the address back.

• It destroys relevant information. When you do update the customer record with his original ...