Magento: Adding column to sales_flat_order_item, sales_flat_invoice_item and sales_flat_shipment_item

Suppose you want to add column to some table before it gets save in Magento. Example, Magento doesn’t save regular price of product when an order is placed, it only saves the selling price. So if your product have some special price in it, then Magento only saves it’s special price when an order is placed, so there is no track of regular price of that product in order item table. Same it goes to invoice item and shipment item. When creating invoice and shipment, Magento doesn’t have any track on the regular price of invoiced item’s and shipment item’s regular price.

So here I show you how you will add a column “product_mrp” in each of 3 tables and update the information without firing any query!

First of all, make an installer script in your module that will alter these three tables and add column “product_mrp” or any of your choice.

	<?php
	$installer = $this;

	$installer->startSetup();

	$installer->run("
	ALTER TABLE sales_flat_order_item ADD COLUMN product_mrp DECIMAL(12,4) NULL;
	ALTER TABLE sales_flat_invoice_item ADD COLUMN product_mrp DECIMAL(12,4) NULL;
	ALTER TABLE sales_flat_shipment_item ADD COLUMN product_mrp DECIMAL(12,4) NULL;
	");

	$installer->endSetup();

	?>

After this is executed, you will find your columns added at the end of your tables.
Now we will catch the event before order is placed, before invoice is created and before shipment is saved.

config.xml

	<sales_order_place_before>
	<observers>
	<adding_product_mrp_order>
	<type>singleton</type>
	<class>Namespace_Module_Model_Observer</class>
	<method>saveProductMrpInOrder</method>
	</adding_product_mrp_order>
	</observers>
	</sales_order_place_before>

	<sales_order_invoice_save_before>
	<observers>
	<adding_product_mrp_invoice>
	<type>singleton</type>
	<class>Namespace_Module_Model_Observer</class>
	<method>saveProductMrpInInvoice</method>
	</adding_product_mrp_invoice>
	</observers>
	</sales_order_invoice_save_before>

	<sales_order_shipment_save_before>
	<observers>
	<adding_product_mrp_shipment>
	<type>singleton</type>
	<class>Namespace_Module_Model_Observer</class>
	<method>saveProductMrpInShipment</method>
	</adding_product_mrp_shipment>
	</observers>
	</sales_order_shipment_save_before>

Now comes the Observer part that will add our column data before data is actually saved in table.

Observer.php

	public function saveProductMrpInOrder(Varien_Event_Observer $observer) {
	$order = $observer->getEvent()->getOrder();
	foreach($order->getAllItems() as $item) {
	$price = Mage::getModel('catalog/product')->load($item->getId())->getPrice();
	$item->setProductMrp($price);
	}
	return $this;
	}

	public function saveProductMrpInInvoice(Varien_Event_Observer $observer) {
	$invoice = $observer->getEvent()->getInvoice();
	foreach($invoice->getAllItems() as $item) {
	$price = Mage::getModel('catalog/product')->load($item->getProductId())->getPrice();
	$item->setProductMrp($price);
	}
	return $this;
	}

	public function saveProductMrpInShipment(Varien_Event_Observer $observer)
	{
	$shipment = $observer->getEvent()->getShipment();
	foreach($shipment->getAllItems() as $item) {
	$product = Mage::getModel('catalog/product')->load($item->getProductId());
	$price = $product->getPrice();
	$item->product_mrp = $price;
	}
	}

Now clear the cache, and place order and create it’s invoice and shipment. You will find the regular price of each of your products in all these three useful tables and hence you can track the original price information even if your product’s price has been changed afterwards.

Happy coding!

30 Comments

Michael April 18, 2012 at 4:50 pm
Hi,I’m not a geek with Magento but I have to do a similar task.
I have to generate a random code in an additional column in sales_flat_order_item (one code for every product sold). I have created the column in the database table.
I need an help to undertand which config.xml and observer.php edit.
I don’t have an additional module. Can I edit:
– app/code/core/Mage/Sales/Model/Observer.php
and
– app/code/core/Mage/Sales/etc/config.xml
???
If yes, which line should I write instead of
“Namespace_Module_Model_Observer”?
Is “Mage_Sales_Model_Observer” right?
Thanks in advance
Reply
- kalpesh April 18, 2012 at 5:05 pm
  Hi Michael. Editing core is not recommended, but if you want to edit it anyway, you will need to edit config.xml and add the event sales_order_place_before as per the given code. Instead of Namespace_Module_Model_Observer, you will need to write Mage_Sales_Model_Observer and inside that file you will have to create a method with name saveProductMrpInOrder if you are following my code. Don’t forget to clear cache before testing your changes.
  Reply
  - Michael April 18, 2012 at 5:17 pm
    Here is my code:
    in “Mage/Sales/etc/config.xml” I added this:
    singleton
    Mage_Sales_Model_Observer
    saveProductSpecialcodeInOrder
    in tags.
    in “Mage/Sales/Model/Observer.php” I added this:
    public function saveProductSpecialcodeInOrder(Varien_Event_Observer $observer) {
    Mage::log(“it works”.time().$test.PHP_EOL);
    }
    and developer mode is enabled, but id doesn’t seems to work. Nothing is written in var/log/system.log…
    any idea?
    Reply
    - Michael April 18, 2012 at 5:18 pm
      oh, it removes tags 🙂
      Reply
      - Michael April 18, 2012 at 5:24 pm
        Ok, I got it.
        I forgot the $test variable which was null and so the function crashed.
        Now it’s all ok, thanks a lot!
      - kalpesh April 18, 2012 at 5:36 pm
        nice to hear everything is working in the end 🙂
Shubhangi June 11, 2012 at 6:20 am
hi I need to insert a column name called subscriber_name in the table newsletter_subscriber. I have tried your way. But its not working for me. Please suggest me what can be done.
Reply
- kalpesh June 11, 2012 at 8:16 am
  You need to
  – write sql script to alter newsletter_subscriber table in your module’s sql directory
  – change the config.xml to match the new upgraded version of your sql upgrade script
  Let me know what you tried if it’s still not working.
  Reply
Classical October 22, 2012 at 2:58 am
It’s verry useful.
Thanks for your tip!
Reply
hitesh March 8, 2013 at 6:22 am
hi , i want to get data of sales_flat_order_item on product view page … i am new in magento ..will u please explain me how to fetch sales_flat_order_item data on product view page ?
i don’t know how to fetch data
Thanks…
Reply
Prashant August 3, 2013 at 11:16 am
Hello Kalpesh,
As per above instructions, I successfully able to add MRP value order but I am NOT able raise event for Invoice.
\————————————————————————–
In app/code/core/Mage/Sales/etc/config.xml
\————————————————————————–
singleton
sales/observer
saveProductMrpInInvoice
\————————————————————————–
In app/code/core/Mage/Sales/Model/Observer.php
\————————————————————————–
public function saveProductMrpInInvoice(Varien_Event_Observer $observer) {
Mage::log(“saveProductMrpInInvoice–>”);
return $this;
}
Am I missing anything? Please help me.
Reply
- kalpesh August 4, 2013 at 12:19 am
  Sorry your code is not posted correctly. You can enclose your code inside {code} to format it properly. Also are you trying to modify core file? Don’t do it!
  Reply
  - Prashant August 4, 2013 at 7:46 am
    Hello Kalpesh,
    I sent details via email to you. I have followed every step you mentioned and it is working only for order and NOT for Invoice and Shipment.
    Reply
Prashant August 9, 2013 at 4:07 pm
Hello Kalpesh,
Yet I could not find solution. Can you please help me here?
Reply
Avijit September 5, 2013 at 10:06 am
Hi kalpesh,
can you give me the folder(module) or provide download link
Reply
- kalpesh September 5, 2013 at 10:35 am
  Avijit, you will have to place the above code in your module. If you don’t have module you can create it in local and then put the code.
  Reply
Avijit September 5, 2013 at 11:27 am
Hi kalpesh,
It is not working for me.
I have done following
1)manually updated table with product_mrp field(3 tables)
2)In /app/code/core/Mage/Sales/etc/config.xml updated the xml that you say in above just changed the Namespace_Module_Model_Observer to Mage_Sales_Model_Observer
3)updated /app/code/core/Mage/Sales/Model/Observer.php with your code above
4)refreshed cache
But when order submit and going to paypal,i’ve checked db product_mrp is Null
Am I missing anything?
Reply
Avijit September 5, 2013 at 11:30 am
Hi,
i know it is wrong to change is base code,But provide some download link everyone will be benefited
Reply
- kalpesh September 5, 2013 at 10:39 pm
  I would put the echo’s and exit’s in Observer.php file to check if the observer method is being called or not. Also make sure your XML code is correct as Magento will not give you error if something is incorrect in XML. Better to create a new module with just config.xml and Observer.php, no need of sql script as you already ran it directly in DB. Send me your code if it still doesn’t work.
  Reply
Avijit September 6, 2013 at 6:45 am
Hi kalpesh,
It is working now.but “product_mrp” taking wrong price (randon price)
Reply
Neet December 18, 2013 at 10:55 am
Hi Kalpesh,
I am working on a website in magento 1.7. I have a requirement where the number of products available is restricted for a given delivery date, since the products are being made manually and only once the order is placed. So I have created an Attribute delivery_date_capacity on the product, set to 5. And I created another table deliverycapacity in magento. Here I will store the productId, DeliveryDate and a CapacityCounter. This counter will be incremented everytime an order is placed for a given date, or it will make an entry in here.
Now I have created an observer class for event sales_order_place_before .
singleton
TruffleStreet_Observer_Model_Observer
saveDeliveryCapacity
Now in the method saveDeliveryCapacity, I want it to check if orders are available for the given delivery date from the deliverycapacity table.
My function is not completed yet.
public function saveDeliveryCapacity(Varien_Event_Observer $observer) {
$order = $observer->getEvent()->getOrder();
foreach($order->getAllItems() as $item) {
$product = Mage::getModel(‘catalog/product’)->load($item->getId());
Mage:log(“Product name : “);
}
return $this;
}
But when I place my order in magento, the entry in my log is:
2013-12-18T10:51:07+00:00 ERR (3): Warning: log() expects parameter 1 to be double, string given in C:wampwwwcsappcodelocalTruffleStreetObserverModelObserver.php on line 28
What is going wrong here? Also is this the right approach to take for my requirements?
Thanks,
Neet
Reply
- kalpesh December 18, 2013 at 12:32 pm
  Hi Neet. The problem is you are using Mage:log instead of Mage::log
  Reply
Neet December 20, 2013 at 10:04 am
Hi Kalpesh,
Thanks for your help :). I had one more question regarding the Observer. I have created an Observer class with 2 methods:
1) saveDeliveryCapacity is called when sales_order_place_after event is fired. This is working fine.
2) checkDeliveryCapacity which is to be called when sales_order_place_before event is fired.
I want to add a check so that if this method returns false, then we should not place the order an appropriate message should be sent to the customer to update the delivery date .
How can i go about doing that? Is it possible?
Thanks,
Neet
Reply
- kalpesh December 20, 2013 at 4:03 pm
  Hi Neet. You can set Mage::register(‘checked’,true); if it’s good in checkDeliveryCapacity and retrieve it on saveDeliveryCapacity with $isChked = Mage::registry(‘checked’); Then based on $isChked value you can go ahead with what you want.
  Reply
Pierre Calaem September 19, 2014 at 5:00 am
Hello, Thx your article is very nice
Reply
vinoth March 31, 2015 at 7:16 am
Hi kalpesh,
i need one small help, how to add mrp price and special price column in magento pdf inovice
pls help me!
Regards,
VINOTH
Reply
Jatin March 26, 2016 at 12:18 pm
Thanks it works
But in saveProductMrpInOrder
$price is empty.
Reply
Ziya January 24, 2020 at 9:38 am
I want to add a new column in sales_flat_order_item table.
Is there any way to do it?
Reply
Ziya January 29, 2020 at 12:22 pm
Hi kalpesh,
I want to override following phtml from my custom module.
/sales/order/items/renderer/default.phtml
How can i do it?
I have created following 3 files.
/app/code/local/Revered/Expdlvrydate/etc/config.xml
————————————————————————-
0.1.0
revered.xml
————————————————————————-
/app/design/frontend/base/default/layout/revered/revered.xml
————————————————————————-
default
sales/order_item_renderer_default
revered/sales/order/items/renderer/default.phtml
grouped
sales/order_item_renderer_grouped
revered/sales/order/items/renderer/default.phtml
————————————————————————-
/app/design/frontend/base/default/template/revered/sales/order/items/renderer/default.phtml
————————————————————————-
I used this file same as default
————————————————————————-
Reply
pradeepika May 2, 2020 at 12:11 pm
new custom field is saving in one database but it is not creating another database
What could be the reason?
Reply