Setting Up Lookups - File Maker

Go to the General Info tab in the Invoice.fp5 database again and go into Layout mode. The BAdd1 (billing street address line 1) field is a lookup to Company.fp5.

Now access the Define Fields dialog and double-click this field. Go to the Auto-Enter tab and click the Specify button next to the Looked-up value checkbox and text. You’ll see the Lookup dialog for BAdd1, which looks like this:

Setting Up Lookups

To define a lookup, all you do is specify which relationship you’re going to use (via the drop-down at the top), pick the field whose value you will be copying to the current database field (in this case BAdd1), and then specify what happens if there isn’t an exact match via the relationship you’ve selected.

In this case, there will always be a valid company associated with the CompanyID of any invoice record, so if there isn’t an exact match it’s not that important. But in other cases where, say, the records on the side of the relationship where the data is being copied from change a lot (get deleted, have their primary key altered), you must specify what happens when there’s no exact match, using the radio buttons in the middle right of the dialog. Here’s what the different options mean:

  • do not copy: Does not copy anything into the field, leaving its contents as is.
  • copy next lower value: Copies the next alphanumerically lower value from the related database. “No zip code 47906? OK, give us 47905.”
  • copy next higher value: Copies the next alphanumerically higher value from the related database. “No zip code 47906? OK, give us 47907.”
  • use __: If there’s no match via the relationship, it will use whatever you type into the box. “No product description for the Tiger Lilly Bulbs product? OK, paste in N/A.”

The checkbox “Don’t copy contents if empty” says that if there is a match, but the matched field has nothing in it, the “nothing” won’t be copied over into this database field. This seems irrelevant, but if you ever do a relookup, you may have a value in the field now that you don’t want overwritten with nothing.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

File Maker Topics