Local Sales Tax Mod - Support Thread

[patternman]

What is the best way to set up the database to use and require Zip+4? The State of WA is telling us that because of the multiple taxes within each Zip that the ZIP+4 is the only proper method to use.

Also, there were questions/comments within the thread that asking about linking to the State of WA's database to check. Has anyone been able to do that?

Indeed, I am also in the State of WA.
At first I just wanted to use the first 5 numbers - and the solution worked. But I trashed that idea eventually because it wasn't really accurate.

I have no idea about a mod to link to the state's database, but that would sure be helpful.

So I used the excel sheet from the dor for the ZIP+4 rates (short version).
I then had to do heaps of work to get the zip codes into a list of ranges - to cut down on the size of the whole thing. This saved a lot of space, but the list is still quite huge.
Then had to get it into the format to use with this mod (which has been very nice). Then uploaded it using Access. Here are the column headings that I used:

local_tax_id should be numerically sequenced 1 thru ...
zone_id should all be the same for the WA zone -- 62
local_fieldmatch should all be the same -- postcode
local_datamatch should be the list of zip code ranges
local_tax_rate should be the list of local rates formatted as 2.1 instead of 0.021.
local_tax_label is the list of city names
local_tax_shipping should all be the same -- false
local_tax_class_id should all be the same – 1

One of the tricks to using the ZIP+4 is that the customers need to give you a ZIP+4. So I am using the Post Office CASS Address Verification module:
http://www.zen-cart.com/forum/showth...987#post659987

My setup works quite well. You can check out my store at www.fashionpatterns.com. There is a small delay at checkout when the correct tax code has to be found.

I went ahead and dumped my local tax table and am posting it here in case anyone would like to have it.
http://www.fashionpatterns.com/downl...tes_090410.zip

I have been reluctant to post this because I offer no real help or guarantees of it working correctly. Nor am I ready to offer updates every three months when the state changes their rates. But if helps, go ahead and use it.

-Jason A.

[techdog]

OK Thanks. I've checked out the CASS Address verification and it looks easy enough. Question though - how do you upload the the information using Access? Do you just import it into the mysql database? I don't use access but I can create csv, xml, excel, etc. files using FileMaker Pro. I've just never thought of how to upload the information from outside of the initial interface.

Thanks again.

Karl

[patternman]

OK Thanks. I've checked out the CASS Address verification and it looks easy enough. Question though - how do you upload the the information using Access? Do you just import it into the mysql database? I don't use access but I can create csv, xml, excel, etc. files using FileMaker Pro. I've just never thought of how to upload the information from outside of the initial interface.

Thanks again.

Karl

Hi Karl,

Using MS Access was just the convenient tool for me at the time. If you have a better or easier way to do it, please do that instead.
I've attached a pdf file for using Access to do this. I wrote this for myself so that I could do it again if needed (because I will surely forget).

Cheers.
Jason

[juills]

Thanks for the Local Sales Tax Mod! It makes the sales tax issue simple! Question for you.

I am using the local sales tax to search the suburb field. (I'm in OH, so I use the suburb field for counties). Whether i use Store Pickup or Shipping Address, multiple lines for the same tax are displayed on the screen. (Please see screen shots).

Any suggestions on how to resolve this issue? Thanks!

[LadyHLG]

juills,
I'm not sure where the issue is, the mod appears to be functioning correctly. 7% sales tax on $2.75 is $.19.
Those lines above are only for debugging to show which records are zone matches.

LadyHLG

[juills]

juills,
I'm not sure where the issue is, the mod appears to be functioning correctly. 7% sales tax on $2.75 is $.19.
Those lines above are only for debugging to show which records are zone matches.

LadyHLG

Is there a way to turn the debugging off?

[LadyHLG]

Modules -> Order Total -> Local Sales Taxes -> Debugging is Active = False


LadyHLG

[Bonkycat]

Indeed, I am also in the State of WA.
At first I just wanted to use the first 5 numbers - and the solution worked. But I trashed that idea eventually because it wasn't really accurate.

I have no idea about a mod to link to the state's database, but that would sure be helpful.

So I used the excel sheet from the dor for the ZIP+4 rates (short version).
I then had to do heaps of work to get the zip codes into a list of ranges - to cut down on the size of the whole thing. This saved a lot of space, but the list is still quite huge.
Then had to get it into the format to use with this mod (which has been very nice). Then uploaded it using Access. Here are the column headings that I used:

local_tax_id should be numerically sequenced 1 thru ...
zone_id should all be the same for the WA zone -- 62
local_fieldmatch should all be the same -- postcode
local_datamatch should be the list of zip code ranges
local_tax_rate should be the list of local rates formatted as 2.1 instead of 0.021.
local_tax_label is the list of city names
local_tax_shipping should all be the same -- false
local_tax_class_id should all be the same – 1

One of the tricks to using the ZIP+4 is that the customers need to give you a ZIP+4. So I am using the Post Office CASS Address Verification module:
http://www.zen-cart.com/forum/showth...987#post659987

My setup works quite well. You can check out my store at www.fashionpatterns.com. There is a small delay at checkout when the correct tax code has to be found.

I went ahead and dumped my local tax table and am posting it here in case anyone would like to have it.
http://www.fashionpatterns.com/downl...tes_090410.zip

I have been reluctant to post this because I offer no real help or guarantees of it working correctly. Nor am I ready to offer updates every three months when the state changes their rates. But if helps, go ahead and use it.

-Jason A.

Hi Jason,

Besides this amazing mod (thanks LadyHLG ) your solution is the best I have found for acceptable slave compliance to the WA state tax master . Yes, dramatic I know, but if you are in WA state you are feeling the pain! Streamlined sales tax my

I have downloaded and installed the local tax mod and the dor zip + 4 (your download isn't working) I will install the address verification mod in a few moments. I am hoping to copy your solution, but need to better understand what you did with the dor information and your instructions above. If your download is still available, I would greatly appreciate using it.

Your checkout is slick and accurate, and the delay is minimal! I think I may have an easy way to perform the updates every 3 months and would be willing to post them for sharing to anyone who uses your fix.

Thanks in advance for your time,

Lisa

[patternman]

Hi Lisa,


Thanks for posting in more than one thread, I had unsubscribed from this thread a while ago. I didn't really know if my little contribution was useful to anyone or not. I'm glad someone has benefitted. And sorry about the link. I don't know when I deleted it from the server. I figured it was so old that it probably wasn't useful to anyone at this point.

Before I begin the meat of this post, please let me just give thanks to LadyHLG and the many others who know a lot more about mods, php, and programming than I. None of this would be possible without the heavy lifting from the real programmers.

But I'd also like to be blunt: please understand that I am not an expert with this stuff.
I am trying to help out and am posting what I can. But I offer no guarantees.
Always make sure to do proper backups before playing around with your database.
And as I stated earlier, I am not really interested in posting updates every three months. If someone else would like to adopt my methods (and hopefully clean them up a bit) and offer regular updates, please feel free to do so. The updates aren't especially difficult; I am just not in a position to take on that responsibility.

For the sake of clarity, I would also like to say that I don't consider this to be an ideal solution to the WA state sales tax. It would be much better to have a real-time lookup tool much like a shipping rate lookup. So for anyone who might know of a working mod for this, please post your information on this thread: Washington State Destination Sales Tax API
http://www.zen-cart.com/forum/showthread.php?t=102788

Ok, sorry for the verbosity.
It was good timing to post right now. The end of the quarter is upon us, and that means some new tax rates. I don't have the new ones done yet. (I'm always a few days behind on it.)

First, I have restored the link to my previous tax rate upload.
http://www.fashionpatterns.com/downl...tes_090410.zip
But like I said, probably not that useful because it's out of date. It's just an sql file that can (hopefully) be easily imported into the your database.

Second, here's a link to my most current tax_rates_local sql file:
http://www.fashionpatterns.com/downl...tes_100403.zip
Same sort of thing as before. It's just an export of my current table. I have not attempted to re-import it from this sql file. So please be careful and do your backups if you're going to play around with this.

Third, I have attached my personal instructions for doctoring the csv data from the WA DOR.
These instructions are attached directly to this post. (It's small enough to be attached, so I don't have to provide my own link to it.)
excel instructions for WA tax data.pdf
Please keep in mind that these are just my own personal notes to help me update the data each quarter. There may certainly be easier ways to do things. I can also not guarantee these instructions to be error free. But they work pretty well for me.

Fourth, here are the links to my previous post and attachment for uploading the data using MS Access:
http://www.zen-cart.com/forum/showth...078#post727078
http://www.zen-cart.com/forum/attach...2&d=1242085524
Just so everyone knows, importing the data using this method requires a very long time (at least it does for me). Importing an sql file is much quicker and easier. I just don't have a good procedure / tools for converting the data to sql before importing. So there is plenty of room for improvement.

Fifth, I have uploaded my excel sheet from this last quarter – prior to import:
http://www.fashionpatterns.com/downl...ort_100403.zip
As you will notice, it is much larger than the sql file. I have provided it so that you can have a good example of what the data from the WA DOR looks like after I've finished doctoring it.

Since the timing is so close, I will go ahead and post my quarter 3 files after I've finished them.

Hope this helps!
Please let me know how it works out for you.

-Jason

[Bonkycat]

Thanks again LadyHLG for a Mod that WA state residents can't live without!

Jason- Thanks for sharing! What you have started may be a great jumping off point for all of us caught in this issue. Let me know if you already made the July 1, 2010 changes.

DISCLAIMER: I am NOT a programmer. Not even close. Without Zen Cart and the great contributions, help, and easy to follow instructions, I would not be building a website.

I took patternmans tax_rates_local sql file (export of his current table) and had a php savvy friend break it into parts and turn it into something that can be uploaded to the tax_rates_local sql file through my php admin by starfield technologies. This is something that godaddy uses, so if you have godaddy and zen, this will work for you. (should work for everyone with starfield tech and possibly other sql control panels)

Download the sql file here: http://www.gemni-gems.com/sql.zip

Go to my php admin, click on your database name on the left hand side, scroll down and look for the table called tax_rates_local (click to highlight). Click the import tab, use the browse button to select file 1 (upload these in order and don't lose track) select SQL under the 'format of imported file' and click GO.
Continue until all are loaded. When complete, you should have 60,980 records entered.

I see it also allows selection of csv, so someone smarter than I should be able to download the WA state CSV file each quarter and load that in without the extra steps. Please share if you figure it out.

Still working on an easy way to update patternmans file each quarter.

*note: (62) is the number that is given in these files for WA state in your database. If you upload the files and don't show any local records when checking via your zen cart admin, you will need to go back to your php admin and correct that in the table.

Good Luck!