[N]...by Jon Rosen
Link or Lookup?
How FileMaker uses lookups.
One of the most common calls I receive about FileMaker is about lookup fields. For the moment, lookups are the closest thing to relationality that FileMaker users have at their disposal. A lookup, in FileMaker, is a way to use data from a 2nd file within the primary file. This is somewhat analogous to a relation in a relational database and is a powerful feature of FileMaker.
It works like this. Let's say you have an invoice file and a customer file. You need the customer's name and address to print on the invoice, but you don't want to type it in each time. You need to have a common field to identify the customer, in this case a customer number. In a relational database, the customer number would be called the primary key, because it is a unique identifier that ties the two fields together. You enter the customer number into the invoice file and use that to identify the customer for the lookup. Let's say you want to lookup the customer address. The lookup field basically says, get me the customer address for the first record you find with this customer number in the customer file.
What could go wrong in a lookup. Well, several things, actually. The first is not using a unique identifier. If you were to use the customer name instead of the customer number, your lookup would pick up the address information for the first customer in the database with that name. That could be a problem because you certainly may have more than one customer with the same name. So, the first rule of lookups might be to make certain you are using a unique identifier.
The next problem could be from mixing field types for key identifiers. If the customer number in one file is a number, but defined as text in the other, you may end up with the wrong record. This is because of the different way FileMaker indexes text and numeric fields. If a number is formatted as text, a lookup may find the first number that is a substring of the actual number. For example, if a number is formatted as text then 12345 will match 1234 because the 2nd string is contained within the first.
Lookups normally work on substrings. If you are doing a lookup on the name “Smith,” if “Smithton” comes first in the file, that's what will be returned. There is, however, a way to get around this problem. Use the “=” to instruct FileMaker to return only whole words that match the string. “=Smith” will return only Smith.
There is one other caveat to lookups. If you do a match on more than one word, the lookup will return the first match without regard to the order of the words. “Red Green” will match on “Green Red.” That's true for “=Red =Green” as well.
It's important to have a good command of the ins and outs of lookups. They are here to stay. Even when the relational version of FileMaker is on the market, you will want to use lookup fields. Relational links, you see, are live connections to another file. Lookups are static. When you want to preserve a value for posterity, you will use a lookup field instead of a relational link.
Jon Rosen
- END -