Working with fields in EAV on magento

Question

I have a custom attribute for min_price I need to update this field using data from the cost field using a formula. (cost+1.50)/.84

Any ideas on how I can do this?

I've tried to make since of the EAV database and do it in MySQL directly but it's confusing to say the least.

https://stackoverflow.com/questions/24580872/mysql-multiple-select-statements-with-an-update

• David Manners: do you need to do this in mysql or in magento itself?
• Brad: Doesn't matter, as long as I can do it without having to export/import all the time.
• Charlie: Use observer catalog_product_save_before And update the attribute
• Brad: @Charlie Do you suggest any demo code is that would show me how to do that? I can piecemeal it together from several sources, but I don't want to use bad info. Thanks

Answer 1

From reading your comments above, sounds like you want something that will update via the backend when you click save.

I have added 2 observers one for the normal backend category save and another for the mass update 'update attributes' on the category grid page.

app/code/local/Namespace/Module/etc/config.xml

<?xml version="1.0"?>
<config>
 <modules>
 <Namespace_Module>
 <version>0.1.1</version>
 </Namespace_Module>
 </modules>
 <global>
 <models>
 <namespace_module>
 <class>Namespace_Module_Model</class>
 </namespace_module>
 </models>
 </global>
<adminhtml>
 <events>
 <catalog_product_save_before>
 <observers>
 <namespace_module>
 <class>namespace_module/observer</class>
 <method>catalogUpdateMinPrice</method>
 </namespace_module>
 </observers>
 </catalog_product_save_before>
 <catalog_product_attribute_update_before>
 <observers>
 <namespace_module>
 <class>namespace_module/observer</class>
 <method>catalogUpdateMinPriceMassaction</method>
 <namespace_module>
 </observers>
 </catalog_product_attribute_update_before>
 </events>
</adminhtml>
</config>

app/code/local/Namespace/Module/Model/Observer

 <?php class Namespace_Module_Model_Observer
{
 protected $additionalValue = 1.50;
 protected $multiplicationValue = 0.84;
 /**
 * Handle catalog_product_save_before event
 *
 * @param Varien_Event_Observer $observer
 * @return void
 */
 public function catalogUpdateMinPrice(Varien_Event_Observer $observer)
 {
 /** @var $product Mage_Catalog_Model_Product */
 $product = $observer->getEvent()->getDataObject();
 $cost = $product->getCost();
 $newMinPrice = null;
 if($cost != ""){
 $newMinPrice = ($cost+$this->additionalValue)/$this->multiplicationValue;
 }
 $product->setMinPrice($newMinPrice);
 }
 /**
 * Handle catalog_product_attribute_update_before event
 *
 * @param Varien_Event_Observer $observer
 */
 public function catalogUpdateMinPriceMassaction(Varien_Event_Observer $observer)
 {
 /** @var $block Mage_Adminhtml_Block_Catalog_Product_Edit_Action_Attribute_Tab_Attributes */
 $attributesData = $observer->getEvent()->getAttributesData();
 $cost = $attributesData['cost'];
 if($cost != ""){
 $newMinPrice = ($cost+$this->additionalValue)/$this->multiplicationValue;
 $attributesData['min_price'] = $newMinPrice;
 $observer->getEvent()->setAttributesData($attributesData);
 }
 }
}

• Brad: I'm running the community version I noticed enterprise_pricepermissions in your answer, will this work with community? Thanks
• James Anelay - TheExtensionLab: Whoops, I have updated the answer now. Yes it will but use the updated version. (I just copied it from a enterprise observer and forgot to update that bit (it's just an identifier))
• Brad: I think this code will work, if I knew a little more about magento. I've got a gist that shows my setup. Wondering if you could tell me where I went wrong. gist.github.com/btray77/0e34cda8fa751524b610
• James Anelay - TheExtensionLab: Nearly :). Line 33 of your config.xml <Overnight_Pricer> should be </Overnight_Pricer>. (Personally I would also keep the following lines lowercase : 11,13,21,24,30,33 in your config.xml as is done in the core)
• Brad: did the changes, not creating log, and not updating pricing...

Answer 2

If it's a one time thing, you could consider doing it quickly via a spreadsheet (although I know others would strongly disagree). You could export the data pretty quickly via some sql

SELECT
catalog_product_entity.sku,
catalog_product_entity.entity_id,
eav_attribute.frontend_label,
catalog_product_entity_decimal.`value`
FROM
eav_attribute
INNER JOIN catalog_product_entity_decimal ON catalog_product_entity_decimal.attribute_id = eav_attribute.attribute_id
INNER JOIN catalog_product_entity ON catalog_product_entity_decimal.entity_id = catalog_product_entity.entity_id
WHERE eav_attribute.frontend_label LIKE "%cost%"

This assumes -

1. You only have the one store view
2. The attribute you're referencing is "cost"
3. The attribute you're referencing is a "price" attribute

So you can then export this as a csv, quickly process the calculation, and import it back it, either by an import tool like Magmi (recommended if you don't know what you're doing), or if you have a full grasp of this explanation, you could pop the results straight back in to the database (again, I know for many this is a cardinal sin).

However, always make a backup of your database before you try things like this. You really never can have too many backups.

James