Relookups In A FileMaker Found Set

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

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

As we discussed before , a lookup is a FileMaker relationship related term that will copy the information from a child table into a field in the parent table. 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 relookup command ( and associated script step ) is another way to refresh the data in the key field and it will refresh that data for all the records in a found set. A relookup can be done by clicking into the parent key field of the relationship and choosing the Relookup Field Contents command under the Records menu. Once again, this will refresh the lookup data from the child file for every field that uses a related lookup operation within the found set.

Sometimes, a relookup can be a very bad idea. Say you have a products sold file that is linked to an inventory file using a product id field. You did this because you want the product name and current price automatically copied over ( via a lookup ) from the inventory file when a product id is entered or refreshed. You changed the name of a product and you want the new name to appear on all the legacy product sold records. You do a relookup on the product id field and realize that along with the product name being replaced, so was the product price with the current price. This is because a relookup will update all data for all fields linked to it for all records in the found set.

In a case where I want the latest related data in a particular field for a found set of records, I tend to use the REPLACE command with a calculated result equal to the related data. This can also be done in scripts by using the Replace Field Contents script step. In a case where I want the latest related data in a particular field for just one record, I tend to use the SET script step with a calculated result equal to the related data.

Also, a relookup cannot be done for a locked record. A locked record means that someone on the network is in that particular record and two users cannot modify the same record at the same time.

=
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.