Calculating Vat values based on Date

[jdunne]

Hi, I'm trying to use a new Vat rate (23%) for new Pricings from the 01-Jan-12 while keeping the old Vat amount for Pricing before that.

I'm using Filemaker Pro 6

I have a "Customer Creation Date" field that adds the current date when a new record is created.

I have an existing "Vat Amount" field that just calculates the vat value for 21% using "Cost Price" * .21 then adding "Cost Price" to "Vat Amount" to get the "Total Price"

Cost Price

Vat Amount

Total Price

I want to keep entries up to the 31-Dec-11 showing the 21% Vat Amount value and any entries from 01-Jan-12 to calculate the 23% Vat Amount

I've created 2 new Vat fields, "Vat Amount 23" and "VAT"

The existing "Vat Amount" field will be used for vat values before 01-Jan-12

The new "Vat Amount 23" will be used to calculate the new 23% Vat rate (Cost Price * .23) from 01-Jan-12

The new "VAT" field will now replace the original "Vat Amount" Field where either a CASE or IF statement will be applied to reflect the Vat value depending on the Record Date.

Cost Price

VAT

Total Price

I'm probably approaching this completely wrong but below is what I entered as a calculation in the VAT field

So as a calculation in the "VAT" field I've entered either

Case(Customer Creation Date <= 31/12/2011, Vat Amount, Customer Creation Date >= 01/01/2012, Vat amount 23)

or

If(Customer Creation Date <= 31/12/2011, Vat Amount, Vat Amount 23)

However none of these work and all that's returned is the Vat value for 23% no matter what date the record was created

Thanks

[comment]

The best way to handle this is to create a field for the VAT rate and populate it with the value of 0.21 for all existing records. Then set the field to lookup the rate from a table of VAT Rates, matching on date, copy next lower value.

You could also just auto-enter 0.23 - but then you must be sure you'll fix it at subsequent rate changes.