0

I would like to add the tax_amount to the orders and invoices grids to be able to have them in the export files. I found some documentation and tutorials on telling how to add a custom column, with a new column created in database, but not one showing existing data.

I assume I have to call existing data from sales_order and sales_invoice tables in

app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_grid.xml

and

app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_invoice_grid.xml

So I started with a new module.

app/code/Vendor/Module/etc/module.xml :

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../lib/internal/Magento/Framework/Module/etc/module.xsd">
    <module name="Vendor_Module" setup_version="2.0.0">  
    <sequence>
            <module name="Module"/>
    </sequence>
    </module>
</config>

app/code/Vendor/Module/etc/di.xml :

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid">
        <arguments>
            <argument name="joins" xsi:type="array">
                <item name="sales_order" xsi:type="array">
                    <item name="table" xsi:type="string">sales_order</item>
                    <item name="origin_column" xsi:type="string">entity_id</item>
                    <item name="target_column" xsi:type="string">entity_id</item>
                </item>
            </argument>
            <argument name="columns" xsi:type="array">
                <item name="tax_amount" xsi:type="string">sales_order.tax_amount</item>
            </argument>
        </arguments>
    </virtualType>
</config>

app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_grid.xml :

<?xml version="1.0" encoding="UTF-8"?>
<!--
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="tax_amount">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Tax amount</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

But it does not seem to work, I only have an empty column.

Any help would be appreciated.

I'm working on Magento 2.2.0.

3 Answers 3

2

I finally made it work.

app/code/Vendor/Module/etc/module.xml :

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../lib/internal/Magento/Framework/Module/etc/module.xsd">
    <module name="Vendor_Module" setup_version="2.0.0">  
    <sequence>
            <module name="Module"/>
    </sequence>
    </module>
</config>

app/code/Vendor/Module/etc/di.xml :

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <item name="sales_order_grid_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\Order\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
</config>

app/code/Vendor/Module/Model/ResourceModel/Order/Grid/Collection.php :

<?php
namespace Vendor\Module\Model\ResourceModel\Order\Grid;
use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as OriginalCollection;
/**
* Order grid extended collection
*/
class Collection extends OriginalCollection
{
    protected function _renderFiltersBefore()
    {
        $this->getSelect()->joinLeft(
            ["so" => "prfx_sales_order"],
            'main_table.entity_id = so.entity_id',
            array('tax_amount','shipping_tax_amount')
        )
        ->distinct();
        
        parent::_renderFiltersBefore();
    }

    // Below function fixes error and endless loading when filters lead to empty page
    protected function _initSelect()
    {

        $this->addFilterToMap('created_at', 'main_table.created_at');

        parent::_initSelect();
    }
}

app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_grid.xml :

<?xml version="1.0" encoding="UTF-8"?>
<!--
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="tax_amount">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Tax amount</item>
                </item>
            </argument>
        </column>
        <column name="shipping_tax_amount">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Shipping tax amount</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

I do not know if it's the best or most straight forward soluttion, but it works, I have now Tax amount and Shipping tax amount in the orders grid, and in the csv / xml export of the grid.

Unfortunately, the trick does not work for invoices, when module is enabled, I weirdly get 3 invoices for each order.

IMPORTANT EDIT After update to 2.4.5 the module works fine on first page load, but I can't filter the orders by date anymore.

I'm getting following error:

Next Zend_Db_Statement_Exception: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous, query was: SELECT DISTINCT main_table.*, prfx_braintree_transaction_details.transaction_source, so.tax_amount, so.shipping_tax_amount FROM prfx_sales_order_grid AS main_table LEFT JOIN prfx_braintree_transaction_details ON prfx_braintree_transaction_details.order_id = main_table.entity_id LEFT JOIN prfx_sales_order AS so ON main_table.entity_id = so.entity_id WHERE (((created_at >= '2022-10-03 22:00:00'))) AND (((created_at <= '2022-10-05 21:59:59'))) ORDER BY main_table.created_at DESC

1

David's answer is working great, I just used it, but changed slightly the sales_order_grid.xml file so the amount is displayed in the right currency with its code:

<?xml version="1.0"?>
<!--
/**
 * sales_order_grid
 *
 * @copyright Copyright © 2021 The Etailers. All rights reserved.
 * @author    [email protected]
 */
-->
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="tax_amount" class="Magento\Sales\Ui\Component\Listing\Column\Price">
            <settings>
                <filter>textRange</filter>
                <label translate="true">Tax amount</label>
            </settings>
        </column>
        <column name="shipping_tax_amount" class="Magento\Sales\Ui\Component\Listing\Column\Price">
            <settings>
                <filter>textRange</filter>
                <label translate="true">Shipping tax amount</label>
            </settings>
        </column>
    </columns>
</listing>
4
  • Nice one, thanks! I just tried it and it's indeed better. Only works on sales_order_grid.xml and not sales_order_invoice_grid.xml unfortunately though.
    – David
    Oct 6, 2022 at 9:16
  • BTW, did you test this with Magento 2.4.5 applying date filter on the table?
    – David
    Oct 6, 2022 at 9:27
  • Hi David! I haven't tried it on 2.4.5 yet!
    – SantiBM
    Oct 6, 2022 at 9:58
  • Well there seems to be an issue since last update, I edited my answer with information.
    – David
    Oct 6, 2022 at 11:56
0

Magento 2 — Add Custom Column to the Invoice grid: https://santhiyasugumar22.medium.com/magento-2-add-custom-column-to-the-invoice-grid-9fadbcb76f8e

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.