Lookups

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:

• 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 home address, you lose any record of where you sent the invoice. If you go back to the special-case invoice two years from now, it will look like you sent it to his home address. But that's not correct.

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.

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:

1. In the Customers database, choose File Define Database. On the Fields tab, from the Table pop-up menu, choose Invoices.

   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.

2. Select the Street Address field in the field list and click Options. In the Field Options dialog box, click the Auto-Enter tab.

   Remember, FileMaker automatically enters a lookup field's value for you.

3. Turn on the "Looked-up value" checkbox.

   The Lookup dialog box appears (Figure 8-22).

   Figure 8-22. Imagine you have a table of currency exchange rates. Some of the currencies didn't have data available the day you gathered the rates, so those rate fields are blank. If you use a lookup to refresh exchange rates in your Products database, you don't want to wipe out the existing exchange rate in this case. Instead, you'd rather keep last week's value, so you turn on the "Don't copy contents if empty" option.

    
4. Make sure the "Starting with table" pop-up menu is set to Invoices.

   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.

5. From the "Lookup from related table" pop-up menu, choose Customers.

   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.

6. In the "Copy value from field" list, choose Street Address. Turn off the "Don't copy contents if empty" checkbox.

   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.

7. In the "If no exact match, then" group, turn on the "use" radio button and leave the associated text box empty.

   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 4.4.4.4). 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.

• Company Name.
• First Name.
• Last Name.
• City.
• State.
• Zip Code.

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.