How To Setup PA (Pennsylvania) Tax Rules for All Zip Codes - PrestaShop 1.6.1.4

[nnapier]

After doing a ton of research and searching on the topic of setting up specific tax amounts depending on counties, I found the following idea posted:

• Setup a different tax for each of the unique tax amounts.
• Create tax rules for each areas that charge the different amounts and use a zip code range.

So once you look up the zip codes you will see a very massive problem with the only approach that I could find:

• Zip code ranges do not work in any way as it's one zip code to another zip code and they are seen as simple numbers by the system.
• If a users zip code falls between the ranges then they are charged the specified tax.
• Zip codes are not perfect and never actually line up in proper numeric form as geography doesn't work that way.

The easiest way to fix the issue above would be for PrestaShop to change the range field to allow for comma separated zip codes. That isn't happening right now so I made my own way.

 

My requirements for the state of Pennsylvania were as follows:

• Allegheny County = 7%
• Philadelphia County = 8%
• All Other Counties = 6%

This is what I did to solve my issues (Attached is a screenshot of the excel of what it looks like):

 

* Warning, requires excel and phpMyAdmin knowledge (can access through cPanel) and probably will not be an option for hosted solutions. My solution was for PrestaShop 1.6.1.4 so this might need to be modified depending on your version.

• Create 3 taxes:
  • PA Statewide (6%) = Value: 6
  • PA Allegheny (7%) = Value: 7
  • PA Philadelphia (8%) = Value 8
• Create a Tax Rule called "PA NEXUS STATE TAXES (6 to 8%)," but feel free to name it how ever you want.
• Within the new Tax Rule view:
  • Add 3 associated rules, one for each of the taxes that you created above, by clicking the "Add a new tax rule" button.
  • Choose the associated tax (in this case the 6%, 7%, and 8%), set the behavior as "This Tax Only" and give each an easy to remember title as you'll need that later. 
• Go into phpMyAdmin by logging into your host backend and finding the link within cPanel or whatever UI they have setup.
  • Once you are within phpMyAdmin, locate your PrestaShop database and expand the table listing. The name of the database would have been chosen by you or your host.
  • Locate the table entitled "tax_rule" and open up the browse view.
    • Scroll to the bottom and find the tax rules that you just added.
• Search on google for "all zip codes in philadelphia county PA" and find a listing.
  • I ended up using the following, but the link might be dead by the time you see this:
    • http://www.zillow.com/browse/homes/pa/philadelphia-county/
  • Copy all of the zip codes into excel into the "A" column on the 1st row.
• Search on google for "all zip codes in allegheny county PA" and find a listing.
  • I ended up using the following, but the link might be dead by the time you see this:
    • http://www.zillow.com/browse/homes/pa/allegheny-county/
  • Copy all of the zip codes into excel into a second column.
• Search on google for "all zip codes in PA" and find a listing.
  • I ended up using the following, but the link might be dead by the time you see this:
    • http://www.unitedstateszipcodes.org/pa/
  • In this case it was a full table so I had to copy the whole table, paste into a second sheet, and then cut out just the zip code column and paste into a third column back on the sheet with the philadelphia and allegheny columns.
• Select all three columns and under the "Data" tab in the ribbon, select "Remove Duplicates." Depending on your Excel version, you might have to look up where to find this.
• Once you have removed duplicates (Excel will remove the secondary duplicates in the overall PA listing so that the values in the Allegheny and Philadelphia are still good), do the following:
  • Cut the allegheny column and paste the values below the philadelphia column with a few rows in between so that you can see the break.
  • Cut the PA column and paste the values below the philadelphia and allegheny combined column leaving a few rows so that you can see the break.
• In an empty column next to the now combined column, do the following in the first row:
  • ="INSERT INTO tax_rule (id_tax_rules_group,id_country,id_state,zipcode_from,zipcode_to,id_tax,behavior,description) VALUES ('54','21','38','"&A1&"','0','53','1','PA Tax - Allegheny County (7%)'),"
  • Replace the VALUES () area to match the column values for the associated tax within your "tax_rule" table in phpMyAdmin. Please note that for the first batch we chose Philadelphia so you want to have it match up with that tax rule.
• Now in the second row below the first one you just made, do the following:
  • ="('54','21','38','"&A2&"','0','53','1','PA Tax - Allegheny County (7%)'),"
  • Replace the area between the () to match the column values that you just chose in the first row with the only difference being the "A2" reference.
• Hover your mouse over the lower right hand corner of the cell that you just created until it becomes a black cross and left click and hold. Drag you mouse down to the end of the Philadelphia listing and let go.
• Repeat the steps above from the "INSERT" method for the allegheny and PA statewide listings until you have one big column of SQL value inserts that form one major insert query.
• Copy the SQL insert column values from the column to your clipboard.
• Open phpMyAdmin and click the "SQL" button.
  • Paste your big insert query into query field and click the "GO" button to execute it. This may take a few minutes as it can be a ton of values.
• Once the big query is complete then phpMyAdmin will notify you of the query success. If it fails then look at the error and figure out what happened. I had an issue with the column names so you might have to play with those.

Now hop into your PrstaShop backend and check the tax rules listing and make sure that all of the new zip code values are there. Feel free to delete your first 3 example rules that you created as all the other values you entered will now overwrite them. Test the result in your cart to make sure that taxes are being applied properly.

 

This option is a few minutes of work, but it should work for any state as long as you can find all of the zip codes and figure out which zip codes to tax at what rate.

 

Thanks for reading and I hope that this saves others a ton of work in the end.

[The Fuzzy Pineapple]

Hi. I hope this helps. I figured out a way to add multiple ranges for the taxes. 

 

For example I am located in florida. There is a 6% tax for the state and a 1.5% surtax in my specific county = 7.5%

 

So I made a new tax (1.5%) as seen here:  and named it the same as the 1st florida tax (6%) + surtax (1.5%) as seen here:  and here 

 

So there are 2 taxes now - Florida - (6%) and Florida + Surtax (1.5%) 

 

*If you have multiple counties that you operate out of, then you will need to make a surtax for each one. (Don't have a way of getting around this, I only have one shop) Ex. Florida (6%), (county) surtax (1.5%), (county 2) surtax (0.5%), etc.

 

Next I went to tax rules and selected the florida tax (6%) and configured it as seen here:   . I then renamed it so I would know that the new florida sales tax also has a surtax added for specific zipcodes as seen here: 

 

The configuration is as follows (first set everything to combine then change the state tax back to "this tax only")

 

 

Florida - do not enter zipcodes - combine- 6%

Florida - zipcode range - combine - 1.5% (surtax)

Florida - new zipcode that is not in the range - combine - 1.5% (surtax)

Florida - different zipcode that might be a different range- combine - 1.5% (surtax)

 

Finally change the first Florida (the state tax that applies to the entire state) to "this tax only"

As follows:

 

Florida - do not enter zipcodes - this tax only - 6%

Florida - zipcode range - combine - 1.5% (surtax)

Florida - new zipcode that is not in the range - combine - 1.5% (surtax)

Florida - different zipcode that might be a different range- combine - 1.5% (surtax)

Done!