Since you have a relationship between the Jobs and Customers tables (Section 7.3.3), 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 can easily put the address fields from the Customers table occurrence on the Invoice layout and see the customer's address. This method is a bad idea for two reasons:
|POWER USERS' CLINIC
Systems with More than One Database
Even if you're building a brand-new system from scratch, there's no rule that says every table has to be in the same database. In fact, you have many good reasons to divide your system across multiple files. The last chapter talked about one of those reasons: You might be storing large images or other files in your database. You can keep these files in an external table so the database file itself isn't so large. Then you can back up, copy, and email the information about the images without including the images themselves.
Here are some other reasons to use more than one file:
These problems arise because invoice data is transactionalan invoice represents a single business transaction at one point in the past. But your customer record doesn't represent a single transaction with your customer. Instead, it represents an association you have with that customer. In general, transactional data should never change once the transaction is complete. Lookup fields solve the problem of saving transactional data.
|POWER USERS' CLINIC
Going to External Records
Now that you've got a file reference and a table occurrence from an external file, what do you do with it? You can create a new layout attached to the Other Leads table occurrence and fill it with fields from the People table. But you already have a great layout for viewing the details of a People record: The detail layout in the People database.
Once again, Go to Related Records comes to the rescue. First, turn the First Name and Last Name fields on the Other Leads portal into a button that performs the Go to Related Record command. In the Go to Related Record Options window, choose the Other Leads table occurrence. Since this is an occurrence of an external table, the "Use external table's layout" checkbox comes to life.
When this checkbox is turned on, the "Show record using layout" pop-up menu lists layouts from the People database rather than this database. Turn the checkbox on and choose the Detail layout. Don't forget to turn on "Show only related records."
Now when you click a person's name, the People database pops up and shows you the correct person. Like magic!
While related fields automatically show new data, lookups use a semi-automatic approach. If you change a customer record, it won't affect the fields in the Invoices record at all. But if you change the Job ID on an invoice record, the lookup triggers again, and FileMaker fetches the new customer's data. Additionally, you can change the data in a field formatted with Auto-Enter lookup at any timefor a one-time address change, for example. This semi-automatic approach to updating data turns out to be just the right thing for transactional data like address fields on invoices: When you change the transaction record, its fields update appropriately, but when you change source records (the address fields in your Customer record), FileMaker leaves the transaction alone.
8.6.1. Creating Lookups
To create a lookup, you define a field normally but add an Auto-Enter option called Looked-up value. You can also add a lookup to an existing field. Simply click the field in the fields list, then click the Options button. The following steps explain how to define a new lookup field:
In the Field Name box, enter Street Address and make sure the Type pop-up menu is set to Text. Click Create.
FileMaker adds the new field to the field list. Right now, though, it's just an ordinary field.
Remember, FileMaker automatically enters a lookup field's value for you.
The Lookup dialog box appears (Figure 8-22).
It almost certainly is set, because in this case, the context is clear. You're defining a field in the Invoices table, so that's the field's perspective. If you have a table on the graph multiple times, you might have to change the "Starting with table" pop-up, and doing so will influence how the lookup finds related data.
As soon as you choose a table, the "Copy value from field" list is populated with all the fields in the Customers table. You're interested in the Street Address field's value.
If you turn off "Don't copy contents if empty," FileMaker dutifully copies the empty value, wiping out data in the lookup field. If you turn this option on instead, FileMaker leaves the lookup field untouchedits value before the lookup remains in place.
If there is no customer record, the street address field should be blank. If you leave this set to "do not copy," any existing address (for a different customer perhaps) is left in the field. (See the box on Section 8.5.1 for other "If no exact match, then" options.)
You're done. Click OK three times to back out of all the dialog boxes. Now switch to the Invoices layout and add the new field to it (Section 22.214.171.124). When you choose a job from the Jobs table, the Invoices address field now looks up the appropriate address from the Customer table, provided you've entered addresses for your customers.
Note: If you're not using the version of this database that's pre-populated with data from the Missing Manuals Web site (Section 3.4.5), and you want to see this in action, give one of your customers some address information and a job.
To finish your Invoices improvements, add lookup fields for the following customer information. Use the same options as in the steps on the previous pages, or to save clicks, duplicate the Street Address field, change its name, and then just change the field from which it looks up. You get all the other lookup settings for free. When you're done, you can arrange your Invoices layout like the one in Figure 8-23.
8.6.2. Triggering a Lookup
Normally you trigger a lookup whenever you change the data in the key field on which the relationship is based. That's why changing the Job ID field makes FileMaker look up the address information again.
Sometimes you have a reason to cause a lookup to occur without changing the key field. For example, suppose a new customer hires you. You do work for her for three months, but never receive paymentdespite sending three invoices. You finally decide it's time to ask her what's up, and that's when you discover you've been sending them to the wrong address. You mistyped her address in the Customers layout and now all your invoices are incorrect too.
She agrees to pay you as soon as you mail the invoices to her correct address. You can correct her address in the Customers table, but that doesn't affect the old invoices. Luckily, you can easily fix them, toowith the Relookup Field Contents command. To use it, you first click the field that normally triggers the lookupthe Job ID field in this case. Then you choose Records images/U2192.jpg border=0> Relookup Field Contents command. You see the message shown in Figure 8-24.
Tip: To get into the Job ID field, just click it and be sure to choose the job that's already selected. After you make your choice, the Relookup Field Contents command works as expected.
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