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.
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”
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”
|Write expression||Input test value||Check it|
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)
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
In case, price requires reducing by 15%, this will be suitable
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)])
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.
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')
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, 1means that we choose the first SKU character for image name (only one character should be used)
substr([ATTRIBUTE(sku)], 2, 1the 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
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
Another example shows how to replace part of product name, for example, we want to add company name instead of "test"
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