Unwanted recalculation of calc field after first entry

[AndyCan]

Hi,

This is an extremely basic question and has to do with calculating a sales tax in the individual lines of a bill.

Basically i have my products table which defines whether a certain product is liable for sales tax and another 'base values' table which holds global variables of things such as the current sales tax rate, etc.

In the bill lines, i want to do a simple calculation of the sales tax once the user has entered the quantity and price of the product (price may vary between supplier and depending on seasonal demand, so i do not store it as a fixed value anywhere). With a calculation field there is no problem, this calculates the tax correctly. The problem is that if the sales tax rate should change in the future any adjustment i make in the base values table will change the tax value in all previous bills. Ie. it will recalculate the value for all tax fields.

My question then is how do i make the calculation value stick in the same way as lookups do?

Many thanks,

Andy

[Inky Phil]

You could put a 'date of last rate change' field in your base rates table and then use a case function to assess whether your tax should change eg the calc for your sales tax field would be along the following lines

Case(Date of invoice

That should do it (I think!!).

Phil

[color:blue]I haven't tried this out and I am a little tired and the more I look at it the more suspect it looks but give it a whirl cos I can't for the life of me see why it shouldn't work

Edited April 25, 2008 by Guest

[AndyCan]

Hi Phil,

Thanks for your response. Are there any other options though? Perhaps some way to replace the tax calc field with a static number field that doesn't get recalculated. Once a bill is entered and approved i would prefer that nothing touch it.

I understand your method, although i still feel like the field is vulnerable to change, ie. if someone were to enter the rate change date in the wrong manner.

Andy

[comment]

You should lookup and store the tax rate in the line item (or the invoice) record.

[AndyCan]

So obvious and so elegant! Thanks again Comment.. you are the answer king hehe.

I should have realised the answer myself, but i guess this flu that i have is clouding my head today :

Bye for now.

[AndyCan]

hmmm small problem here.

The base values table (with only global variables) does not appear in the drop down list for lookups because it's not connected to the bills lines table.

How do i get around this?

My first reaction would be to give the base values table a primary key with global value of one and add a foreign key to the bill lines table with an auto entry value of 1 for each record.

Is this necessary or is there a better way to resolve this?

Andy

[comment]

If it's a global field, then use auto-enter calculated value =

YourBaseTable::gTaxRate

You don't need a relationship to reference global fields.