10/14/21 Franck Bugnet Tuto

MagicFormula online help

MagicFormula, available natively in Merlin Backoffice from version 1.5, is a hyper powerful and flexible tool to perform an infinite number of mass data modification operations.

When to use it :

    • When the (faster) mass modifier does not perform the desired operation.
    • When the (faster) mass modifier does not perform the desired operation.
    • To use other columns as operands in the calculation.
    • To display in a free column, the result of a calculation, for example the brand rate, the turnover per product, the volume...
    • .
    • To copy or concatenate one or more columns in another (also realisable with MagicEdit).
    • To dynamically add missing columns in an import file and fill them by calculation.  
    • To carry out mass operations on texts not foreseen in MagicEdit.
    • To copy data from the product table into the columns of the child tables specific declinations or prices. 
    • .../...

How to enter a new formula ?

This field can receive all kinds of formulas, to fill in or modify in mass the values ​​in the columns.

It can be just as well mathematical formulas, acting and producing numbers, as expressions of manipulation of the texts, or even a mixture of the two.

It is possible to save and reuse the formulas.

Each column of each table has its own list.

This list is accessible by clicking on the small icon in the form of double blue arrows.

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

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

If the text is displayed in red, it 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 contrary, it starts with a text (or the name of a column containing text), it will handle the data of the formula, including the numbers, like strings and will give as result a string of characters.
To force a number, or the content of a column containing a number, to be seen and treated as text, for example at the start of a formula, enclose that number or column name between two double quotes ("").
To force a string resulting from a text column, but containing numbers, to be treated as a number, use the Val () function

It is possible to enter formulas and expressions of the type "IF (condition) THEN (value1) ELSE (value2)", with the small button "?" of the title bar.
The condition can be multiple (of type AND / OR), using the buttons & and |).
The word "PAS" is used to reverse a condition (eg: "PAS <=" means "not less than or equal to").
The button "!" allows you to quickly insert the word "NOT". The% (percentage) button, inserts in the formula "/ 100".
For example "{VA} + {VA} * 5/100" is the formula to know to increase the current values ​​by 5%. It is also equivalent to this: "{VA} * (1 + 5/100)".

In mathematical formulas, the signs * and / (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 comma digits, use the period (.), Not the comma!

To concatenate strings (texts), 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 then the name of the product on the same line.

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

Expert level use:

  • The manipulation of the texts allows the use of all the functions of management of the character strings of the Windev language. For example Contains (), StringStartWith (), Right (), WithoutSpace (), ExtractString (), Replace (), etc ... You can either manually enter the function name or pick them in the second dropdown list.
  • Likewise you can perform complex calculations using arithmetic operators of the Windev language, like + =, - =, mathematical functions like Abs () and binary operators &, |, || ... The only limit is your imagination. Refer to the Windev documentation for free access on the PCSoft site. It goes without saying that Sitolog will not be able to provide training on this language, which is not its own, as part of the free support. But for complex issues, you can get our advice through our service tickets available on our site. You can either manually enter the function name or pick them in the first dropdown list.

Reminder of some rules:

  • Use it. (the period and not the comma) for decimal digits.
  • Use * for mulitplication and not the letter x nor the point.
  • To use a value to read in a column (a variable), use the selector, to avoid typing errors.
  • Variables are written as the name of the column surrounded by brackets {}.
  • {VA} means "current value of the field".
  • Any open parenthesis must be closed. There must be the same number of (as of).
  • Two variables or digits must always be separated by an operator.
  • It is forbidden to place two operators next to each other.
  • It is forbidden to divide by 0. -A parenthesis cannot be empty.
  • Respect the syntax of the comparisons: ((A= B)? (C) ELSE (D)) for if A = B, insert C, otherwise insert D. The possible conditions are =, <,>, <=,> =,! =, IN (,,). The condition can be reversed by adding before the word PAS. So "A PAS = B" means "if A is different from B".
  • The formula must neither start nor end with an operator, but with a number or a variable or a parenthesis.

Category's other articles

This article was useful ? Share it !