Lookups are fast which is pretty much the only reason to use them. I don't like how they bury business logic in the database schema, I'd rather use a script to do the same.
As to updating the data: careful with that. The reason to use a lookup (or auto-enter calc with the update option turned off) is that you explicitly WANT to preserve the moment-in-time data. You do not want the address on an old invoice to automatically update when a customer moves.
I generally use lookups when I don't want the information to change over time, eg the shipping address or the price of an item on an invoice. If you want the information to remain current such as the customer address on an estimate then I would use the customer fields for that.
Good pint about preserving origanal data..
What sort of script would be best suited instead of a lookup..a Get fuction?
Thanks Will..Question..Just as a matter of interest does using fields from another related table in the current layout slow things down, compared to local table fields if you know what I mean..( orange magnifying glass v's Green ones)
I don't know the answer to that, but I have not experienced any slow down using related fields vs current table fields in a Filemaker database with 30k customers and 12k skus.
Here's what I'm doing in a system right now for associating customer contact information with estimates/orders/invoices/etc. Maybe you'll find something useful in it.
Customer information is actually stored in three tables Customer (name, ID, type, status, etc.), Address, and ContactAttribute (phone, email, URL, etc.). The Address and ContactAttribute tables both have an id_Customer field that matches the Customer::id primary key field. I'll use an Invoice table for discussion purposes, but the same idea might apply for estimates/orders/etc.
When an invoice is created and during data entry, the Invoice::id_Address field contains the primary key of the same Address record you'd see when looking at current addresses from a Customer record, i.e. Invoice::id_Address = Address::id and Address::id_Customer = Customer::id. At this stage when the Invoice is still being modified, if the Customer's Address is modified, the change will be reflected on the Invoice.
The Address and ContactAttribute tables also have a modificationTimestamp field and an id_modification field. The id_modification field is defined as an auto-enter calculation that replaces existing values whenever the record is modified; when the record is modified, the id_modification field gets a new UUID:
Let ( ~trigger = modificationTimestamp ; Get ( UUID ) )
(The ~trigger variable in the Let function is there to trigger the re-evaluation of Get ( UUID ) when the modificationTimestamp field changes. Also, I use a numeric UUID in practice rather than FileMaker 12's native Get ( UUID ) function due to the performance benefits of number fields as keys.)
When an Invoice is "committed" (not in the database operation sense, but in the "data entry is done and we're billing the customer now" business logic sense), a script takes the value from Address::id_modification field and looks for any Address records where the primary key field, Address::id, matches. If no matching Address record is found, the script duplicates the Address record, then sets Address::id in the new record and Invoice::id_Address to the id_modification from the old record, and nulls-out Address::id_Customer in the new record. This creates a new copy of the Address to be permanently associated with the Invoice, and the Address can no longer be modified by users via the Customer because that Address record is no longer associated with the Customer. (The Invoice::id_Customer field is unaffected by the operation.) If, when commiting an Invoice, a match for Address::id_modification = OtherAddress::id is found, this means that the Address has not been modified since the last Invoice using it, so Invoice::id_Address is simply set to Address::id_modification, linking the Invoice to the permanent Address record not accessible from the Customer record.
This approach lets me use permanent Address records to associate with Invoices for historical accuracy, but without creating any more duplicate data than absolutely necessary, since each Invoice doesn't have to re-create any address data that hasn't changed.
For just viewing data from related vs. local records, there is some performance penalty since FileMaker has to pull more records over the network; but this is usually negligible. Finds (including the Quick Finds referred to by the orange and green magnifying glass icons) will be more severely affected. If your users need to perform finds on related data, you may want to create a scripted process that performs an initial find in, for example, the Address table, then do a GTRR back to the related target records.
Thanks J.. for your great contribution..
Im going to print this off and go over it..I find your approach very interesting and may just help me broaden my tunnel vission
- Use lookups if you want to capture what was current at the time the record was created.
- Use related data if you don't care what it was back when the estimate or invoice was written, but want to reflect up-to-date info now all the time.
Lookups get my vote if you want a record of what it was at the time, which is usually best for invoice work. Similarly with tax rates, you want to look them up, not have them recalculate based on current rates on older invoices that went out when the old rates applied.
I would still go with Auto-Enter calcs with the update toggle off. Same effect but much more control as you can specify a full calc. In fact I don't think I've used lookups since FMI gave us that auto-enter option
I agree entirely.
I got caught up in the choice between lookups and live related values, but the auto-enter calc to capture info has the lookup's time-value but with more flexibility to capture data in more ways from more fields than a simple lookup.
Hi Stephen & Wim..
Whats your thought on say for a tax rate ..thanks Stephen you made me have a look into this..
Having a tax rate field on Invoices..with AutoEnter..check the "Data check box" and imput the tax rate.to AutoEnter. then check the "Do not replace existing value"
That way The historcal rate dosnt update....Please understand Im at the beginning stage of FM..so maybe off the mark
Beyond just the computation / data entry aspect of your original question, you might consider calculating the sales tax in the "lineitems" table.
Depending on your business logic you might have products that are taxed at different rates on the same invoice. For example in some states or even counties agricultural parts maybe taxed at a lower rate. Or if there are backordered items which cross the date line for tax rate changes etc, etc. It also can make credit memos / refunds more simple. Just a thought but dealing with "tax-overlay" districts are a real pain to comply with.
I think you do want to save the historical data at the point the invoice is generated, so the tax-rate data should not update. A simple lookup from a "current tax rate" table will work for this, with the do not replace option selected. That way it only gets updated if the address or other lookup-key is changed, which shouldn't happen on existing invoices once posted.
You might even want to have an Invoiced_date field, and make the invoice locked to further edits once "invoiced" with a date entered.