Lookups vs. Related Data - File Maker

To recap, a looked-up value is a bit of data that is copied from a field in another database via a relationship. If you’ve got an invoice database with an associated line items database, whenever you add a product as a line item on an invoice (the customer is buying it), you want certain fields to automatically “look up” certain bits of data from a products database, like the product’s name, price, or description, so that you don’t have to look them up yourself. Perhaps more importantly, you want your historical information to be preserved on the looked up fields, regardless of any changes made from the “master” data source fields that the lookup fields look up from.

In the case of our related company information, these are dynamic, related fields that update automatically if company information is modified, and these changes are okay, even desirable. But in the case of something like an invoicing system, auto updated product pricing on an invoice would not be a good thing.

For example, click the Invoices tab in Contacts.fp5 and flip to a record that has a related invoice in the portal. Click an invoice that has a total Order $ greater than zero. You’ll find yourself in the Invoice.fp5 database on the General Info tab. Here you’ll see all of the same company and contact fields that you see on the contact’s record, but the fields here are local lookups, not related fields.

Now go into Layout mode and double-click one. Yup, local fields. Why? Well, if ever the contact or company information were deleted from the database, you wouldn’t want data in dynamic, related fields on an invoice layout to just vanish into thin air; you would want to preserve it forever as a history of who ordered what and when.

So when a new order is created and a contact selected, all the address/ phone information is looked up via a relationship to Contact.fp5 and pasted, really, into the same named local fields in Invoice.fp5. Now, even if the contact changes his address (and you’ve updated her record in Contact.fp5), and orders again, you’ll have one invoice record with the old address and one with the new. This way, you’ll always have an accurate history of what was sent where.

Now, if you go to the Line Items tab in Invoice.fp5 and go into browse mode, you’ll see another important use of lookup fields. Here’s where you select what is being ordered by choosing products in a portal.

Go ahead and add a few to this portal in the next available open portal rows. As you are selecting products in the portal, all the fields in InvoiceLineItem.fp5 (like price and other descriptive information), the related database you’re working with here, are auto-populated via auto-entered lookups. After an order’s complete, you would print a copy and send one to the customer for payment, then enter that payment information on the payment tab. If the prices or product information ever changed in Product.fp5, it would be okay because the lookups in InvoiceLineItem.fp5 would preserve the old, looked-up information before the product was updated.

However, if the fields in InvoiceLineItem.fp5 like price (called List in this database) were dynamically linked to Product. fp5’s data (by being defined as an unstored calc like Products by ProductID::List), updating the list price in Product.fp5 would also automatically update the list price on all line items for all invoices in the system, changing the balance due, the invoice total, and other fields. Basically, you would have a big reconciliation mess on your hands.

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

File Maker Topics