Database Reference
In-Depth Information
Figure 5-19. Here's the new pop-up menu for the Jobs ID field on the Expenses layout. To attach a
job to an expense, choose a Job ID from the new pop-up menu. When you do, FileMaker places the
ID of the job you choose in the Expenses::Job ID field. The name is displayed in the pop-up menu
to help you pick the right ID but is not entered into the Job ID field.
Lookups
Since you have a relationship between the Jobs and Customers tables, you don't have to
enter customer information on each job record. Nor do you want to store that data in both
tables. If you display the customer's name and address information on the Jobs layout and
then update the customer's data, it automatically shows on the Jobs layout. This dynamic up-
dating of related data is the essence of a relational database. Sometimes, however, 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
use relationships to do a one-time copy and paste of data
from one table to another. Once the looked-up data is in its new table, you can edit it, if ne-
cessary, but it doesn't change automatically if the related data changes.
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 she's going to be in
Punakaiki for a month and to please send her next invoice there, you have no way to
enter an alternate address on just one invoice. You have to change the address in his cus-
tomer record, send the invoice and then change the address back.
▪
It destroys relevant information
. When you
do
revert the customer record with her ori-
ginal address, you lose any record of where you sent the invoice. If you go back to the
special-case invoice 2 years from now, it'll
look
like you sent it to his home address.
