Well, the fields may look redundant but they really aren't. When you need to capture a "snapshot" of the data in a related field you aren't just making a copy of the data, you are preserving the value of that field at the time you needed that value because future changes to the value in the related table are not relevant and will produce incorrect results. The classic case where this is done is for unit prices looked up from the inventory table. You don't want a price change to affect the data on past invoices so you copy over the current unit price at the time you choose a product ID in the line items table.
Often, businesses also want client contact data such as a billing or shipping address to be copied as well so that they can be sure to accurately handle issues that may arise when a client complains that they didn't receive a shipment and a review of the invoice record reveals that the shipping address used differs from the client's current address due to an update of their contact info that took place after the order was placed...
Choosing between looked up (copied) and linked data in your database design is thus one of the standard design decisions you have to make and are usually driven by the needs of the client.
In addition to the auto-enter calculation that can be used to copy over the values, a looked up value field option in the same dialog can be used. It's a touch more cryptic to set up, but enables you to use the "Relookup" menu command or script step to force FileMaker to copy over a new value from the related table--which can occasionally be a useful feature.
Yes, adding in these fields will increase the size of your file by a bit, but hard drive capacity these days is measured in gigabytes so the size increase isn't likely to be a major issue for your file.
Thanks Phil, I thought that might be the case and appreciate your confirmation.
ON a side note, does it make a difference if I create a LInes::notes field, or a Inventory::notes field to be placed in the Lines Portal.
This field would be for adding new general information for each product. If it were housed in the Inventory table, the field would only appear in the Inventory Repair history - which is created through creating a work order as well.
My work Order table ->Lines is for creating employee rentals mostly, but I can also use the work order to create a repair work order as the w/o would pull contact info from the contacts table as well.... same thing just a different contact::job_category
I created a LINES::notes field and a INVENTORY::notes field both for this same purpose. I place one at a time in the LINES portal, and the same corresponding field in the INVENTORY repair history portal layout, and could not see any difference.... at lease none that I was aware from my brief testing....
does it matter ?
It makes a difference. A notes field defined in Lines will be specific to that line item record on that order. The notes field for product will be specific for all products ordered with a given Product ID. I would only define a notes field in Line Items if at least one of the following is true:
a) Future changes to Product::notes cannot be allowed to change what is shown for notes in a portal to Line Items.
b) The user needs to be able to edit the notes field in the LIneItems portal to create a note specific to that order.
cheers!!! Makes sense.