The Upsides And Downsides Of FileMaker Lookups?

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Although the premise of this discussion is lookups, the same can be said for auto enter via a calculated value. You can use auto enter calculated value to auto enter a related value just like a lookup would. I’ve heard that some developers have abandoned the use of lookup settings for auto enter via a calculation because of the greater ability to control the data entry.

Notice how close together the auto enter calculated value and the lookup settings are in the auto enter dialog box?

THE UPSIDE OF A LOOKUP - A lookup is great when you want a time capsule view of information from the child table at a particular moment in time. An example could be the price of product a customer purchased on an invoice. You want the invoice to show the price of when the order was placed. A related field would change the invoice totals if prices were changed in the inventory file. That would be a bad thing because you would have the current price but not the actual sales price at the time of the transaction.

Another advantage is screen refresh. When a related field is on a layout, it has to open the related table in order to show the information. The same is NOT true for information via a lookup. Data via a lookup will only need to open the related table if when the lookup is executed.

Lookups copy information from one field to another field when the field in the parent key field is updated. It can be a great feature for the FileMaker developer but it can also have a possible downside. Almost any downside can be overcome by using other FileMaker features. You simply need to know when to use which feature.

A lookup field can be indexed. That is to say it can be used as a parent match field, it be searched much faster during a find command and it will sort faster ... particularly in databases with a large found set of records.

Finally, lookups can be scripted and can be calculated! Introduced with FileMaker 7 are two logical functions that work with lookup operations. They are Lookup and LookupNext ( check them out ! )

THE DOWNSIDE OF A LOOKUP - For starters, a lookup process includes the copying of information from one field to another field. This means that data is in both places, repeated and redundant ( three things that all mean the same thing by the way ).

So a lookup operation will take up more room in a FileMaker solution than showing a related field on a layout. Although I haven’t seen many FileMaker database files crash because of their size, it could always be a factor.

Another disadvantage of lookups is the flip side of the advantage we spoke of earlier. Lookup data is a time capsule of the data in the other table when the lookup was last executed. If you want to show the latest information in a field of related data ... a lookup is NOT the way to go. Related data is always dynamic. When it is changed, it can be seen almost immediately.

Also a lookup will only show you one related value, unlike a portal that has the ability to show multiple relations. The one relation that a lookup will pull over will the be the first related record. The first related record can be different based upon the sort order of your relationship. If you did now specify a sort order for the relationship, the lookup will be the related record that was entered first in that table. If their is a sort order for that relationship, it will be the first related record using that sort order.
More info about the author and FileMaker in general, contact me at

© 2008 - Dwayne Wright -

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.