Resolved Price returns 0 for Tax/Vat when it should have a value

Hi Tharuna

I have created a couple of exports and all seems to be working correctly. I am just awaiting feedback from the client and I will update the ticket in due course.

Thanks very much for your help!

David

Hello David,

The change Tharuna did will not work as you will not have the complete tax details.

The problem seem to be the duplicate values, not sure how this can be overcome.

I may have found an option, let me check it further and get back to you.

Hello Dave,

I have taken a look and noticed this:

order_id	product_id	order_product_code	order_product_shipping_tax	order_product_tax	order_product_tax_before_discount	order_product_price

17487	0	order additional	0	0	0	-2.83

17487	0	order additional	0	0	0	0

17487	1161	John_Wick_Select_Action_Figure	0	3.99833	0	19.99167

There are 2 rows that just say additional, since you are grouping the data these will come first and the other data is then squashed by the database. This only happens on a few orders I see, so you will not see this happening for every order. I am a little unsure how to deal with this. One thing you could do is add a skip rule on the product_id field that if the value is 0, it should not be included. However you cannot use the grouping anymore because the skip rule will come after the database has done it's job. On the other hand this field also has price data in it, that makes me unsure how to deal with this. I guess it depends on the data you need.

Let me know if this helps.

Hi Roland

I am not sure where you have found this information. I cannot see it in the export I have. Is there a way that I can send the export that I have on file without it being visible on the forum? I have re-added the link for the export I was referring too.

Essentially the VAT should be showing in the separate column for accounting purposes. Most of the shipping charges are zero rated.

Thanks

David

Hello David,

I am not sure where you have found this information.

This is from your hikashop_order_products table. This table is used to get the product information on the order export.

I cannot see it in the export I have.

If you use PhpMyAdmin for example and check the table I mentioned earlier you can see that or as an alternative you can create a Custom Table Export in RO CSVI and export the data from this table. You can then see how HikaShop stores the orders. In this case there are 3 records stored for that one product in the order you mentioned.

Is there a way that I can send the export that I have on file without it being visible on the forum?

Sure, email it to support@rolandd.com

Essentially the VAT should be showing in the separate column for accounting purposes.

Yes I understand that but RO CSVI is not a reporting tool although it goes a long way. So we should be creative in how to get the correct data from the database. In this case that one product has 3 lines of data. Send me the file and let's see what is in there.

Hello Dave,

If I understand this correctly, it is not possible to group the fields if I need the TAX/VAT values displayed.

Grouping is not the only issue I see after checking Kerrie Bell. So you said you expect to see £5 for the shipping field. This is not going to happen with RO CSVI because those £5 consists of 2 values in this case £1.325 for one product and £3.665 for another product. As I mentioned earlier is that RO CSVI is not a reporting tool but only exports, so because of this there is no calculations done when exporting the orders. In this case RO CSVI does not check for each order how many products are there and what totals do I need to calculate.

The only option I see is to export multiple rows per order so that for orders with more than 1 product, you do get all records albeit in multiple rows. The Kerrie Bell example, it seems to show a duplicate, that would need to be checked why that one product is showing twice. That can be done by adding all fields and see where it differs. This could also be resolved by some grouping I guess.

First question to answer of course is if it is OK to export multiple rows for an order?

Hi Roland

thanks for the reply.

I see now where you got the info from, thanks for the clarification.

The data I am trying to display is the VAT/Tax that is applied to the order. So in the export I selected order_product_tax expecting that to pull the VAT for the order. Most of the shipping is free unless over a certain weight.

I have just noticed another issue as well. On the order 17494 the VAT is only showing as £1.325 for the product, this should have been £5 & the shipping VAT is £0.998. So the VAT total should have been £5.99. It appears that it has only shown the VAT for one product. Not grouping shows the VAT 3 times in total plus the column for shipping vat.

If I understand this correctly, it is not possible to group the fields if I need the TAX/VAT values displayed.

I will send the latest export shortly by email & also the one for 17494.

Thanks

David

Hello,
As Roland mentioned we found a way to get the total tax amount for an order. I have updated a patch file on your site and have created a copy of export template. Please check the export with template name "Test Hikashop Order Export VAT UK with tax amount ". I have added a new template field tax_amount to this export template. This field is the sum of all tax amount of an order. Run the export and let me know if that is correct.

Hi Roland

apologies for the late reply, been a long bank holiday here so not been at my desk.

Having duplicates will make the export a lot larger for reading purposes, however the full tax etc does need to be exported. The problem seem to be the duplicate values, not sure how this can be overcome. If you have a suggestion it would be greatly appreciated.

Thanks

David

Hello David,

Having duplicates will make the export a lot larger for reading purposes, however the full tax etc does need to be exported.

For full tax you mean the sum of taxes of products in an order? As Roland mentioned that is not possible with RO CSVI exports.

The problem seem to be the duplicate values, not sure how this can be overcome. If you have a suggestion it would be greatly appreciated.

I have set order_product_id as group by field in Kerrie Bell example template. There are no duplicates in export now. Please check.