0 votes from 0 votes

Bulk Update Magento Products Applying Expressions in Multi Editors

Magento bulk product update is a must-have manipulation, missing which, you actually miss the harmony in the catalog. Although, this will not happen if you use Store Manager for Magento, since it comprises solid functionalities for mass product edits, among them Multi Editors.


Multi Editors drastically accelerate product management, give the possibility to fluently renew product details and manage inventory. Multiple chosen product listings will acquire values, you configure for necessary attribute in Product or Inventory Multi Editor.


Nevertheless, you might have some specific requirements at times, for example, increase or decrease product price by percentage value, add description or manage stock availability. You find out that you can not set identical price for multiple selected merchandise and are nonplused with this task.


In fact, you can fulfil all the adjustments via Multi Editors, using expressions. When processing products with Multi Editors, you can see Expression field beside each of attributes. Clicking on … icon, you will get Expression Editor, that facilitates expression building, provides SQL help, lets check the formula and make sure it will return desired value.



Below in this article we will describe several cases, demonstrating how to alter product details using SQL statements.


SAMPLE #1

Change Stock Availability, depending on product quantity using IF condition


Suppose some of Magento products are good sellers and are coming to the end. You, as a farsighted store owner, prefer not to miss a thing and update Magento product inventory in time. So, the task is to reveal these products and set them as Out of Stock.


We are going to use IF function to compose the expression, applying which, we will get products “Out of Stock” if less than 2 items of these products left.


The following statement should be configured for “Stock Availability” field in Inventory Multi Editor


IF([(qty)]<2,0,1)
, where 0 is “Out of Stock”, 1 - “In Stock”


If quantity of selected products is less than 2 items, they will be marked as “Out of Stock”, otherwise, they will remain “In Stock”



After you input expression, do not forget to check “Update” box for the field to be modified.

SAMPLE #2

Change Stock Availability, taking into consideration two conditions


We need to mark products as Out of Stock if "Manage Stock" option for them is disabled and number of these products is less than 3. The following rule should be indicated for "Stock Availability" field.


IF([(manage_stock)] = 0 and [(qty)] < 3, 0, 1)
, where 0 is “Out of Stock”, 1 - “In Stock”



According to this rule, products won't be marked as "Out of Stock" if their quantity is more than 3.


Another sample shows how to set availability to “Out of Stock” if quantity is less than 10, “In stock” if some have more than 100 items at the moment. In case products do not fall under both conditions, they will be turned to “In Stock”


IF([(qty)]>100,1,IF([(qty)]<10,0,1))
Write expression Input test value Check it

SAMPLE #3

Set stock notifications, depending on product quantity


When the stock level of some products goes below the value, indicated in “Notify for Quantity below” field, Magento automatically informs merchants about it.


We would like to receive notifications, when product quantity reaches 50 items, when at the moment their quantity is 300 or more.


This expression statement should be applied to “Notify for Quantity Below” field in Inventory Multi Editor.


IF([(qty)]>=300, 50, 1)


SAMPLE #4

Update product price


Product prices change continuously and you should keep pace with this. Expressions ensure flexibility and help adjust prices on the fly.


For example, to increase price by 10%, apply this command


[FIELD_VALUE]*1.1

In case, price requires reducing by 15%, this will be suitable


[FIELD_VALUE]*0.85

Another example shows how to select certain products for update. For example, you need to reduce price for products, that cost $500 or more at the moment. The aim is to decrease their price by 25%.


IF([ATTRIBUTE(price)]>=500, [ATTRIBUTE(price)]*0.75, [ATTRIBUTE(price)])


SAMPLE #5

Set Special Price for selected products


Using expression you can instantly set special price for numerous products. We want to configure special price, that is 25% less than actual price and to attain this we are going to execute the following SQL command.


[ATTRIBUTE(price)]*0.75

SAMPLE #6

Implement modifications to text fields


May be some of merchandise, placed at your store, miss short descriptions? Do not worry about anything. We know how to improve the matters. In our case product short description will be created on the basis of product name, to which we add “Test Description” phrase.


We use CONCAT function for this expression and add it in Short Description field of Product Multi Editor


CONCAT([ATTRIBUTE(name)], 'Test Description')



SAMPLE #7

Change image file name on the basis of product SKU


By means of expression we are going to modify product image file name. We want to substitute current image names with ones, generated on the basis of product SKUs. We use CONCAT and SUBSTR functions and build the following rule


CONCAT( '\/', 
        substr([ATTRIBUTE(sku)], 1, 1), 
        '\/',
       substr([ATTRIBUTE(sku)], 2, 1), 
       '\/',
       [ATTRIBUTE(sku)], 
       '.jpg' )

substr([ATTRIBUTE(sku)], 1, 1
means that we choose the first SKU character for image name (only one character should be used)

substr([ATTRIBUTE(sku)], 2, 1
the second SKU character will be used as the second character in image name (we specify that 1 symbol should be used)

[ATTRIBUTE(sku)], '.jpg'
this will be used as the rest of image name


SAMPLE #8

Replace or Remove Details


There might occur situations, when you need to substitute or withdraw some product details, let's say part of product name. For example, all of your products have "test" attached to product name and you want to delete this. The following expression will help you out

REPLACE([ATTRIBUTE(name)],'test','')

Another example shows how to replace part of product name, for example, we want to add company name instead of "test"

REPLACE([ATTRIBUTE(name)],'test','Company Name')



These and other expressions will help you out in any situation, simplify data handling and shorten time, spent on product arrangements. Want to use expressions in the course of Magento import? Read related article - input-missing-data-and-modify-existing-by-means-of-expressions-in-the-process-of-magento-import

Make Bulk Updates Quick and Simple with Store Manager Update FREE

    Free upgrade to Magento 2 supported version