Locking Calculation Results

[supermansmama]

Please help. I am totally a beginner FM database designer for my non-profit org and have come across a problem which I feel may be easy to fix but I cannot find the answer anywhere so far.

I have a member database which includes a Transaction table which creates and stores invoicing info for our customers/members. The problem is with our Tax field in this table. This year, Canadian gov't decided to decrease our tax from 6% to 5% so I did so with the tax calculation, however all previous transactions (pre2008) changed as well and although we do not need to print this old info anymore, it is annoying to see the incorrect tax for a prior transaction. Also, you never know if someone will need a replacement receipt from a year back and if the tax is changed to the present rate, the receipt won't be accurate.

So my question is:

how can I 'lock' a calculation field so that the result of a calculation remains when the equation is later updated???

I hope my scenario makes sense. Feel free to ask as many questions as possible if it is confusing.

Any advice is greatly appreciated!

[Fitch]

Sounds like you'd want to use an auto-entry field rather than a calculated field.

Or you could script the tax calculation, e.g. if you're already running a script to update your transaction totals, this could be part of that.

If you do decide to stick with a calc you could make the date part of it:

Case( TransactionDate < Date(1;1;2008) ; .06 ; .05 ) * TransactionSubtotal

[supermansmama]

wow that first part of ur reply was quite confusing since i have absolutely no training in FM. But the case formula makes alot of sense. I would like to leave it as a Calc formula since I assume its the easiest.

I'm going to try to apply ur example formula to mine. I'll let u know how it goes.

[Fitch]

I'll be happy to try to clarify if you tell me which part was confusing.

[comment]

the case formula makes alot of sense.

Will it still make sense after the tax rate is changed again? And again?

Tax rate should be looked-up or auto-entered into a field in Invoices, and stored there - not hardcoded into a calculation.

[supermansmama]

BIG BIG THANK YOU TO FITCH!!

I liked the idea of using the transaction date but I realized using Batch number would be easier for me since I wasn't sure how to present the date within the calculation.

I simply inputted the following:

Case (Product = "Donation"; 0; Invoicing::BatchNum < "0800"; Price * Quantity * .06; Invoicing::BatchNum >"0800"; Price * Quantity * .05 )

Just thought I'd show off my beautiful equation LOL

I love this stuff. I really should learn more.

Thanx again,

L

[supermansmama]

Will it still make sense after the tax rate is changed again? And again?

Tax rate should be looked-up or auto-entered into a field in Invoices, and stored there - not hardcoded into a calculation.

I totally agree. For now it'll do. If I do as u say will I have to redo my tables a great deal??

Like I said before, I should really dedicate more time to learning the proper ways to do this.

In the meantime, you are all a great help!

[comment]

will I have to redo my tables a great deal??

Not really. You need to add two fields:

gTaxRate - Number, Global*

TaxRate - Number, Auto-enter a calculation = gTaxRate

and change your formula to use the TaxRate field instead of .05 or .06.

Then find the records before the rate change and replace field contents of the TaxRate field with .06. Find omitted and do the same with .05. Enter .05 in the global field and you are done**. Be sure to have a backup in case...

---

(*) If your solution is served, then you need to use Preferences table instead of a global.

(**) You have other issues, such as relating to a product by name instead of ProductID (what if the product name needs to be changed?), and hardcoding the tax liability into the calc (the same thing). Usually, you would lookup the product's Taxable status from the Products table along with other details, such as price, and calculate the tax in the invoice once for all taxable products.

Edited May 2, 2008 by Guest