I thought I understood this a few days ago, but now I’m totally confused. Hoping somebody can straighten me out.
I come to Coda with very long experience with more conventional database design – where I define the primary and foreign keys in tables, build the relationships based on those keys etc, and where also I have the ability to script the entry of values. In the world I’m used to, there’s a difference between a true related value and a “looked up” value. (I put “looked up” in quotation marks because I am not sure that this is how Coda uses the term “lookup”.) So in my experience:
- A true related value is dynamically linked to a child table from the related parent table. If the value in the parent table changes, the value displayed in the child table will be updated automatically. For example, say Jane Doe in the PERSONS table is linked to Acme Inc in the COMPANIES table. If Acme changes its name to Acme International Ltd, Jane Doe’s person record will automatically show her employer as Acme International Ltd (the new value).
- A “looked up” value on the other hand is not dynamic. It’s a static, one-time data grab. Used for example to get the prices of items being sold, when the prices of those items might change later. So if on January 1 the price of a size 6 widget in the PRODUCTS table = $1.25, and I create a sales invoice for the purchase of some size 6 widgets, the item cost will be looked up as $1.25. But if the prices are changed in February, any new invoices for size 6 widgets will reflect the new price – while the old $1.25 price in all the old line-item records will be not be changed.
I cannot figure out how to do this second thing. Please help!