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.

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:

• You can create a database for each kind of interface you need. For example, if you need to track sales, you can create a database with the tables you need to store the actual data: Orders, Line Items, Customers, Shipments, Products and so forth. You can use this database in two ways, though: Sales Entry (creating and managing orders) and Reporting (daily, monthly, and quarterly sales reports, trend and promotion analysis and so on). To keep your system as simple as possible, you can create these separate interfaces as two distinct databases. Since they share the tables from the central Sales database, the reporting data stays up-to-date as FileMaker processes new sales. .

  But since they're in two databases, the layouts you need for order entry don't get in the way of the reporting layouts, and vice versa

• One company usually has many database needs. You might have Sales, Marketing, and Engineering departments in your organization. Each of these departments has unique needs and wants a database to match them. But the Marketing department might be very interested in sales data, and the Sales department needs access to engineering information. You can create a separate database for each department, but share some tables between systems. This way you get an interface tailored to each group, but the important data is shared.
• When you can use external table occurrences, you have a very flexible design metaphor. A database-or file-can hold interface elements (layouts, scripts, value lists) or data (tables and fields) or both. You can construct the database in almost any way you see fit: one file for each table, all tables in one file, or tables in logical groupings; all the interface in one database, or several databases to break things up. A FileMaker file is a very flexible unit of organization: Use it as you see fit.

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:

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.

Figure 8-23. This series of images shows your lookups in action. In the first image, the invoice is associated with Daniel's job. If you choose a job tied to Mister Miyagi instead, the customer fields update instantly to show his information. Since these are lookups, though, you can change them right here without affecting the customer record itself. And if you change Mister Miyagi's address in the future, it won't affect the address on this invoice.

 

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.

Figure 8-24. When you run the Relookup Field Contents command, FileMaker shows this message. It's careful to let you know just how many records you'll be updating. That's because you can't undo this action. If you lookup new address information into old invoices accidentally, you lose historical data. If you're sure you want to proceed, click OK.

---

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.

---