FileMaker Lookups And Data In The Found Set

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

A lookup is a relationship related auto enter field action that will copy the information from a child table occurrence into a field in the parent table occurrence. The copying of information is triggered when the parent key field in a relationship had data first entered into it or data is refreshed.


A lookup can take place in at least five different situations. When data is first entered into a parent field of a relationship, the lookup is executed for that record. When data is edited in a parent field of a relationship, the lookup is executed for that record. When the replace command is executed within a parent key field of a relationship, the lookup is executed for all the records in the found set. When data is imported in from another file into a parent field of a relationship, the lookup is executed for that record ( note ... this can be turned off via one of the import dialog boxes). When the relookup command is executed for a parent key field of a relationship, the lookup is executed for that record.

A lookup is great when you want a time capsule view of information from the child file 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.

So the above example would be a PRO of lookups.

Another advantage is screen refresh. When a related field is on a layout, it has to open the related file 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 file if when the lookup is executed.

Finally, a lookup field can be indexed. That is to say it can be used as a parent key 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.

So lets even things out by talking about the downside of using lookups. For starters, a lookup process includes the copying of information from one file to another file. This means that data is in both places, repeated and redundant. So it will take up more room in a FileMaker solution. 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 file 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.

Replacing via a relookup can be done by clicking into the parent key field of the relationship and choosing the Relookup Contents command under the Records menu. This will refresh the lookup data from the child file for every field that uses it within the found set.

A relookup cannot be done for a single field replace but we have not talked about the replace command yet. I do what to say that you can do the same thing as a single field relookup by using the replace command. This is done by replacing via calculate the related value.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

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.