Resolved VM export problem

Hello,

If the order is just a single billing address The order exports fine
IF the order has a shipping address It doesnt seem to export the "bank_account_nr" field

Do you have an address type filter set in your export template? Please post export debug log to check. To get the debug log check How to collect debug information? document.

When I look at the database I can see that the "bank_account_nr" is ONLY recorder in the BT address. OK so this should be fine If I just use billing_bank_account_nr instead in the rule. But this also exporting as blank.

What if you export billing_bank_account_nr field directly without any rule applied? Does it get the value then?

I can see an address_type in the query - but cannot see this set anywhere in my template.

Also to answer your 2nd question - No When I export the bank_account_no directly there is only a Value when the order has a single BT address

If Have an order with the bank account no filled in for the BT address and I cannot get it to export AT ALL.

Even if I include a new. "billing_bank_account_nr" AND "shipping_bank_account_nr" field. Both of these are blank for this order even though the BT address does indeed have the "bank_account_nr" field filled in


heres the debug - but Ive attached the full log

IF (LENGTH(`user_info2`.`po_number`) IS NULL, `user_info1`.`po_number`, COALESCE(`user_info2`.`po_number`, `user_info1`.`po_number`)) AS `billing_po_number`

FROM `vjqv4_virtuemart_orders`

LEFT JOIN `vjqv4_virtuemart_order_items` ON `vjqv4_virtuemart_orders`.`virtuemart_order_id` = `vjqv4_virtuemart_order_items`.`virtuemart_order_id`

LEFT JOIN `vjqv4_virtuemart_order_userinfos` AS `user_info1` ON `vjqv4_virtuemart_orders`.`virtuemart_order_id` = `user_info1`.`virtuemart_order_id`

LEFT JOIN `vjqv4_virtuemart_order_userinfos` AS `user_info2` ON `vjqv4_virtuemart_orders`.`virtuemart_order_id` = `user_info2`.`virtuemart_order_id` AND `user_info2`.`address_type` = 'ST'

LEFT JOIN `vjqv4_virtuemart_orderstates` ON `vjqv4_virtuemart_orders`.`order_status` = `vjqv4_virtuemart_orderstates`.`order_status_code`

LEFT JOIN `vjqv4_virtuemart_product_manufacturers` ON `vjqv4_virtuemart_order_items`.`virtuemart_product_id` = `vjqv4_virtuemart_product_manufacturers`.`virtuemart_product_id`

LEFT JOIN `vjqv4_virtuemart_manufacturers` ON `vjqv4_virtuemart_product_manufacturers`.`virtuemart_manufacturer_id` = `vjqv4_virtuemart_manufacturers`.`virtuemart_manufacturer_id`

LEFT JOIN `vjqv4_users` ON `vjqv4_users`.`id` = `user_info1`.`virtuemart_user_id`

LEFT JOIN `vjqv4_virtuemart_countries` ON `vjqv4_virtuemart_countries`.`virtuemart_country_id` = `user_info1`.`virtuemart_country_id`

LEFT JOIN `vjqv4_virtuemart_invoices` ON `vjqv4_virtuemart_orders`.`virtuemart_order_id` = `vjqv4_virtuemart_invoices`.`virtuemart_order_id`

LEFT JOIN `vjqv4_virtuemart_paymentmethods_en_gb` ON `vjqv4_virtuemart_orders`.`virtuemart_paymentmethod_id` = `vjqv4_virtuemart_paymentmethods_en_gb`.`virtuemart_paymentmethod_id`

LEFT JOIN `vjqv4_virtuemart_shipmentmethods` ON `vjqv4_virtuemart_orders`.`virtuemart_shipmentmethod_id` = `vjqv4_virtuemart_shipmentmethods`.`virtuemart_shipmentmethod_id`

LEFT JOIN `vjqv4_virtuemart_products` ON `vjqv4_virtuemart_products`.`product_sku` = `vjqv4_virtuemart_order_items`.`order_item_sku`

WHERE `vjqv4_virtuemart_orders`.`virtuemart_order_id` IN (513918, 513926, 513927, 513928) AND `vjqv4_virtuemart_orders`.`created_on` >= '2020-10-23 10:33:11' AND `vjqv4_virtuemart_orders`.`order_status` IN ('C','S') AND `user_info1`.`address_type` = 'BT'

Hello,

I can see an address_type in the query - but cannot see this set anywhere in my template.

You have set Address type as Billing or Shipping in the template. The one you see in the query is the condition to select Billing address row. There is another condition to select Shipping address in the same query.

Also to answer your 2nd question - No When I export the bank_account_no directly there is only a Value when the order has a single BT address

When there is no shipping address and when address type is set to billing or shipping address, the value of Billing address would be taken. isn't that correct? or you are saying only orders with billing address work but orders with both billing and shipping address does not export the value at all?

If Have an order with the bank account no filled in for the BT address and I cannot get it to export AT ALL.

Even if I include a new. "billing_bank_account_nr" AND "shipping_bank_account_nr" field. Both of these are blank for this order even though the BT address does indeed have the "bank_account_nr" field filled in

As per the debug log you are exporting four orders 513918, 513926, 513927 and 513928. What if you run the below query from PHPMyAdmin for the order no which you say bank_account_nr is not exported? Replace XXXX with order id before running the query. Do you see value for bank_account_nr field? Can you share a screenshot on the results?

SELECT `company`,`address_1`,`address_2`,`city`,`zip`,`bank_account_nr` FROM `vjqv4_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = XXXX AND `address_type` = 'BT'

or you are saying only orders with billing address work but orders with both billing and shipping address does not export the value at all? YES thats absolutely spot on

So - but Ive had to resort to fixing this another way - I'm copying the "bank_account_no" over to the ST address at the end of the checkout so its recorder in both the BT and The ST for each order.

Thanks for your help on this

Hello,

So - but Ive had to resort to fixing this another way - I'm copying the "bank_account_no" over to the ST address at the end of the checkout so its recorder in both the BT and The ST for each order.

Thank you for posting your solution.

You did not tell us the result with the query. The value of bank_account_no should be included export for any address type. I am curious to know why it does not work when the order has only Billing address.

Yes Sorry, I just ran that query with a relevent order no and it does indeed show the bankaccount no Value as expected

SELECT `company`,`address_1`,`address_2`,`city`,`zip`,`bank_account_nr` FROM `vjqv4_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 513926 AND `address_type` = 'BT'

so No idea why its not appearing in the csv.

I have done a workaround for now - but it would be nice to get to the bottom of this

Hello,

or you are saying only orders with billing address work but orders with both billing and shipping address does not export the value at all? YES thats absolutely spot on

bank_account_nr is a shopper field and is added as a column in `vjqv4_virtuemart_order_userinfos` table. If you have address type filter set as Billing or shipping address in template RO CSVI first checks if there is a Shipping address record for an order and use those details in export. If not use Billing address details. Since the order 513926 has both billing address and Shipping address, it uses Shipping address details for which bank_account_nr is empty. You can see the attached screenshot for the query from your debug log that it is looking for shipping address record.

You are copying the bank_account_no over to the ST address, export works.

"Since the order 513926 has both billing address and Shipping address, it uses Shipping address details for which bank_account_nr is empty." yes true, but I'm also explicitly adding in the field "billing_bank_account_nr" at the end of the file. and this returns blank aswell. I thought that forcing the "billing_bank_account_nr" it would override/ignore the "Billing or shipping address " filter

Hello,

yes true, but I'm also explicitly adding in the field "billing_bank_account_nr" at the end of the file. and this returns blank aswell. I thought that forcing the "billing_bank_account_nr" it would override/ignore the "Billing or shipping address " filter

That is correct, billing_bank_account_nr should get the value ignoring the address type set in template. The debug log sent to us previously shows there is no billing_bank_account_nr field added to export. The fields added are billing_company, billing_address_1, billing_address_2, billing_city, billing_zip and billing_country_2_code. Moreover i tested this with sample data on my local server and do see billing_bank_account_nr getting a value when there is a billing and shipping address available for an order with address_type filter set to Billing or shipping address.