Over 3000 products now need to include VAT - An easy way?

[bridge32]

Helo all,
We currently have over 3000 products and have now registered for VAT.
I have to now include vat on most of our products.
At present we have our products set up like this:
Tax Class: -None-
Products Price (Net): £1.99
Products Price (Gross): £1.99

Now what we want to do is keep the Gross price the same, but have the Net price exclusive of vat. (£1.66)
I understand to reach this figure you would use £1.99 / 1.2 = £1.66 but how would i do this for all Taxable Products quickly? Would there be an SQL script / patch i could use?
The Taxable Products ID is 1 and Non Taxable Products ID is 2.
I also need to have the non taxable products set as Non Taxable Products (ID 2) which would leave the net and gross price the same.

I have tried to use easypopulate, but there is no net / gross column. I saw the Taxable Class Column, but even when i change this to say "Taxable Products" as per Class and then import, it does nothing.
Could someone help please and say me hours of tedious work :)
Thanks
Nic

[bridge32]

Helo all,
We currently have over 3000 products and have now registered for VAT.
I have to now include vat on most of our products.
At present we have our products set up like this:
Tax Class: -None-
Products Price (Net): £1.99
Products Price (Gross): £1.99

Now what we want to do is keep the Gross price the same, but have the Net price exclusive of vat. (£1.66)
I understand to reach this figure you would use £1.99 / 1.2 = £1.66 but how would i do this for all Taxable Products quickly? Would there be an SQL script / patch i could use?
The Taxable Products ID is 1 and Non Taxable Products ID is 2.
I also need to have the non taxable products set as Non Taxable Products (ID 2) which would leave the net and gross price the same.

I have tried to use easypopulate, but there is no net / gross column. I saw the Taxable Class Column, but even when i change this to say "Taxable Products" as per Class and then import, it does nothing.
Could someone help please and say me hours of tedious work :)
Thanks
Nic

I can see this may be difficult, so as i have only 300 products that are non taxable, if i was to change these manually to "Non Taxable Products" would this be easier to have an SQL script run on any items that have the tax class as "none" and change to "Taxable Products"?
Thanks
Nic

[David Allen]

Zen stores the prices in the database without tax and then adds the tax on for display (if prices are shown with tax) and checkout. So you could either dive into phpMyAdmin and change the tax class and price for each product with sql, or if you have setup to use easypopulate you could set the net price before import.

[bridge32]

Hello David,
Thanks for replying.
I have tried to set the net price and also the tax class as appropriate with easy populate, but for some reason when i upload the file it does not change them in zencart (they still show the same price and tax class ie: -None-
I was hoping there would be an sql query i could use as i have seen similar in the forums.
For example: change all product tax classes from -None- to Taxable Goods and use a formula to get the net figure divided by 1.2 so the gros figure would stay the same and the net figure would show an amount exc vat.
Nic
Nic

[David Allen]

Something like this:

'Update products set products_price = products_price/1.2 , products_tax_class_id = 1'

This will affect ALL products so I'd recommend doing a backup of at least the products table before you do any changes.

Regards
David

[bridge32]

Hi David,
I added this: 'Update products set products_price = products_price/1.2 , products_tax_class_id = 1' into Install SQL Patches and it said it failed. Am i supposed to put this some where else?
I have exported the products table field too just in case i need to import it back if it causes problems.
Thanks
Nic

[bridge32]

I have now tried the above script in the database, and it failed in there too.
Can someone give me some help when they are free please?
Thanks you
Nic

[bridge32]

I have now tried the above script in the database, and it failed in there too.
Can someone give me some help when they are free please?
Thanks you
Nic

I have sorted this out now.
I changed the sql to this: "UPDATE products SET products_price = products_price / 1.2 , products_tax_class_id = 1;"
I made the update & set in uppercase, then placed a space eitherside of the / sign and added ; at the end :)