Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

How to add customer’s email address to Sales > Invoices grid? 
 
tilzinger
Sr. Member
 
Total Posts:  126
Joined:  2007-12-27
 

I’ve tried so many things and nothing works. I just want to add a Customer Email column to the Sales > Invoices grid view.

Here is what I have that does not work.

The collection that isn’t working

$collection Mage::getResourceModel('sales/order_invoice_collection')
            
// ->addAttributeToSelect('email')
            
->addAttributeToSelect('order_id')
            ->
addAttributeToSelect('increment_id')
            ->
addAttributeToSelect('created_at')
            ->
addAttributeToSelect('state')
            ->
addAttributeToSelect('grand_total')
            ->
addAttributeToSelect('order_currency_code')
            ->
joinAttribute('billing_firstname''order_address/firstname''billing_address_id'null'left')
            ->
joinAttribute('billing_lastname''order_address/lastname''billing_address_id'null'left')
            ->
joinAttribute('customer_email''order_addrress/customer_email''customer_id'null'left')
            ->
joinAttribute('order_increment_id''order/increment_id''order_id'null'left')
            ->
joinAttribute('order_created_at''order/created_at''order_id'null'left')

The column is successfully added, but the rows are blank.

$this->addColumn('email', array(
            
'header'    => Mage::helper('sales')->__('Customer Email'),
            
'sortable'  => false,
            
'index'     => 'email'
        
));

Any help would be appreciated, thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
 
tilzinger
Sr. Member
 
Total Posts:  126
Joined:  2007-12-27
 

Anyone have a clue how to fix this?

 
Magento Community Magento Community
Magento Community
Magento Community
 
icesh
Member
 
Total Posts:  34
Joined:  2011-04-15
 

never use joinAttribute
I usually use getselect()

and there is something different from my resourceModel and yours

this is my code

$collection = Mage::getResourceModel($this->_getCollectionClass());
$collection->getSelect()->join(array(’sfi’=>’sales_flat_invoice’),’main_table.entity_id=sfi.entity_id’,array());
$collection->getSelect()->join(array(’sfo’=>’sales_flat_order’),’sfo.entity_id=sfi.order_id’,array(’sfo.customer_email’));
$this->setCollection($collection);

$this->addColumn(’customer_email’, array(
‘header’ => Mage::helper(’sales’)->__(’Customer Email’),
‘index’ => ‘customer_email’,
‘filter_index’ => ‘sfo.customer_email’,
));

$this->_getCollectionClass() return ‘sales/order_invoice_grid_collection’ (yours is ‘sales/order_invoice_collection’)

so in my code, I have to join sales_flat_invoice grid with sales_flat_invoice (sales_flat_invoice grid entity_id = sales_flat_invoice entity_id) and then join them with sales_flat_order (sales_flat_invoice order_id = sales_flat_order entity_id)
we can get customer_email from sales_flat_order

btw, you can test your query by echoing $collection->getSelect()->__toString()

hope that helps, I’m still interested in other method, please post here if you find other method.. thanks

 
Magento Community Magento Community
Magento Community
Magento Community
 
tilzinger
Sr. Member
 
Total Posts:  126
Joined:  2007-12-27
 

I don’t have a sales_flat_order or sales_flat_invoice table. I’m still on Magento 1.3.2.4. Here is my entire file, it’s app/code/core/Mage/Adminhtml/Block/Sales/Invoice/Grid.php

class Mage_Adminhtml_Block_Sales_Invoice_Grid extends Mage_Adminhtml_Block_Widget_Grid
{

    
public function __construct()
    
{
        parent
::__construct();
        
$this->setId('sales_invoice_grid');
        
$this->setDefaultSort('created_at');
        
$this->setDefaultDir('DESC');
    
}

    
protected function _prepareCollection()
    
{
        
//TODO: add full name logic
        
$collection Mage::getResourceModel('sales/order_invoice_collection')
            
// ->addAttributeToSelect('email')
            
->addAttributeToSelect('order_id')
            ->
addAttributeToSelect('increment_id')
            ->
addAttributeToSelect('created_at')
            ->
addAttributeToSelect('state')
            ->
addAttributeToSelect('grand_total')
            ->
addAttributeToSelect('order_currency_code')
            ->
joinAttribute('billing_firstname''order_address/firstname''billing_address_id'null'left')
            ->
joinAttribute('billing_lastname''order_address/lastname''billing_address_id'null'left')
            
// ->joinAttribute('email', 'order_address/email', 'customer_id', null, 'left')
            
->joinAttribute('order_increment_id''order/increment_id''order_id'null'left')
            ->
joinAttribute('order_created_at''order/created_at''order_id'null'left')
        ;
        
        
// $collection = Mage::getResourceModel('sales/order_invoice_grid_collection'); 
        // $collection->getSelect()->join(array('sfi'=>'sales_flat_invoice'),'main_table.entity_id=sfi.entity_id',array()); 
        // $collection->getSelect()->join(array('sfo'=>'sales_flat_order'),'sfo.entity_id=sfi.order_id',array('sfo.customer_email')); 
        // // $this->setCollection($collection);

        
$this->setCollection($collection);
        return 
parent::_prepareCollection();
    
}

    
protected function _prepareColumns()
    
{
        
// other columns here
        
        
$this->addColumn('email', array(
            
'header'    => Mage::helper('sales')->__('Customer Email'),
            
'sortable'  => false,
            
'index'     => 'email'
        
));
        
       
// rest of the columns
        
        
$this->addExportType('*/*/exportSalesCsv'Mage::helper('reports')->__('CSV'));
        
$this->addExportType('*/*/exportSalesExcel'Mage::helper('reports')->__('Excel'));

        return 
parent::_prepareColumns();
    
}

    
protected function _prepareMassaction()
    
{
        $this
->setMassactionIdField('entity_id');
        
$this->getMassactionBlock()->setFormFieldName('invoice_ids');

        
$this->getMassactionBlock()->addItem('pdfinvoices_order', array(
             
'label'=> Mage::helper('sales')->__('PDF Invoices'),
             
'url'  => $this->getUrl('*/*/pdfinvoices'),
        ));

        return 
$this;
    
}

    
public function getRowUrl($row)
    
{
        
return $this->getUrl('*/*/view',
            array(
                
'invoice_id'=> $row->getId(),
            )
        );
    
}

    
public function getGridUrl()
    
{
        
return $this->getUrl('*/*/*', array('_current' => true));
    
}

}
 
Magento Community Magento Community
Magento Community
Magento Community
 
G_Mehta
Guru
 
Avatar
Total Posts:  418
Joined:  2010-01-27
India
 

Hi tilzinger,

You need to join attribute for the email, as following:

->joinAttribute(’email’, ‘order_address/email’, ‘billing_address_id’, null, ‘left’);

make sure in your “sales_order_entity_varchar”. there is entry for the customer email…

add column like following:

$this->addColumn(’email’, array(
‘header’ => Mage::helper(’sales’)->__(’Customer Email’),
‘index’ => ‘email’
));

and done… go for it smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
tilzinger
Sr. Member
 
Total Posts:  126
Joined:  2007-12-27
 
Gaurav Mehta - 24 May 2011 07:36 PM

make sure in your “sales_order_entity_varchar”. there is entry for the customer email…

I only see email addresses in the “sales_order_varchar” table, no email addresses are in the “sales_order_entity_varchar” table.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top