Canada Tax - date comparison - scripting

[buckbuck]

On April 1 2013 the tax regime for businesses in British Columbia changed. Previously a single tax (HST) was applied to sales. April 1 the tax split into two, GST and PST. Further complicating this, Canada is a federation of Provinces and Territories and each has its own tax system. For example, the Province of Ontario uses HST whereas Alberta has only PST. So, if you sell to Alberta you charge PST only.  HST is a harmonized federal tax whereas PST is Provincial Sales Tax.

 

I am trying to write several scripts to calculate the payable tax. To start with British Columbia.

 

I have a field that shows the federal tax type payable , either "HST" or "GST". 

 

The Tax Field Type 1 script I wrote goes as follows; calculation, text result;

 

 

Case ( 

(Date_Ordered  < Date ( 4 ; 1 ; 2013 ) and CONTACTS::StateCompany = "British Columbia") or

(Date_Ordered  < Date ( 4 ; 1 ; 2013 ) and CONTACTS::StateCompany = "BC"); "HST"; 

 

(Date_Ordered  ≥  Date ( 4 ; 1 ; 2013 ) and CONTACTS::StateCompany = "British Columbia") or

(Date_Ordered  ≥  Date ( 4 ; 1 ; 2013 ) and CONTACTS::StateCompany = "BC"); "GST"

)

 

This field result should toggle between HST and GST accordingly. I then use this result to extract a tax rate.

 

 

Case ( 

Tax Field HST GST 2013 = "HST"; .13;

Tax Field HST GST 2013 = "GST"; .07

)

 

And this to produce a Tax Amount HST GST;

 

Case ( 

Tax Field HST GST 2013 = "HST"; Amount Net * Tax Field Rate_HST 2013; 

Tax Field HST GST 2013 = "GST"; Amount Net * Tax Field Rate_HST 2013

)

 

 

I am not getting the result I expect. Any ideas why?

Once I have this model I intend to enlarge the scripts to include all the other Provinces.

 

 Thanks

 

[Rick Whitelaw]

Unless you are doing thousands if transactions why not simply have a tax rate per transaction and enter it manually? BC doesn't expect you to split the taxes do they? I believe the total should suffice. Otherwise you'll be updating calculations each time any province changes its tax laws.

[buckbuck]

Thinking about your suggestion I would be required to know all the tax rates for all 17 tax jurisdictions. Not that I sell into them but what if. Thanks

[Lee Smith]

I would be required to know all the tax rates for all 17 tax jurisdictions.

You're going to need that anyway, aren't you?

[buckbuck]

If I script this function based on Provinces the rates will apply automatically. Yes, I need to account for all the Provinces but only once. Updates might need to be affected to the framework if rates change but this should be easy enough.

[Lee Smith]

http://sbinfocanada.about.com/od/invoices/a/How-To-Invoice.htm

Is this table accurate? If so, why not create a table for looking up the rates?

[buckbuck]

Sure this is reasonably efficient. It is still seventeen tax regimes. Anyway I thought automation was one of the benefits of Filemaker - the calculation engine, scripting ect. Why not script the whole thing since every invoice has what is needed - an order date, a buyer province and a tax rate.

[Lee Smith]

Whoa, I was asking so that I would be using accurate data for a sample. I have appointments most of the day, but I'll try to throw something together later, if someone else doesn't beat me to it.

[buckbuck]

I seem to be working it out. I will publish my result once I am sure it is correct.

[Lee Smith]

That's great! Learning by doing is always a good thing. Sorry I didn't get back to you yesterday, I got tied up in a meeting that took longer than I expected.

 

If you run into a snag, post your file and we will help you out.

 

Good Luck

 

Lee

[Rick Whitelaw]

I followed the link Lee provided. It seems to indicate that the tax rate used is the tax rate of the province or territory from which the invoice is issued and not the destination. I'm from Canada and this surprises me. However, I have provided services in Ontario for clients in Chicago and invoiced to Chicago. I charge the appropriate tax for Ontario. HST. I'm not shipping a product however. I recently paid for the services of a company in USA and was charged no tax whatsoever. If that company was in Ontario I would have paid HST.

My two cents . . .

[buckbuck]

In Canada, if I am in Ontario and the goods are bought by an Albertan client, the tax rate is GST only. Ontario to Ontario, HST, Ontario to British Columbia, GST. British Columbia to British Columbia, GST + PST. Hope that sort of makes sense.