Adjust sales tax calculation without affecting previous records

[mrswombat]

Hi,

I currently have the following calculation for sales tax in my layout: "Round(If ( Tax Flag = "Yes" ; ${Sub-Total} * .0875; 0 );2)"

Our tax rate decreased by 1% effective July 1st. I searched through this forum and couldn't find anything that quite answered my question. What I want to do is change the current calculation to specify that any records >= 7/1/11 will reflect the new tax rate of .0775, but I want to keep my old records (< 7/1/11) at the old tax rate. I've tried a Case calculation but couldn't get it to work. Please help and thank you!

[comment]

What I want to do is change the current calculation to specify that any records >= 7/1/11 will reflect the new tax rate

No, what you want to do is find all records before July 1st and enter .0875 into a TaxRate field. Then set this field to auto-enter the current rate from a global field or a preferences table.

[mrswombat]

No, what you want to do is find all records before July 1st and enter .0875 into a TaxRate field. Then set this field to auto-enter the current rate from a global field or a preferences table.

Thank you for the quick response. As you can see, I'm a total newbie, so forgive my confusion. I'm not quite understanding what you are saying. Would you please elaborate (spell it out) just a little bit more? Thank you so much for your help.

[comment]

I am saying that tax rate is DATA and should not be stored in a calculation formula. This is not the first time a tax rate has changed, and certainly not the last time. The correct solution is to keep the current rate in a global field and let each sale look it up from there at the time of creation.

[mrswombat]

I created another table with the different tax rates as two different fields. I currently have them as number fields set to auto enter data as the tax rate that I want. (I don't think this is correct, but I'm not sure how else to give these fields a numerical value!)

I defined a relationship from my new Tax table to my original table and matched up the fields (my original sales tax field to the "old tax rate" and "new tax rate" fields in my Tax table). I changed my sales tax field to a number field to auto enter the following calculation: Round(If (Tax Flag = "Yes" ;${Sub-Total} * Tax::Old Tax Rate; 0 );2)

This still doesn't seem to work and I think I'm waaay off what I'm supposed to be doing. I still need to figure out how to then make the Sales Tax field work such that it doesn't change all my records on and before 6/30/11. Would someone please help me out with this? I'm afraid I'll need a play by play of how to do this. Thank you ahead of time.

[Ocean West]

you should only have ONE tax rate field - the value in that field will be the tax rate added to the field at the time the record is created.

The calculation will remain the same. when the tax rate changes all you need to do is change the value that is auto set in the field for new records from some date going forward.

once you change the field to a Number field you may have to find all records < a 7/1 date and then do a replace in to that field with the OLD rate .0875... and then replace

the value with records ≥ 7/1 the rate of .0775 or what ever your current rate is.

[mrswombat]

I deleted the second field as you said. I can't get anything to show up in my sales tax field though. I suspect I'm missing something very basic here. Right now my calculation looks like this: Round(If ( Tax Flag = "Yes" ; ${Sub-Total} * Tax::Tax Rate; 0 );2). I replaced the actual tax rate with the field containing the tax rate and it seems to me it should pull the info. from that field but it's not. More help please? :unsure:

[mrswombat]

Hmmm, I think it could be a problem with the relationship. I'll keep working at it, but any tips are appreciated!

[comment]

See if this helps:

TaxRate.zip

[mrswombat]

This helps immensely. Thank you, thank you, thank you!!! :D

[comment]

Here's another option. Note the sort order of the relationship.

TaxRates.zip

[mrswombat]

Wow...thank you again! I guess it got to a point where it was just easier to do it than type out a long explanation huh? Thanks again...

[comment]

easier to do it than type out a long explanation

That happens often.

[mrswombat]

Just want to report that my situation has been resolved and everything worked / is working beautifully!! Thank you sooooo very very much again!!! :woohoo: