Database Reference
In-Depth Information
UP TO SPEED: EMPTY LOOKUPS
Lookup options give you some control over what happens when you don't have a matching related
record. Normally, if FileMaker tries to find a related record to look up data from and it can't find
one, it just leaves the lookup field alone.
That's what happens when, in the “If no exact match, then” group of radio buttons, you turn on “do
not copy.” Here's what the others do:
▪ The “copy next lower value” option looks at the closest
lower
related record. For example, if
you turn it on for the Invoices::Street Address field, and there's no matching related customer,
FileMaker copies the address of the customer with the next lower Customer ID alphabetically.
In this case, it makes absolutely no sense.
But what if you're looking up price information based on quantity? If the customer orders 38, but
you have pricing for 30 or 40, you might want to get the price for 30 items, the next lower value.
▪ The “copy next higher value” option works just like its similarly named counterpart. It just cop-
ies the value from the next
higher
related record instead.
▪ The “use” option lets you specify any value you want to substitute for a missing related value.
For example, if you're looking up customer age information and you don't have an age for one
person, you can tell FileMaker to use “N/A” instead.
Using a Relookup
A lookup is triggered whenever you change the data in the key field on which the relation-
ship is based. That's why changing the Job ID field makes FileMaker look up the customer
information again.
Sometimes you want a lookup to trigger
without
changing the key field. For example, sup-
pose a new customer hires you. You work for her for 3 months but never receive pay-
ment—despite 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 correct her address in
the Customers table, but that doesn't affect the old invoices.
Luckily, you can easily update them—with the Relookup Field Contents command. First,
find just the three bad Invoices for your customer and then click the field that normally trig-
gers the lookup—the Job ID field in this case. Then choose the Records→Relookup Field
Contents command. You see the message shown in
Figure 5-21
.
