MagicFormula online help

MagicFormula online help for bulk editing PrestaShop data using formulas

MagicFormula interface

To open MagicFormula, place the cursor in the column to be modified and right-click, which has the effect of opening the column's context menu.
If MagicFormula is available on this column, you have two options, either an action on only the rows already selected, or on all rows:

Select or enter your formula (see instructions below):

Once you've entered your formula, click on "Phase 1: Preview". The result of the operation is displayed in the table. All that's left is to validate (save to database) or cancel:

How to enter a formula

The input field can accommodate all kinds of formulas, to fill in or mass-modify values in columns.

These can range from mathematical formulas, acting on and producing numbers, to text manipulation expressions, or a mixture of both.

Formulas can be saved and re-used. You can also delete and duplicate them:



Each column in each table has its own list of formulas (filtered list).
By unchecking the filter option, it is possible to use a formula created for another column in one column. If you duplicate the formula, the copy will be associated with the current column.

This list can be accessed by clicking on the small icon in the shape of double blue arrows.

You can either type in your formulas on the keyboard, or use the quick-entry buttons in the title bar of this microwindow.

Formulas can use values read from the same column as the one being modified, with the {VA} parameter. Or data from other columns (use the column selector above, avoid typing them on the keyboard), in the form {column name}.

If the text is displayed in red, this means that at least one of the columns used in the formula is not present in the table, which will certainly lead to an incorrect result.

If the 1st element of the formula is a number (or the name of a column containing a number), the formula will be a mathematical calculation.
If, on the other hand, it starts with text (or the name of a column containing text), it will manipulate the formula data, including the digits, as strings and give a string as the result.
To force a number, or the contents of a column containing a number, to be seen and treated as text, for example at the start of the formula, enclose the number or column name in double quotes (" ").
To force a string from a text column, but containing digits, to be treated as a number, use the Val() function.

You can enter formulas and expressions like "IF (condition) THEN (value1) THEN (value2)", using the little "?" button in the title bar.
The condition can be multiple (AND/OR type), using the & and |) buttons.
The word "NOT" reverses a condition (e.g. "NOT <=" means "not less than or equal to").
The "!" button is used to quickly insert the word "PAS".
The % (percentage) button inserts "/100" into the formula.
For example, "{VA}+{VA}*5/100" is the formula you need to know to increase current values by 5%. It is also equivalent to "{VA}*(1+5/100)".

In mathematical formulas, the * and / signs (multiply and divide), take precedence over the + and - (add and subtract). Add parentheses if necessary.
Ex: A+B*C is equivalent to A+(B*C).
If you want to add A and B first, enter (A+B)*C.

To enter numbers with commas, use the period (.), not the comma!

To concatenate strings of characters (text), use the + sign. For example, "{VA}+ " _ "+{'name__ps_product'}" will fill each cell with its own content, followed by a space, a dash, another space and then the name of the product in the same line.

Beware, you can only build formulas with the columns displayed. But if you later re-use a previously constructed formula with a column that is no longer displayed, the result will inevitably be incorrect (content at best replaced by an empty string or even an error).

Expert level usage:

• Text manipulation allows the use of all Windev language string management functions. For example Contient(), ChaineCommencePar(), Droite(), SansEspace(), ExtraitChaine(), Replace(), etc...
• You can also perform complex calculations using the arithmetic operators of the Windev language, such as +=, -=, mathematical functions like Abs() and the binary operators &, |, ||.... The only limit is your imagination. Please refer to the free Windev documentation on the PCSoft website. We've made a shortlist of the most useful functions, but there are many more. It goes without saying that Sitolog cannot provide training in this language, which is not its own, as part of its free support. But for complex issues, you can get our advice through our service tickets available on our website.

Reminder of a few rules:

• Use the. (the dot, not the comma) for decimal digits.
• Use * for multiplication and not the letter x or the dot.
• To use a value to be read in a column (a variable), use the selector, to avoid typos.
• Variables are written in the form of the column name surrounded by quotation marks and square brackets {'column name'}. Quotation marks are optional when the column name contains no spaces or special characters such as + - * / % etc
• {VA} means "current field value".
• Any open parenthesis must be closed. There must be the same number of ( as ).
• Two variables or numbers must always be separated by an operator.
• It is forbidden to place two operators next to each other.
• Dividing by 0 is forbidden.
• A parenthesis cannot be empty.
• Correct comparison syntax: ( (A=B) ? (C) OR (D) ) for if A=B, insert C, otherwise insert D . Possible conditions are =, <, >, <=, >=, !=, IN ( , , ) . The condition can be inverted by adding the word NOT in front of it. Thus "A PAS= B" means "if A is different from B".
• The formula must neither begin nor end with an operator, but with a number or a variable or a parenthesis.

Complete video tutorial with numerous examples, of the MagicFormula mass editing tool (in French)

To learn all about MagicFormula and calculated columns, both in the tables of the main window and in the import tool. After a complete explanation of the interface, the many detailed examples will enable you to master the tool and above all adapt its use to your own needs.
The training begins with a short reminder of the other tools, such as the modifier by calculation, so that you fully understand the differences and know when to use one or the other.

Increase the resolution to 720p if the video is too blurry, replay it in Auto if it freezes.