To make rate and price changes that only affect new records use a related table and an auto-enter field option to copy over the rate or price. You can then change the value in the related table without the value changing in existing records.
thanks for your reply
Do you mean put all four fields in a related table or just 1.2,3 or 4 of them?
Let me give you a simple example and then you can see if it generalizes to your more complex issue.
Say you have this setup:
Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
Productds::__pkProductID = LineItems::_fkProductID
A Portal to line items is use to list items sold on a given invoice. When you select a product in the _fkProductID field on a LIneItems record, this links the record to a specific record in products. One of the items in Products is the unit price charged for that item. You could define a calculation field like this to compute the line item cost:
Qty * Products::UnitPrice
But then, if there's a price change, all your invoices will recalculate and the totals on them will not reflect the the actual purchase amounts because the calculations no longer use the unit price in effect at the time the transaction was completed. Sound familiar?
Define a UnitPrice field in LineItems. From Manage | Database | Fields, open field options for this field and set up either a Looked up value or a calculation to refer to the UnitPrice field in the Products table. This will copy over the unit price value from products into the line items table. If you later change the price for that product, the value copied over remains unchanged and all the price based totals calculated on your existing invoices remain unchanged. But when you add a new line item record to an invoice, the new price is copied over.
So any field in your calculation that is subject to such changes should be set up to copy over values in similar fashion. Note that you can copy over individual values or set up an auto-enter calculation that computes and stores a computed total. Both options can be the "best option" in different circumstances.