cancel
Showing results for 
Search instead for 
Did you mean: 

Magento 1.9 / Mysql - How to add 'shipping title' column to shipment grid?

SOLVED

Magento 1.9 / Mysql - How to add 'shipping title' column to shipment grid?

I'm gonna add the column in my order -> shipment grid!

 

I want to bring the "title" on 'sales_flat_shipment_track' table And it has also "parent_id" column, means entity_id on sales_flat_shipment!

 

Table === sales_flat_shipment_track

 

654.JPG

 

Table === sales_flat_shipment

 

555.JPG

 

So i need to implement this requirment to Mage_Adminhtml_Block_Sales_Shipment_Grid

What code do i have to insert to protected function _prepareCollection() & protected function _prepareColumns() ?

 

 

**** I tried *****

 protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel($this->_getCollectionClass());                 
        $collection->getSelect()->join('sales_flat_shipment_track', 'main_table.entity_id = sales_flat_shipment_track.parent_id', array('title'));
         
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

 

Please let me know it

Thank you so much!

 

 

Tags (1)
7 REPLIES

Re: Magento 1.9 / Mysql - How to add 'shipping title' column to shipping grid?

Try not to modify core files if you can avoid it. This will make your store difficult or impossible to upgrade.

This can be achieved using only observers. No rewrites or core file modifications are necessary.

 

Here's how:

Create an observer for the `sales_order_shipment_grid_collection_load_before` event. Use this function to join the data. Shipments can have more than one tracking number and carrier, so this will give you all of them in one field. You can change [C] and [S] to be any delimiter you wish.

    public function joinColumnsToCollection($observer)
    {
        $collection = $observer->getEvent()->getOrderShipmentGridCollection();
     
        // Join the tracking numbers in a new column

        // Don't use this first code. With this approach, some grids get error: "Integrity constraint violation: 1052 Column 'order_id' in where clause is ambiguous"
        /*$collection->getSelect()->joinLeft(
            array('tracking' => 'sales_flat_shipment_track'),
            'tracking.parent_id=`main_table`.entity_id',
            array(
                'tracking_numbers' => 'GROUP_CONCAT(
                    IF(carrier_code IS NULL AND track_number IS NULL,
                        NULL,
                        CONCAT(carrier_code, "[S]", track_number)
                    )
                    SEPARATOR "[C]")'
            )
        );
        $collection->getSelect()->group('tracking.parent_id');
        */
        $trackingTableName = Mage::getSingleton('core/resource')->getTableName('sales_flat_shipment_track');
        $collection->getSelect()->joinLeft(
            array('tracking_number_table' => new Zend_Db_Expr("(SELECT
                track.parent_id as tracking_order_id,
                GROUP_CONCAT(
                    IF( carrier_code IS NULL AND track_number IS NULL,
                        NULL,
                        CONCAT(carrier_code, '[S]', track_number)
                    )
                SEPARATOR '[C]') tracking_number_list
                FROM `{$trackingTableName}` as track
                GROUP BY track.parent_id
            )"
            )),
            'tracking_number_table.tracking_order_id=`main_table`.entity_id',
            array(
                'tracking_numbers' => 'tracking_number_table.tracking_number_list'
            )
        );
     }

 To add the column to the grid block itself, observe the controller_action_layout_render_before_adminhtml_sales_shipment_index event, and call this function in your observer:

    function addShipmentGridColumnToBlock() {
        $grid = Mage::app()->getLayout()->getBlock('order_shipments');

        $columnSettings = array(
            'header'       => 'Tracking Numbers',
            'index'        => 'tracking_numbers',
            'filter_index' => 'tracking_number_table.tracking_number_list',
            'type'         => 'text',
        );

        $grid->addColumnAfter('tracking_numbers', $columnSettings, 'increment_id');
    } 

Re: Magento 1.9 / Mysql - How to add 'shipping title' column to shipping grid?

I don't need tracking number 

I just want to bring title not tracking number 

Re: Magento 1.9 / Mysql - How to add 'shipping title' column to shipping grid?

This code will give title from tracking

protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel($this->_getCollectionClass());
         $collection->getSelect()->joinLeft('sales_flat_shipment_track', 'main_table.entity_id = sales_flat_shipment_track.parent_id', array('title'));
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

and in prepare column

$this->addColumn('title', array(
            'header'    => Mage::helper('sales')->__('Title Shipped'),
            'index'     => 'title',
            'type'      => 'text',
        ));

This is tested solution

Find helpful ? Consider Giving Kudos to this post.
Problem solved? Click Accept as Solution!"
Qaisar Satti

Re: Magento 1.9 / Mysql - How to add 'shipping title' column to shipping grid?

Thank you so much it works

btw when i export this grid, there are the case that parent_id are multiples per on entity_id.

So the error comes out like 'parent_id' already exists. 

 

Do you have the solution of it? Thank you ! 

Re: Magento 1.9 / Mysql - How to add 'shipping title' column to shipping grid?

I group by the title

 

$collection->getSelect()->joinLeft('sales_flat_shipment_track', 'main_table.entity_id = sales_flat_shipment_track.parent_id',array('title'))->group(array('sales_flat_shipment_track.parent_id'));

 

 

it works Thanks !  

Re: Magento 1.9 / Mysql - How to add 'shipping title' column to shipping grid?

it works Thanks !
Cong ty giup viec theo gio uy tin

Re: Magento 1.9 / Mysql - How to add 'shipping title' column to shipping grid?

Tags (1)