better to reference a field or using field/lookup?

[overrider]

Hello all,

Example: I have an Item Database referenced by a Purchase Order Line Items File. In the Line items File i dont really need any other Info than Item ID and Price to be actually existing. I still want Users to see Item ID -- Category -- Sub Category though when they browse the Line Items. Should i make a new Field in Line Items via my Relationship between Line Items and items, or should i actually create a new Field and make a Lookup via the Relationship?

For me it seems to be more clean if i make a field using the relationship, so basically only displaying the fields content rather than actually looking-up the data. But on the other hand i feel look-up is a one time thing, while the other way is constantly referencing the item file. My main Question being what People think which way would perform better when there are a lot of Records.

regards,

Overrider

[Ender]

I'm sorry, what was the field you were wondering about? What table is it supposed to accessed from? And are you planning to perform finds on this field?

[transpower]

It's cleaner to show the field making use of the relationship, rather than using another field and looking up the value.

[Ted S]

A Purchase Order is a historical record, right? So one would assume that it should reflect what the customer actually ordered.

If you have a business with a dynamic product line it is possible for an item's description to change periodically as time marches on. I would think that most business would see minor changes if any but neverless there could be changes. Maybe it's something as simple as now the batteries are Ray-o-vac brand whereas they were Eveready when the customer placed the original order.

If asked by the customer a number of years later what the exact description was for an item he ordered could you be certain by looking at your PO file if the line item descriptions were coming from the live item master table?

I don't see this is a technical issue, to me its more about a historical accuracy.

[overrider]

sorry, i knew i did not explain well, since i am not native english speaker. i made a small example file to make clear what my point was.

it is to be remembered that my question wasnt situation specific, it can be a purchase order line items file, a notes or history file for employees or whatever file which needs to display data from another table.

transpower: i think you understood my question, and you say it is cleaner, but is it also performing better?

regards,

overrider

example.fp7.zip

[transpower]

Overrider: I'm guessing better performance, but I don't have the data to back that up...

[Ender]

In previous versions, duplicating parent fields in child files was a good way to improve performance for Finds and Sorts on those fields. With FM7, this is not as big an issue. Finds on related fields now perform pretty good. In a local file with hundreds of thousands of records, a find in a related field may take 5 seconds, or on a remote file (with Server hosting,) that find may take just a few minutes. This is a huge improvement over previous versions.

Sorting on related fields performs okay on local files, but for WAN situations, sorts are significantly faster on local fields (though still pretty slow.)

The disadvantage of using copies of parent fields in child tables, is that the file size increases, making maintenance and backups take longer, and somewhat degrading performance of the overall database.

So in general, it is a good practice to use the related fields for displaying info on screen or in reports, and for most Find and Sort operations, using the related field will be okay.

The big exception to this is when the design requires you track history of what the value was at the time the record was created. This is especially important with Invoice and PO Line Items, where the price of a product (referenced from a Product table,) may change over time. In this case, since it is important that the price on the Line Item remains unchanged, even if the price in the Product table changes, a static Price field must be used in the Line Item table. For convienience, a lookup or auto-entered calc can be used to populate this Price when the Product ID is entered for the Line Item.

You should always consider each field in your solution, and determine if the value can change, and if so, is it necessary to keep a history of what it was for the related tables.