Think of how your invoice system manages any price changes. The same issue applies there as with this data from your customer table and the solution is the same in both cases.
Using the customer example only, You can define corresponding fields in your invoice table to hold this data from the customer table. Assuming that you have a relationship linking the record in customers to your invoice record, either looked up value or calculation auto-enter field options can copy data from fields in the customer record into corresponding fields in the invoice record once you select a customer record and link it to your invoice record. Since this is a copy of the customer data, changing the original copy in the customer record will not change the copy of it made in the invoice record.
I frequently point out to people setting up an invoice system that most businesses will want customer contact data to be copied to each invoice record so that there is no confusion over what data was used for billing and shipping at the time the order was processed. You need to be able to see the actual info in place even if the data was later changed in order to resolve any issues over a customer not being billed or not receiving a shipment.
"I'm sorry sir, but that invoice still had your old office's shipping address as we did not receive your change of address notice until after that order was processed and shipped...."
But that's my point. I have one calculation auto-enter field that uses data from my customers' table and it is not a copy of data. I don't know how to acutally make a copy. Once I change data in my customers' table the invoice is affected. Could you please advice how to make such copy of the data from customers' table?
If it's an auto-enter field, it IS a copy of the data and changes in the customer table will not change the data shown in the invoice.
If, on the other hand, it's an unstored calculation field referencing this data, it will update when the referenced customer info is changed.
Forgive me my lack of knowledge...I am very beginner :)...
So, if I use "Number" data field and then use Auto-enter/ Calculation value I got no data in my invoice table using the very same formula that with Calculation field :/
I am not able to spot the problem...
The very fomula (basicly that is a simple multiplication of two values from customers' table - which is in turn linked to invoice table via customer_id key) works great when using Calculation field and does not work when used as Auto-enter/Calculation value :(
Could you please advice how to deal with it?
Thanks in advance.
I got it! I had to recalculate my invoices after changing the field type. Now it works exactly as you said! Great :) Thanks a lot!
I will use the same mechanism for some other fields basing on what you said in your first post.