modify calculation for sales tax increase

[muscovite]

I inherited a database to track departmental requisitions including a calculation to add sales tax to the sum of subtotal and shipping. The local sales tax increased from .078 to .079 the first of April. I would like to modify the calculation such that it calculates the correct sales tax going forward without changing the previous records. Can someone help me with the syntax? I am a machinist, not a developer.

The starting DEPT_REQ# for the new tax rate is 11507.

This is the existing calculation: Unstored,=Let([@Prior=If(  GetAsNumber(DEPT_REQ#)>10980; Shipping+Subtotal; Subtotal)]; If(IsEmpty(Override);@Prior*.078; Override))

[Ocean West]

I would never hard code tax rate in to a calculation but rather in a tax rate table and a date it is in effect and lookup that rate when at the time you record is created. 

[comment]

I absolutely agree with @Ocean West about not hard-coding tax-rates into calculation formulas. Tax rates change over time, and you will find yourself modifying the formula again and again.

Since you rate yourself as a novice, you might just add a TaxRate field to your table, populate it with the value of 0.078 in all existing records, and make it auto-enter the value of 0.079. Then change the calculation formula to use TaxRate instead of .078 .

Make you sure to have a backup before you try this, in case you make a mistake.

At the next tax rate change, simply change the auto-entered value.

 

 

 

Edited April 7, 2022 by comment