3 Replies Latest reply on Jun 17, 2010 3:07 PM by philmodjunk

Tax calculation question

Title

Tax calculation question

Post

Fairly new user of FileMaker Pro, trying to make changes to a database.  Using version 10, but some of the computers on the network are running version 8.5.

This is probably a very simple question about a calculation in a field.  I am trying to get a value for Provincial Sales Tax (PST) that is conditional upon province and Delivery Date.  If the province is Quebec, then the PST needs to always be the Sub Total + GST * 0.075.  If the province is Ontario, the PST needs to be the Sub Total * 0.08 if the Delivery Date is < June 1, 2010.  If the Delivery Date is after June 1, 2010 then the PST for Ontario needs to be 0.

This is what I have so far, which in my mind should work:

If ( Exact (Site Province ; "Quebec") ;
(Sub Total  + GST) * .075 or If (Exact ( Site Province;"Òntario" )  and (Delivery Date < Date ( 7; 1; 2010)) ; Sub Total * .08); 0 )

For some reason it gives me a value of 1 for PST in Quebec.  It seems to work properly for Ontario.

I'm sure I'm just missing something simple, but I just can't see it.

Thanks

• 1. Re: Tax calculation question

Or is a logical operator.

If the term on either side is true (not zero), it returns true ( the value 1 ). So the only possible results for that part of your expression are 1 or 0.

We could go further with the calculation but there's a better approach.

Set up a table, TaxRates, with at least two fields: Province, Rate

Relate this table to the table where you have the expression you've posted by Province.

Define a number field, TaxRate, and set up a Looked up Value field option on  it to lookupTaxRates::Rate.

Now the expression: (SubTotal + GST) * TaxRate will compute the correct value for both provinces and any others you might have. To handle a tax rate change, you can either include an effective date that is part of your relationship, or you can simply wait until close of business the day before the change and edit the Rate field for that province.

• 2. Re: Tax calculation question

The problem is that we have two taxes, GST and PST.  In Ontario, the two are being replaced by a new tax, HST.  Quebec  will still keep GST and PST.  I need to apply GST and PST in Ontario before July 1, 2010 and only HST after that.   Will I have to have 3 fields (HST, GST, and PST) for each province in order for this to work?  And how do I get the look up value to change after July 1?

I'm still pretty new to FileMaker so please excuse my ignorance and thank for the help.

• 3. Re: Tax calculation question

Yes, I'd use at least three fields in the look up table. You can even place the calculation expression in this table, look it up and use the evaluate function to use the looked up expression to compute the tax if necessary.

how do I get the look up value to change after July 1?

I answered that in my last post. You can either wait until close of business last business day prior to July 1 and then update your record for this province or you can add effective dates to your table.

If you use effective dates, your relationship for the look up will look like this:

Invoice::Province = TaxRates::Province AND

Invoice::cToday < TaxRates::EffectiveDate

cToday is an unstored calculation field that returns a date: Get ( CurrentDate ).