On Thu, Jul 14, 2011 at 3:20 PM, Geoff Graham <[log in to unmask]> wrote:

Here’s a random lame example:
>
> Take an Invoices table with a child table of line items. The line items has
> Price_Shipped_Extended, which is the unstored final price of each line item
> record. (something like: (Qty *Round(Wholesale * (1 – Discount); 2)). When a
> yearly report on a  particular item or account is run, given a few hundred
> thousand line items, it will be slow to total up. The fix is conceptually
> simple: make Price_Shipped_Extended a stored value. Let's say I tried, and
> FileMaker won't let me store it. I realize that "Wholesale" is simply the
> price from the Products table being pipelined directly into line items. The
> fix in that case is to make a new "Wholesale" field in the line items file,
> and copy the price from the products table when the line item record is
> created, or at the time a product is entered in. Since the price (and Qty
> and discount) are now stored values, the extended price can now also be
> stored, for possibly a 10X speedup in reporting time.
>


In general, I recommend ALWAYS using lookups to obtain prices when
calculating the cost to a customer. That way you have a permanent record of
the price quoted. If you rely on a dynamic relationship to get the price,
you not only slow down the calculation process (a concern mainly to database
geeks like us), you also introduce the possibility that the price will have
changed between the time the order is placed and the time it's shipped,
which invariably leads to unhappy customers.