FileMaker Lookup Related Data

What is lookup? And why is it important?

Let’s say you have a product catalog table, with a product of “Football” with pricing of $10.

You also have a sales order table. And you are about to create a new sales record to sell a “Football” to your friend John.

As a database user, when you enter “Football” into the sales order. You would want to be able to reuse the pricing data we entered previous to save time, right?

If you already got your relationship setup properly, you will have 2 main ways to do it.

  1. Create a direct reference to the “pricing” field in the product catalog table.
  1. Create a field in the sales order table, and have that field copy and paste the pricing from product catalog
 

Basically method #2 is what lookup is all about.

But what is the difference anyway?

The biggest difference is how they handle historical value.

For example, today your football is priced at $10, and you created a sales order and sell one of them to your friend John, he seems very happy with the low price. So you went home thinking “hmmm… maybe I can up the price and make more money”, so in the next day you up the price in the product catalog to $20

If you are using method (1), a direct reference to a related value. Since yesterday’s sales order pricing is directly linked to product catalog pricing. It will also be increased to $20. Which means the record from yesterday where you sell to John will also change to $20, which is not an accurate historical record.

Unless you plan on never changing your product catalog pricing. Method #1 is not able to handle situation like this.

But with method (2), since the value is stored within the sales order table itself. It doesn’t matter if the next day you decide to be super greedy and increase pricing to $500. The historical record of you selling the football to your friend John at $10 will always be there, because it is stored within the sales order table, not the product catalog table.

Thats the general idea of it, and setting up a lookup field is quite easy too:

  1. Connect the relationship between your table
  2. Click into one of your field, and in the “auto-enter” tab
  3. Select “lookup value” and select the relationship and the field you want to lookup from

 

If you want to see it in action, you can check out my video above where I am going to explain this whole thing in depth.