Tag: ui component

Add column in Sales Invoice/Order grid in Magento 2

Magento 2 UI components are designed for simple and flexible UI rendering of several list and grid types.

Sales order and  invoice grid in Magento 2 can be accessed in admin panel under “Sales” -> “Orders/Invoice” menu. By default only several main columns are visible in grid, but there are additional columns, that can be enabled from “Columns” dropdown on the top-right side.

7cdea-sales_order_grid_optional_columns

If you are going to create a module that provides additional useful information about orders or invoices, it is a good idea to add corresponding columns to sales order grid or sales invoice grid.

Create custom module in Magento 2 before you are going to add custom column in sales order or invoice grid. To add custom column in sales invoice/order grid. You have to perform following 3 steps.

Step 1. Add column to sales_order_grid/sales_invoice_grid database table

Columns are added to database tables using InstallSchema script. This script should be updated in the same module, where custom_column field was added.

We need to create InstallSchema at following directory :

app/code/VENDOR/MODULE/Setup/InstallSchema.php

Adding following code snippet will do trick for you.

$setup->getConnection()->addColumn(
        $setup->getTable('sales_invoice'),
        'custom_column',
        [
            'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            'length' => 255,
            'nullable' => true,
            'comment' => 'Custom Column'
        ]
    );
$setup->getConnection()->addColumn( 
       $setup->getTable('sales_invoice_grid'), 
       'custom_column', 
       [
           'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT, 
           'length' => 255, 
           'nullable' => true, 
           'comment' => 'Custom Column' 
       ]
   );

It will create a column named custom_column, of type text, with comment “Custom Column” to  sales_invoice and sales_invoice_grid during installation. If you want to add custom column for sales order grid then you can use sales_order and sales_order_grid table instead of sales_invoice and sales_invoice_grid.

sales_order_grid and sales_invoice_grid  is index table and is used for order/invoice grid rendering speed up. It is designed to store all information required for sales order/invoice grid rendering, so custom columns are required to be added to this table.

 To reflect changes in database in Magento 2, Optionally deleting module entry from setup_module table and running bin/magento setup:upgrade command will work for you.
After this step you can see that custom_column column is present in sales_order_grid/sales_invoice_grid(as well as sales_order and sales_invoice) table, but is remaining empty as it is not mapped to any data source. Let’s do it in second step.

Step 2. Map column using DI configuration to populate it in sales_order_grid/sales_invoice_grid table

First let’s understand how sales_order_grid/sales_invoice_table table is populated.

For order When order is placed and for invoice when invoice is created, data related to this order/invoice is selected from sales_order/sales_invoice table joining several additional tables and inserted to sales_order_grid/sales_invoice_grid table. This operation is initiated by \Magento\Sales\Model\ResourceModel\Grid::refresh function and the default select is declared in MAGENTO_SALES_MODULE/etc/di.xml” file.

So to include our custom column in mentioned insert from select, we have to extend di configuration creating app/code/VENDOR/MODULE/etc/adminhtml/di.xml file.

The following xml snippet should be added to di configuration inside config node.

<config ...>
    ...
    <virtualType name="Magento\Sales\Model\ResourceModel\Order\Invoice\Grid">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="custom_column" xsi:type="string">
                     sales_invoice.custom_column
                </item>
            </argument>
        </arguments>
    </virtualType>
    ...
</config>

The above code is for custom column mapping for invoice grid. To add mapping for order grid. you just need to replace bold text with Magento\Sales\Model\ResourceModel\Order\Grid and sales_order

This above configuration is specifying that custom_column  is added to select from sales_order/sales_invoice  and will populate sales_order_grid.custom_column/sales_invoice_grid.custom_column column with corresponding value from sales_order.custom_column/sales_invoice.custom_column.

After this step, our custom_column column in sales_order_grid table is populated with value from sales_order table each time order is placed and for invoices, custom coulmn in sales_invoice_grid is populated with value from sales_invoice table each time invoice is created.  Still, column will exist only in database, and will not be visible in admin panel.

Step 3. Configure UI grid component to display custom column

To reflect this custom column on admin panel sales order/sales invoice grid, we have to extend sales_order_grid/sales_order_invoice_grid UI component by adding a UI configuration file in our module.

It is possible to extend UI configuration for sales order and sales invoice grid introducing 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 file.

Generally, it should have the same name and path in relation to module directory as main sales order and invoice grid UI component file.

Add the following xml snippet to the ui configuration file

<!-- Add the column "custom_column" (Custom Column) to the sales invoice grid. -->
<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="custom_column">
        <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">Custom Column</item>
            </item>
        </argument>
    </column>
</columns>

Above code is for adding custom_column column to sales invoice grid. to add custom column for sales order grid replace sales_order_invoice_columns with sales_order_columns

This will extend sales_order_columns/sales_order_invoice_columns and add a column based on custom_column field, of type text, with translatable label “Custom Column”.

Don’t forget to refresh config cache.

Hope this tutorial was useful for you. If you have any query or feedback, leave it in the comment segment, I will response as soon as possible:

Advertisements