Check out the Looked up value auto-enter setting and also examine the line items table of the Invoices starter solution that uses this method to look up pricing info from the Products table.
A field with a looked up value setting, copies the value from the related table into the field. Since this data is copied, changes to the original value in the related table do not change the value, but new records will copy in the new value...
(and there are ways to use effectivity dates to control when a change goes into effect.)
That works great for the tax rates. Having the looked-up field *copy* its value is fantastic.
Wondering how it would work for the company/contact example, as that wouldn't be a looked-up field.
And ... do you have an example of using dates?
Here's a thread that discusses the two basic ways to make info appear automatically when you select a value that links it to a related record. Both rely on a relationship, but one is the looked up value method, the other is a dynamic link that will automatically display updates made to the lookup table. Which is best depends on the needs and prefrences of the user.
Company/Contacts is a classic case where different needs will take you to one method or the other. Some businesses need to know "what contact info was current when we created this invoice?" which tells you that copying the data into the invoices table is needed. Other businesses need to know "What's the right now current contact info for this company?" which tells you that you want a more dynamic link.
For Date controlled lookup settings, let's start with the Invoices Starter solution. You have a products table where a unit price for each product may be stored. You can only store one price for each product so you cannot make price changes before they go into effect and you cannot keep a record of previous pricing.
Add a Pricing Table with these fields:
To LineItems, add an unstored Calculation field, cToday, define as: Get ( CurrentDate ) and select "unstored" in storage options.
define this relationship:
LineItems::ProductID = PricingTable::ProductID AND
LineItems::cToday > PricingTable::EffectiveDate
In this relationship specify that the PricingTable Records be sorted by EffectiveDate in Descending order.
Now set up your looked up value field option to look up Price from PricingTable instead of Products.
Looked up value settings will copy the "first" related record when there is a match to more than one record. By specifying the sort order on EffectiveDate, we make sure that the matching PricingTable entry with the most recent effectiveDate that is the same as today or earlier is the record from which the price will be copied.
"Company/Contacts is a classic case where different needs will take you to one method or the other. Some businesses need to know "what contact info was current when we created this invoice?" which tells you that copying the data into the invoices table is needed. Other businesses need to know "What's the right now current contact info for this company?" which tells you that you want a more dynamic link."
... That's it, except I need to know both ..!
So in just reading thru the effective date solution, it looks like exactly what is required.
Thanx again Phil ..!!!!!
The two methods are not mutually exclusive. You can use both. You can also selectively update looked up value fields by editing the key field used to the define the look up relationship or in a batch operation on the entire found set via the Relookup Field Contents option found in the records menu.