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.
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.
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 ).