Calculation result depends on date query

[Gogargirl]

This must be so simple, but I cannot work it out or find the answer! Using Filemaker 14.

I have an old db in which my records are stock items. I currently have a number field showing the price net of tax (PRICE_NET) and a calculation field (PRICE_INC) which shows the price including tax at 10%, using the calculation PRICE_NET * 1.10.  I also have a date field (DATE) showing when the item was acquired.

Let’s say the tax increased to 15% at the beginning of this year. I therefore want to change the calculation in PRICE_INC so that it displays the net price + 10% if the date in the DATE field falls before 01/01/2016, and the net price + 15% if the date in the DATE field falls after 01/01/2016.

Assuming there may be another tax rise in the future, I would want to modify the calculation to include the date range when the tax stood at 15%.

How do I do this please? I want the prices to be always visible – so no scripts please – and I do not want to do this with look-ups.

[comment]

The correct approach to take here is to store the applicable tax rate in a Number field - not hard-code it in a calculation formula.

To protect your existing data, define a new field TaxRate (number) and populate it for all existing records prior to 01/01/2016 with the value of 0.1. Then change your PRICE_INC calculation to PRICE_NET * (1 + TaxRate). Finally, set the TaxRate field to auto-enter the value of 0.15.

This is still not the ideal setup, because changing the tax rate requires the intervention of a developer. Ideally, the current tax rate would be stored in a one-record preferences table and auto-entered from there.

[Gogargirl]

Thanks for your help Comment!

I think I've misled you, as I don’t need this task to conform to good accounting practice; it is for a history research project and I was trying to simplify my query to understand the basic calculations I would need.

In fact the ‘stock items’ are historical commodities and items bought at various prices, currencies and tax rates in the 18^th and 19^th centuries. I therefore do not want to have variables auto-entered as the records are created – because the records already exist. I want the tax rate  - or maybe the currency exchange rate, or anything else I may think of -  to change depending on the date in the date field.

 Can this be done please?

[comment]

I see. Well, technically you could hard-code the rates in the calculation formula, say:

Let ( [
rate = Case (
DATE < Date ( 1 ; 1 ; 1750 ) ; 0.1 ;
DATE < Date ( 1 ; 1 ; 1800 ) ; 0.15 ;
DATE < Date ( 1 ; 1 ; 1850 ) ; 0.2 ;
0.25 )
] ;
PRICE_NET * ( 1 + rate )
)

However, I would still advise against it. Not because it goes against good accounting practice, but because it goes against good database practice. The rates are data - and data should be stored in records and fields, not in calculation formulae.  If only because that way it's visible and can be easily corrected for data-entry errors.

I would add a table for TaxRates, where each rate would have a record with fields for FromDate and Rate, and have your items get the applicable rate from there via a relationship - either directly, or as a lookup. And I would do the same for the other parameters, such as exchange rates.

[Gogargirl]

OK - thanks Comment. I like your suggestion in the last paragraph and will give it a go.