Adding New Columns to the Orders, Invoice, and Credit Memo’s Grid in Magento 2

Published by John on January 27, 2021 Under Magento

One of my clients that uses Magento 2 wanted to be able to export orders and include the tax amount, discount amount, and full street address from magento.

The grid export under Sales -> Orders did basically what he wanted in terms of an export, aside from a few missing fields. So, rather than install a plugin, I looked into what is involved with adding items to the grid.

Important Note: There is some performance hit associated with this and for this site, we are just turning it on/off as needed. If you need a more permanent solution or are concerned with the additional database calls, a better solution would be to extend the actual grid tables, rather than using Mysql Joins as is done below.

For this example, we used base currency for discount, tax, and shipping costs.

If you get an error, like the column is ‘ambiguous’ when applying filters, see the note below

Setup a custom Module

This guide assumes you already have a custom module setup. When using the below files, replace ‘Vendor/Module’ with the correct information from your Magento 2 plugin.

Add Fields to Collection

First, lets add three files, which join several fields to the three collections: Order Grid, Invoice Grid, and Creditmemo Grid:

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

<?php

namespace Vendor\Module\Model\ResourceModel\Order\Grid;

use Magento\Sales\Model\ResourceModel\Order\Creditmemo\Grid\Collection as OriginalCollection;

class CollectionCreditmemo extends OriginalCollection{

    protected function _construct(){
  
        $this->addFilterToMap('created_at', 'main_table.created_at');

        $this->addFilterToMap('billing_country', 'soab.country_id');
        $this->addFilterToMap('billing_street', 'soab.street');
        $this->addFilterToMap('billing_city', 'soab.city');
        $this->addFilterToMap('billing_zipcode', 'soab.billing_zipcode');
        $this->addFilterToMap('billing_state', 'soabr.code');

        $this->addFilterToMap('shipping_country', 'soa.country_id');
        $this->addFilterToMap('shipping_street', 'soa.street');
        $this->addFilterToMap('shipping_city', 'soa.city');
        $this->addFilterToMap('shipping_zipcode', 'soa.shipping_zipcode');
        $this->addFilterToMap('shipping_state', 'soar.code');

        $this->addFilterToMap('tax_amount', 'so.base_tax_amount');
        $this->addFilterToMap('shipping_tax_amount', 'so.base_shipping_tax_amount');
        $this->addFilterToMap('discount_amount', 'so.base_discount_amount');

        parent::_construct();
        
    }

    protected function _renderFiltersBefore(){

        $this->getSelect()->joinLeft(
            ["so" => "sales_order"],
            'main_table.order_id = so.entity_id',
            array('base_tax_amount as tax_amount','base_shipping_tax_amount as shipping_tax_amount', 'base_discount_amount as discount_amount')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soa" => "sales_order_address"],
            'so.shipping_address_id = soa.entity_id',
            array('street as shipping_street', 'city as shipping_city', 'postcode as shipping_zipcode', 'country_id as shipping_country')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soab" => "sales_order_address"],
            'so.billing_address_id = soab.entity_id',
            array('street as billing_street', 'city as billing_city', 'postcode as billing_zipcode', 'country_id as billing_country')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soar" => "directory_country_region"],
            'soa.region_id = soar.region_id',
            array('code as shipping_state')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soabr" => "directory_country_region"],
            'soab.region_id = soabr.region_id',
            array('code as billing_state')
        )
        ->distinct();

        parent::_renderFiltersBefore();
    }
}

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

<?php

namespace Vendor\Module\Model\ResourceModel\Order\Grid;

use Magento\Sales\Model\ResourceModel\Order\Invoice\Grid\Collection as OriginalCollection;

class CollectionInvoice extends OriginalCollection{

    protected function _construct(){

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

        $this->addFilterToMap('billing_country', 'soab.country_id');
        $this->addFilterToMap('billing_street', 'soab.street');
        $this->addFilterToMap('billing_city', 'soab.city');
        $this->addFilterToMap('billing_zipcode', 'soab.billing_zipcode');
        $this->addFilterToMap('billing_state', 'soabr.code');

        $this->addFilterToMap('shipping_country', 'soa.country_id');
        $this->addFilterToMap('shipping_street', 'soa.street');
        $this->addFilterToMap('shipping_city', 'soa.city');
        $this->addFilterToMap('shipping_zipcode', 'soa.shipping_zipcode');
        $this->addFilterToMap('shipping_state', 'soar.code');

        $this->addFilterToMap('tax_amount', 'so.base_tax_amount');
        $this->addFilterToMap('shipping_tax_amount', 'so.base_shipping_tax_amount');
        $this->addFilterToMap('discount_amount', 'so.base_discount_amount');

        parent::_construct();

    }

    protected function _renderFiltersBefore(){

        $this->getSelect()->joinLeft(
            ["so" => "sales_order"],
            'main_table.order_id = so.entity_id',
            array('base_tax_amount as tax_amount','base_shipping_tax_amount as shipping_tax_amount', 'base_discount_amount as discount_amount')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soa" => "sales_order_address"],
            'so.shipping_address_id = soa.entity_id',
            array('street as shipping_street', 'city as shipping_city', 'postcode as shipping_zipcode', 'country_id as shipping_country')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soab" => "sales_order_address"],
            'so.billing_address_id = soab.entity_id',
            array('street as billing_street', 'city as billing_city', 'postcode as billing_zipcode', 'country_id as billing_country')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soar" => "directory_country_region"],
            'soa.region_id = soar.region_id',
            array('code as shipping_state')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soabr" => "directory_country_region"],
            'soab.region_id = soabr.region_id',
            array('code as billing_state')
        )
        ->distinct();

        parent::_renderFiltersBefore();
    }
}

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

<?php

namespace Vendor\Module\Model\ResourceModel\Order\Grid;

use Magento\Sales\Model\ResourceModel\Order\Creditmemo\Grid\Collection as OriginalCollection;

/**
* Order grid extended collection
*/
class CollectionCreditmemo extends OriginalCollection{

    protected function _construct(){

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

        $this->addFilterToMap('billing_country', 'soab.country_id');
        $this->addFilterToMap('billing_street', 'soab.street');
        $this->addFilterToMap('billing_city', 'soab.city');
        $this->addFilterToMap('billing_zipcode', 'soab.billing_zipcode');
        $this->addFilterToMap('billing_state', 'soabr.code');

        $this->addFilterToMap('shipping_country', 'soa.country_id');
        $this->addFilterToMap('shipping_street', 'soa.street');
        $this->addFilterToMap('shipping_city', 'soa.city');
        $this->addFilterToMap('shipping_zipcode', 'soa.shipping_zipcode');
        $this->addFilterToMap('shipping_state', 'soar.code');

        $this->addFilterToMap('tax_amount', 'so.base_tax_amount');
        $this->addFilterToMap('shipping_tax_amount', 'so.base_shipping_tax_amount');
        $this->addFilterToMap('discount_amount', 'so.base_discount_amount');
  
        parent::_construct();

    }

    protected function _renderFiltersBefore(){

        $this->getSelect()->joinLeft(
            ["so" => "sales_order"],
            'main_table.order_id = so.entity_id',
            array('base_tax_amount as tax_amount','base_shipping_tax_amount as shipping_tax_amount', 'base_discount_amount as discount_amount')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soa" => "sales_order_address"],
            'so.shipping_address_id = soa.entity_id',
            array('street as shipping_street', 'city as shipping_city', 'postcode as shipping_zipcode', 'country_id as shipping_country')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soab" => "sales_order_address"],
            'so.billing_address_id = soab.entity_id',
            array('street as billing_street', 'city as billing_city', 'postcode as billing_zipcode', 'country_id as billing_country')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soar" => "directory_country_region"],
            'soa.region_id = soar.region_id',
            array('code as shipping_state')
        )
        ->distinct();

        $this->getSelect()->joinLeft(
            ["soabr" => "directory_country_region"],
            'soab.region_id = soabr.region_id',
            array('code as billing_state')
        )
        ->distinct();

        parent::_renderFiltersBefore();
    }
}

The _construct in the above files helps fix an issue where when you apply filters, you may get a ‘Column not found: 1054 Unknown column’ or ‘Integrity constraint violation: 1052 Column ‘created_at’ in where clause is ambiguous’ error, when attempting to apply filters after adding a custom column.

This code loads after the collection has been initialized and adds a map between the variable name and the name in the table, so if you add/change variables, you may need to adjust this.

Update your di.xml to Override Collections

Next, lets update our di.xml file to use the above classes when rendering the 3 grids:

File: app/code/Vendor/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\CollectionOrder</item>
                <item name="sales_order_invoice_grid_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\Order\Grid\CollectionInvoice</item>
                <item name="sales_order_creditmemo_grid_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\Order\Grid\CollectionCreditmemo</item>
            </argument>
        </arguments>
    </type>

</config>

Update UI to Show New Fields

Finally, we need to update the UI Grid Component so that they items appear in the grid:

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

<?xml version="1.0" encoding="UTF-8"?>
<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">
            <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="discount_amount" class="Magento\Sales\Ui\Component\Listing\Column\Price">
            <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">Discount Amount</item>
                </item>
            </argument>
        </column>


        <column name="shipping_street">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Shipping Street</item>
                </item>
            </argument>
        </column>

        <column name="shipping_city">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Shipping City</item>
                </item>
            </argument>
        </column>

        <column name="shipping_state">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Shipping State</item>
                </item>
            </argument>
        </column>

        <column name="shipping_zipcode">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Shipping Zipcode</item>
                </item>
            </argument>
        </column>

        <column name="shipping_country">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Shipping Country</item>
                </item>
            </argument>
        </column>

        <column name="billing_street">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing Street</item>
                </item>
            </argument>
        </column>

        <column name="billing_city">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing City</item>
                </item>
            </argument>
        </column>

        <column name="billing_state">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing State</item>
                </item>
            </argument>
        </column>

        <column name="billing_zipcode">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing Zipcode</item>
                </item>
            </argument>
        </column>

        <column name="billing_country">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing Country</item>
                </item>
            </argument>
        </column>


    </columns>
</listing>

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

<?xml version="1.0" encoding="UTF-8"?>
<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_invoice_columns">
        <column name="tax_amount" class="Magento\Sales\Ui\Component\Listing\Column\Price">
            <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="discount_amount" class="Magento\Sales\Ui\Component\Listing\Column\Price">
            <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">Discount Amount</item>
                </item>
            </argument>
        </column>


        <column name="shipping_street">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Shipping Street</item>
                </item>
            </argument>
        </column>

        <column name="shipping_city">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Shipping City</item>
                </item>
            </argument>
        </column>

        <column name="shipping_state">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Shipping State</item>
                </item>
            </argument>
        </column>

        <column name="shipping_zipcode">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Shipping Zipcode</item>
                </item>
            </argument>
        </column>

        <column name="shipping_country">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Shipping Country</item>
                </item>
            </argument>
        </column>

        <column name="billing_street">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing Street</item>
                </item>
            </argument>
        </column>

        <column name="billing_city">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing City</item>
                </item>
            </argument>
        </column>

        <column name="billing_state">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing State</item>
                </item>
            </argument>
        </column>

        <column name="billing_zipcode">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing Zipcode</item>
                </item>
            </argument>
        </column>

        <column name="billing_country">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing Country</item>
                </item>
            </argument>
        </column>


    </columns>
</listing>

File: app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_creditmemo_grid.xml

<?xml version="1.0" encoding="UTF-8"?>
<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_creditmemo_columns">
        <column name="tax_amount" class="Magento\Sales\Ui\Component\Listing\Column\Price">
            <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="discount_amount" class="Magento\Sales\Ui\Component\Listing\Column\Price">
            <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">Discount Amount</item>
                </item>
            </argument>
        </column>


        <column name="shipping_street">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Shipping Street</item>
                </item>
            </argument>
        </column>

        <column name="shipping_city">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Shipping City</item>
                </item>
            </argument>
        </column>

        <column name="shipping_state">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Shipping State</item>
                </item>
            </argument>
        </column>

        <column name="shipping_zipcode">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Shipping Zipcode</item>
                </item>
            </argument>
        </column>

        <column name="shipping_country">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Shipping Country</item>
                </item>
            </argument>
        </column>

        <column name="billing_street">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing Street</item>
                </item>
            </argument>
        </column>

        <column name="billing_city">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing City</item>
                </item>
            </argument>
        </column>

        <column name="billing_state">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing State</item>
                </item>
            </argument>
        </column>

        <column name="billing_zipcode">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing Zipcode</item>
                </item>
            </argument>
        </column>

        <column name="billing_country">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Billing Country</item>
                </item>
            </argument>
        </column>


    </columns>
</listing>

Conclusion

After clearing your cache and, depending on your setup, recompiling/upgrading, you should now see two new fields in your Sales Orders, Invoice, and Credit Memo Grids: Tax Amount and Discount Amount

Then, if you expand the available columns, you will see several more related to billing/shipping address.

As noted, there is a performance hit associated with this, so a better solution is not to use Joins like the above, but add new columns to the actual grid table, as otherwise this sort of negates the advantage of having a grid table in the first place.


No Comments |

Add a Comment