VAT calculation not correct

[Comparts]

Hi all,

I'm new to this forum, but i have been using joomla/virtuemart for more then a decade.
Always been able to solve some issues till now ... 
At the moment the website is running on Joomla! 3.9.5 / Virtuemart 3.4.2
I have a VAT calculation problem.
On the site there are over 90k products, these are imported (csv) with only the resellers price 21% VAT included.
My accountant asks me to seperate the VAT.
In my case this formule ( :121x21) Belgium VAT 21% would be needed.
Whatever setting i use in the VAT and tax calculations + - / -21% 21% value at 21.000 the outcome is never correct.

To keep it simple i will set an easy example for a product that cost the buyer 121€ VAT included.
121€ is the only price i have.
I need 21% VAT to be seperated from it, in this case 21€

What am i missing or are there different values possible? FX calculated values?

Thank you in advance for any answers

Dirk

[AH]

Ok - I think I understand you

You have to adjust the import to remove the VAT - VM will calculate this for you.

If the stuff is already imported and VM is holding the the VAT inclusive figure in the base price you can do a simple  sql to the prices table do :  base_price /1.21
This gives you the net value

Then setup a general VAT per product & 21%

[Comparts]

ok
i assume that i need to insert a value in jos_virtuemart_product_prices / product_price
Would that be correct?

Regards
Dirk

[Jörgen]

Yes and no You should use a query that looks about this

Code Select Expand

UPDATE `xxx_virtuemart_product_prices` SET `product_price`=product_price / 1.21 WHERE 1

Test on a backup of the database

Jörgen @ Kreativ Fotografi

[AH]

I see no values in this price row - I guess you have shown a blank row for some reason.

As Jorgen - shows the update sql is pretty simple

Run this on a test version of your system

Then you can see/test how to configure VM

e.g.
Add a standard VAT configuration of 21%
Display the product prices etc

Now you handle VAT correctly

[Comparts]

I see no values in this price row

Hmm, that is what i get when i click "insert" in the DB.
A bit confused now ..., i must admit that in my lifetime (and i'm 57  ) that i can count
the times on one hand that i have entered a DB .
Someone who wants to do this for a fee?

Regards
Dirk

[Jörgen]

Insert is used when you enter a new row in the database. The sql I showed updates already entered rows. If Yo choose browse for the database xxx_virtuemart_product_prices you will see all the added prices. The column virtuemart_product_id is the id for the product that uses this price but since You can use several prices with one product there can be several entries with the same virtuemart_product_id, the column virtuemart_product_price_id used together with other data to distunguish between the different prices for one product.
This one I could help You for free. Email me.

Jörgen @ Kreativ Fotografi

[Comparts]

Problem solved

Thank you very much Jörgen

Regards
Dirk

[Jörgen]

You are welcome
See You in Belgium if I am going that way

Jörgen @ Kreativ Fotografi