Hello, I'm Franck, the director of Sitolog. I'm going to talk to you today
about the Magic Formula tool. Magic Formula is a complementary tool to the one I showed in
the tutorial just before. Remember, if you looked at it, it was the tool called
modified by calculation that appears here below the tables. And so this
modified by calculation tool allows you to mass modify numbers, numerics. For example, if I
want to modify these few sales prices here, I could, for example, add 5% like this.
sales price + 5%, I do more and I had my prices here increasing by 5%. So,
this calculation tool is very interesting because of its speed, that is to say that when you perform
this type of calculation on hundreds or even thousands of lines, it's almost instantaneous.
It is really optimized to perform calculations very, very quickly. However, this tool for
me has some weak points. The first weakness is that it only works on
numbers. Okay? You can't perform operations with this tool on
text columns. You have to use Magic Edit. But sometimes, we need to perform certain operations
that mix text and numbers. We'll see that later. So, we can't do that
with this tool below. The second weakness is that it's limited to only a few columns.
That's because the scripts behind it are hyper-optimized and are therefore
done one by one for each column. So I couldn't do it for all the columns. I
did it on the main numeric columns. And then the third weakness of this tool
is that it's not multi-column. That is to say, here I performed an operation that modifies the
sale price using the sale price as a parameter. But if I wanted, for example,
to calculate the cotaxes here based on the sale price, well, I can't. Okay? We don't
have multiple column headings to use as parameters in the
formula. So that's why we developed a new tool called Magic Formula 1
that compensates for all these flaws. So for me, the two are complementary. Magic Formula 1 is
much more powerful, you'll see that it allows you to do a lot more things. On the other hand,
it's slower to execute. So, that doesn't mean it's slow, but simply
instead of executing something, a complicated calculation on a hundred lines in less than 2 seconds,
maybe it will take 15 seconds. That's still quite reasonable when you see the
time savings it brings, but it's still a fairly different approach to this calculation tool.
So, the first thing we're going to see is how to execute Magic Formula. Magic Formula
is like Magic Edit. It's executed by right-clicking in a column. For example, in
the sales price column. Here, if I want to modify the few rows that I have selected there,
I right-click on one of these rows. I have my context menu that appears and you
see here two menus that appear. Magic Formula in all the selected cells
of this column which will act only on the grayed-out rows and then Magic Formula in
the entire column which will act on all the rows currently present in the table. So I will
choose the first one and I have my tool that appears here. Know that Magic Formula is available in
almost all the columns of the table or tables, including the text columns. We will see that
in a moment. So, first way of using. So what you need to understand rather is that
in this interface here, you have tools at the top that allow you to enter a formula without
typing on the keyboard. We can say it like that, or uh directly edit with the keyboard in this field
here, so which contains the formula. For example, if I do a really simple formula
like 2 + 3 and I do phase 1 preview, here you see that in my five fields,
I have my current number which will be replaced by the result of operation 5. If I cancel,
I'm going back. So this is the same principle as Magic Edit. You have a und so
a preview of the result, a possible cancellation or a validation if I click on phase
2. At that moment, my result is saved in my database as I asked it. So there,
I really started with a very simple mathematical formula.
Let's suppose that I want, a bit like the mass calculation tool that I showed you before,
to perform an operation from the number or numbers that are already present,
that is to say from the numbers 5 here. At that moment, I will use here the button called
current value. So I click on it, I have this symbol here VA for current value in
brackets which appears in my formula and this time I can do what I want. For example,
if I want to make this value even more 4, I type my mathematical formula, I
preview and I have 5 + 4 each time that appears, it will give me 9. If I validate,
it's saved. Okay? So we are already in something that looks like the
mass calculation tool. But the big difference is that, as I was saying, it is multi-column.
That is to say, instead of the current value here, I can remove it, I will be able to use
another column for example. Let's suppose that I want, uh, not to start from the sale price but
to start from another column and add something to it. Add + 4. I come here in
this tool where it is marked column to look for one of the columns already present in the table. Any one.
For example, if I want to take the value of the barcode, I can select it here. Here,
I'm going to do an operation in this sales price column that will be equal to the value of the barcode
+ 4. Well, that might not make sense, I'm not even sure that the barcodes are filled in.
We'll take something different. We'll take for example the final price including tax. There you go.
So here, I'm going to fill in my sales price excluding tax with my sale price including tax + 4. I apply,
it tells me the result and I validate. So something very simple to make
formulas, write formulas using the numbers coming from the numbers or even the
texts coming from another column present in the table. So of course, your column must
be displayed. That is to say, here, this list is dynamic. It only contains
the columns that you have included in your column configuration. If there is one that
is missing, well, you will create a new configuration or add the column in it.
As I was telling you, to type your formulas, enter your formulas,
you can either type on the keyboard, so here, I use my computer keyboard,
or use these little buttons here which allow you to insert what we call
operands. Uh so calculation tools. So the plus is obtained here, the minus, the multiply,
the divide, the percentage. You can open parentheses so uh to uh do I
don't know how to write formulas with parentheses, so formulas that are a little more
complicated. You have the period, you have the quotation mark, so we'll see that
later, which allows you to enter character strings between quotation marks. We'll see that
we can actually create formulas on character strings. You have a tool here,
the question mark which is very powerful which allows you to create and write a condition of the
style. So for example, if 0 = 1, at that point, we insert the value, I don't know, 5. Otherwise, here,
I insert the value, for example, 6. Okay? So, writing conditional calculation formulas like this is very easy
. So with a test part here, a result part if the test is successful
is true, and then the value if the result is not true. In the test part here, you can
of course use an equal condition, but also a type condition like this,
less than or greater than 1, at that point,
I insert 5, otherwise I insert 6. You can, uh, put greater than or equal. You can
put negations. So, it's this symbol here, exclamation point. So if I insert
an exclamation point here, that means it will insert not. So if 0 is not greater than or equal to 1,
at that point, I insert 5. Otherwise, I insert 6. Well, I'm not going to go into detail but see some
fairly sophisticated formulas. You also have the possibility of putting conditions of type E.
So if I insert E here in fact I will have my condition I will not put it there I will
put it here in my condition I will remove the not to understand better here
here if 0 greater than equal to 1, I put myself just after I type and I put here a second
condition if 0 is greater than 1 and 2 e= to 3 for example I will ask for 5 otherwise I will ask for 6
so condition of type e you can also have a condition of type or it is this sign which is
here. There you go. If 0 greater than 1 or 2 equals 3, at that point, I ask for 5, otherwise I ask for 6.
So, when all these mathematical tools here, let's say simple, are not
enough, you have the possibility of using more sophisticated mathematical tools here.
So the list is quite long. For example, absolute value. Calculate a duration, an age.
Have to obtain the current year, calculate cosine, sine and so on. Do rounding, rounding,
rounding down, rounding up. H compare two dates, obtain a result, obtain the
current date, check if a number is even or odd. So that is quite practical sometimes.
in precisely in combination with the question mark tool. So if
the number is even, obtain do such calculation. If the number is odd, do such other calculation.
exponential factorial the formulas log max and mine between two values. So that is very useful for
comparing two columns. You can very well say if I don't know the current stock is lower or finally
what is the greater between the current stock and the reserved stock for example and then depending on
that and well set up or not a discount a whole bunch of possibilities with max and mine. calculate
averages, take only the decimal part or only the integer part of a number, obtain the
power, the root, the sines, chance which will give you a random number and then val which
is the absolute value. Sorry, excuse me, it's not the absolute value. Val will convert you,
excuse me, a text string into its numeric value. So that's very practical to
take for example, I don't know a barcode. a barcode which in fact is not a number but
a is not a number but it's a character string. Well, you can convert it
into a numeric value from these numbers. So there are a whole bunch of mathematical tools.
So this tool, I told you earlier, it also works on
text type columns. So we're going to do an example. So I'm going to close it. This time I'm going to go
to the rental column. Here, still on these five lines. I'm going to right-click. Same
thing, Magic Formula in the selected lines and the tool opens. So, one first thing in
passing, you'll notice that the contents of the formula field are not the same as before.
Why? Because in fact, Magic Formula memorizes the formulas for each column independently.
So you find there, I find the column that I used before the formula that I used
previously when I tested on the rental column. If I close it and come back
here in the sale price, right-click, I relaunch Magic Formula, I find my previous formula.
There you go. Magic Formula on all these lines and I have my field which is empty again. So I'm going
to type a formula this time of text type. So what I would like, for example, in
this example, I'm going to take the product name and I'm going to put it. I want this
product name to be copied into the location column. So for that, I go to my column list. Here,
I'm looking for my column that contains the product name, it's this one, and I include it.
So it appears here. So don't be surprised every time if the name that's there, the value
What's there isn't exactly the column title. In fact, it's simply because it
uses the name of the field in the database, and that's the name of my table. So ultimately,
it doesn't matter, you don't need to memorize or know this type of terminology by
using the list directly, well, you choose the right column directly. So here,
I have my product name which will be copied into location, but I can concatenate or add
something else. For example, if I do more, and then I'll look for another column. For example, I'll
take the reference or supplier reference. Reference, I go here. There you go. If
I do that, I ask it to preview here, I have the name of my product plus the reference
which, which, which are contained. So I'm going to validate. We're going to check. I'm not even sure that
the products here have a reference. So 7 11 if look there for example if I take this one
mug is yet to come demo 11 I think that demo 11 is the reference of my product must be
somewhere here here demo 11 so he has concatenated my product name with my reference
here he has done that all on my c lines
So, what is interesting is that on this type of column,
you can do something other than simply concatenation. So concatenation
is the number plus. In fact, you have a whole bunch of possibilities. Here,
in this tool called string, you have a set of text processing tools.
For example, extract string, you will be able to extract a string from a finally a substring
of a string. Right, you will be able to take the last X characters. Left, the first X.
Size will give you the size in number of characters of a string. Uppercase or lowercase,
you will do string conversions. Uh here, you have a replacement tool. So,
you're looking for a substring in a string and replacing it with another string of
your choice. Convert the string, remove it by removing all accents or certain
characters or certain characters from right to left. You'll even be able, if you
're good at regular expressions, to type regular expressions to convert your
strings, modify your strings with a slightly more sophisticated formula.
So here's a set of string management tools.
Some, for example, strings start with "this is a test, this is a condition."
So you'll be able to use this in conjunction with the question mark.
So if my string starts with the character or the following string, then
fill my field with such a value, otherwise fill with another value. So there's really no
limit. That's really the advantage of Magic Formula, is that you can really enter
formulas, I don't want to say complicated but powerful enough to obtain any
type of result. In particular, to fill fields according to certain conditions,
you'll be able to do it. And then as you saw, we can add here an affinity of
formulas, excuse me, columns. So I can concatenate not just two columns
together, but I can add a third. I can concatenate with a number. So I can very
well have my location which will be equal to my name plus the reference, uh, plus its size
, width. So you can of course add text yourself, eh. For example here, if I
want to include something between the two, I go to the keyboard, I use quotation marks here
and I type my text, for example Toto, maybe a space before, a space after. At that point,
well, my location would be equal to my name plus space Toto, space reference and so on.
If I validate, then if you make a mistake, that's interesting. That's what
I did here. If I didn't do it on purpose, if you make a mistake in your formula, Merlin will
tell you. So there, he explains to me that this formula, uh, is not executable. I don't know,
I don't know yet what I did wrong. We'll look. Uh, so, he gives me some
Tips, things to check like using a period and not a comma for
decimals, always using an asterisk for multiplication and not
the letter X, and so on. Okay? All the classic typical mistakes you can
make are explained here. So, what did I do? Well, there you go, we can see it here,
it's obvious to me. I forgot to add a plus here. Uh, so it's not able to execute
Toto nothing between the two references if that's not what I'm asking for, eh. I forgot
to ask it to concatenate these two strings. Now if I validate, there you go, I get
robot print here. It was my number here, total space, space, my reference, which was worth é.
So I'm going to cancel. We're going to do some more concrete, more meaningful examples in a
moment. But before that, I'd like to finish showing you the interface, in particular, come back
to these three little buttons that you see here. So this one, question mark in blue,
simply allows you to access the online help for Magic Formula, so a page that is being
created on the c sitologue site. So that's interesting. I advise you to go
and take a look at the rest of this tutorial. The eraser pen here simply allows you to erase the formula.
Okay? And then this, which is very important, this double chevron allows you to access what in fact?
Here is a list of formulas of other formulas. In fact, all the formulas that have already been
entered and automatically memorized by Magic Formula 1 for the column in question. So
here, this left formula VA5, uh, it's a formula that I had typed previously. So I
can reselect it and I can reapply it. There you go. So left, I come back, I'm going to cancel.
What does it do? It takes the first five characters of what? Of VA. VA means
current value. So if I close, I take the five lines here and apply my
left formula V to 5. If I preview, I have the first five characters of my location.
If now I want to type another formula than this one,
then I can either modify it, but at that point it is lost. Or I click on this chevron
and I click here on the plus button. Plus allows you to add a new formula.
Minus allows you to delete the formula that is preselected here, and then this last button
here allows you to duplicate it. We'll see what that's for in a moment. So for example, if
I do plus, so here again I have my blank field and I'll be able to type my
other formula. So typically I'll take the I'll go to the right. Here I type right.
Right of what? Well, right of the current value, for example.
And then how many characters to the right? I'll take the last three characters.
If I ask for that, I'll get something like here - 7 No, there I'll get -11 and so
on. Let's preview. Here we go - 7 to 11 and then here I have the last three characters of
the printed word. So I'll show you again if I click on the chevron. Here, I have in
my list now my two formulas right and left that were entered on this column.
So, if I come back here and go to another column, for example, the name, right-click,
I don't find my left and right. Okay? Because left and right were formulas
that I entered for the location column. Here, I find a list of other formulas
that I previously typed for the no column. So once again, the formulas,
even on the texts are associated with a particular column. Now, let's imagine that I want to
use this formula here which is a little complicated, but I'm going to use it in the table
or in the location column. How can I do that? So, if I come to my location column,
I launch Magic Edit, Magic Formula, excuse me, don't get confused. I click on my double chevron and
here I realize that I don't have my formula. So how do I get it? Well, actually you
have to uncheck the filter. The filter here is this function that limits the list here to formulas.
which were created specifically for the location column. So I click on filter
to remove the filter. At that point, I end up with all the formulas in this table.
So all the formulas in all the columns in this table. Well, there are a bunch of them,
it's all the tests I did during development. So, you can reuse
the one you want. Okay? So, to help you, you can search manually
like this. You can also type here in the search field here between the two, it's not very
visible. I typed, I looked for a formula that begins with chance. I saw that there was one.
I type chance. And there, I find my Azer 515 formula directly in my list. So,
what is this formula? It's a formula that looks for a number that gives a random number
between 5 and 15. So if I preview, here I have a random number in my
location column between 5 and 15 different for each row. However, it's important to mention
that when you want to reuse a formula that was created in for another column,
I really advise you before using it in another column for which it
was created, is to duplicate it. Duplicating it means that the column, finally the formula,
excuse me, will become specific or specific to the new column. Because otherwise, in fact
when you modify the formula that was created here on for example for the column no, you
modify it and use it in location, in fact this formula will become specific to location and therefore
you will no longer find it in the column no. So in fact, you will constantly move
formulas from one column to another and in the end you will not really find it. So I
advise you if you want to use, for example, I don't know this column here, well before making
a forecast, applying it, etc., is to duplicate it. So I click here on the
duplicate button and at that moment I find myself here with the list of columns specific to location,
including the one I just duplicated. So there I can reuse it. But if I reuse it,
it will now be specific to the occasion, and even if I modify it,
it will not modify the column or the formula in the column in which I
initially created this formula. I don't know if I'm very clear, but basically the idea is
when you want to reuse formulas between several columns, choose it. So for that,
you have to remove the filter. Choose your formula and then immediately afterward, duplicate it.
So, I told you at the beginning that Magic Formula is also available not
only on the products table, but also on other tables. For example,
here the variations table. So, I don't know if these products have variations.
I'll show you a little example. Here, for example, this one, brown bear cushion. Ah,
it has two variations, one in black and one in white. Uh so as I was saying,
we can use Magic Formula in this type of column. No problem. Uh we enter the formula
we want exactly like at the top. There you go, I just type a number. I fill in my value with
my number. Well, however, what I'm going to show you is that it's a little more powerful than
that. That is to say, the columns of the table here, which we call a child table, uh, can be
filled using data taken from the top table, in the product table. Typically,
we'll do the example on the reference column here, variation reference. Let's imagine that in
my variation reference, I want to add here, sorry, I want to add at the end, uh, the name of the
manufacturer. So, the manufacturer's name, well, you don't have it in the bottom table, it doesn't exist
in this variation table. Here, we can't display it, it's not an available section.
On the other hand, we can have it in the top table. I think I've already added it. Here
, I have the manufacturer's name. Well, look, I'm going to the variation reference. Right click,
I run Magic Formula in the entire column and I come here. I'm going to delete this formula and I
'm going to tell it that I want to replace this reference with itself. So I click on
current value. I've already told you about that. I concatenate with something else. Plus I'm going to add
a space so quotation mark space quotation mark plus. And now I want to put the name of the manufacturer. So
, I come here in column and look, I haven't shown but at the top you have all
the columns up to here which are the columns of the current table in which I'm working and then
below up to the product table separator, you have the columns of the top table. So there,
I can go and find the manufacturer that I add here. And that's it. So, that's almost it.
You'll see, it's not exactly well it's not going to give exactly the result that I want. There,
if I preview, it adds me a, it doesn't add the name of the manufacturer but it adds me
its ID. This is normal because the formula uses the column actually called Manufacturer ID.
In fact, this column here, manufacturer, supplier, manufacturer, contains the ID. It displays the name because
it's more practical, but in fact the column itself, the value, is the manufacturer's ID.
So if I don't want to have the ID but the name, there's actually a little trick. I come
here at the end and I add it. So I can do it either by hand by typing a period and then
you type the displayed value like this. Or, and I think this will be more practical because
you won't remember it. So I remove that. I come here to the
channel management tools and I have to have it somewhere. Here it is at the very end. Displayed value. So I click
here. So it adds it to the end. So I'm going to remove the space to make it cleaner and
I preview it. And this time I have my manufacturer name displayed here. If I validate,
it saves in the database. So what I wanted to show you is this, is that Magic Formula
also allows you to fill data from the table of variations or the table of specific prices
with data read from the columns of the product table. And so there are
absolutely infinite possible uses. I'll show you one for references,
but it can also work on numbers. So, we can fetch prices, weights,
things like that at the top, quantities to fill data, attributes,
whatever, in short, whatever you want in the table of variations or specific prices.
So, to fully understand the full power of Magic Formula, I must now tell you
about another concept in Merlin which is the concept of what we call
calculated free columns. So a calculated free column, as its name suggests, is a free column,
that is to say, one that is not linked to the database and is only filled via a calculation.
So calculated columns, you can put five in each of the produced tables,
this one in the variation tables. So the variation table is the one
here in the image variation tab and also in the specific price table here. So
these are free columns that you can fill using a formula created in Magic Formula.
So to add calculated columns in the tables, well, as usual,
we go to column, the column configurator. We go to the product, variation or
specific price tab depending on where we want to add another column. For example,
in the product table, I'm going to add a calculated column. So, the calculated column
or calculated columns are present in this long list. Once again,
I remind you that to make searching easier here, I advise you to click on this little
button here which expands the complete list. And at that point, it's much easier
here to locate the so-called calculated column group. I expand it and I have my five possible columns
here. I'll take the second one for example, I'll put it here anywhere in your list and
I validate. So it refreshes the display and I end up with my calculated column
here automatically filled. So why is it automatically filled? Because in this
column, I had already entered a formula. If, on the other hand, I use a calculated column that
does not yet have a formula, we will do it right away, and well, we will have no content in it.
So to see the formula that was entered in the calculated column,
I right-click Magic Formula either in the selected cells, that is to say,
the first row or here in the entire column. Select all the rows and I see here the
formula that was used previously. If I want to create my own formula, for example,
I would like to fill the calculated column with the weight, the value of the weight multiplied by 2. Well
, as usual, I create a new formula. I click on more here and I type my formula.
So I go to my weight. So the weight is a column. So I will look for my weight here in
the list of columns. I'm going to multiply it by 2. I click here on the multiplication by 2 and
I preview. So I have my result here. Please understand that this result is not stored
in the database. Once again, this is not a calculate two section, it's not a
database section. It's not a column linked to the database. So validating here has no effect and
no impact on your site, eh. It's finally in any case it's purely informative. So
here I have my calculation result which is indeed equal to my weight multiplied by 2. So what
is the point of the calculated formulas? Well there are plenty of them I'll quote you even if it's just one.
I'm often asked to add a column in Merlin displaying what is called the
markup rate, which is different from the margin rate. Or some people ask me I would like to use
I would like to see my margin rate on my selling price and not my margin rate on my
purchase price. So this is a different formula from the margin rate that I've used so far.
So all this type of need can perhaps be addressed using calculated formulas. Another
example, some of you need to calculate and display the volume of
your parts so that you can define the additional shipping costs.
Well, with a calculated formula, a column calculated using a magic formula,
you can calculate the volume, which is the multiplication, as you know, of width, height,
and depth. Lots of possible uses. We'll see some examples in a moment.
So the first concrete example now, I'm going to add a column
of the type "calculate" so that I have as information the turnover achieved
for each product. So I go back to my column configurator.
So to calculate the turnover, what do I need? I need to know the
purchase price, the sale price. So that gives me my margin, my profit if you prefer, and then I
finally need to know my sales volume. So I'm going to create a new configuration,
it might be simpler, which I'll call, uh, turnover.
I validate. So that allows me to start from a slightly simpler configuration.
So I already have my Tortax sales price, I already have my purchase price. Very good. Uh, I
now need to know the quantity sold. So we're going to wrap everything up.
The quantity sold, where are we going to find that? We're probably going to go to the stock
or warehouse section here, and I have my quantity sold here. Perfect. So I'm going to put it there.
And finally, I need a column called calculated to display the result of my calculation. So
I go to calculate and I'm going to take a formula, or one of the columns
. I'm going to take the first one, I'm going to put it there, and I validate. So it refreshes.
So we're going to classify the display according to the quantity sold. There's no point in having calculations
on the products that have been sold. So here we have some up here,
8 items, 3, 2, etc. Um, and then we're going to enter our formula. So before that,
I remind you that the quantity sold here is a function of a certain duration,
eh. So quantity sold here in the last 30 days. If you don't know, the duration of 30 days
here may change. You go to the left control panel, you go to
calculation options and a little further down here you have the duration. So in the inventory management section, here you
have the duration determining the quantity sold. So here, I'm on the last month, but it
can be just the day, the week up to the full year, see all the possible dates.
So our formula now. So I come to my calculated column, I right-click,
Magic Edit in the entire column. Here, I have my previous formula. So, just an aside.
When the formula appears in red, it means that it uses a column that no longer exists in
the display. So there, I don't know which one it is. It might be this one, the physical quantity. I
think it's not included in my display. So the current formula can't be
used. So, however, it doesn't matter. I'm going to create a new one. I'll do more and
here I have my field towards my new formula. So what do I want to calculate? I said
the turnover. So what is the turnover? So I open a parenthesis,
it's my selling price minus my purchase price. So the selling price excluding tax minus
my purchase price. I close my parenthesis. So that's my profit. And I multiply that by the
number of items sold. So the quantity sold in the last 30 days. Quantity sold in the last 30 days
here. There, that's my turnover over this period. I validate. And there,
I validate. So we're on a calculated type column. So once again, it's not saved
in the database but it's information. So what's also important to understand is
that if at some point, uh, I redisplay on refresh the display or I change category
et cetera, I display other products, my calculation in my calculated formula is still there. eh,
that's one of the advantages of calculated formulas, is that the formula is memorized in the column
and always reapplied. This is not the case for other formulas. So if, for example, I put a
formula on the reference column, uh, there is certainly one, you see, there is one
here. In fact, this formula is not automatically reapplied each time I refresh
the display because otherwise, I would systematically modify the data in the database each time
I redisplay it. Let's imagine that I have a formula that makes purchase price equal purchase price + 2.
Well, each time I redisplay the purchase price, I will add €2 to my purchase price. So in the end,
I would have anything as data. So that's why, once again, the
formulas on the database columns are not automatically reapplied.
They are only applied when you privatize and validate.
This is not the case for calculated columns. A calculated column has a formula that is
here. And each time you refresh the display, this formula is automatically reapplied.
So, a second fairly simple example. Let's imagine that you need to fill in your column and
cotax from the purchase price or the sale price. So, still the same,
I go to my column configuration, I'm going to add the section that I want to
modify. So in this case, the purchase price. So, still the same technique. I wrap it,
I go to the prices, I'm going to add my ecotax. I put it somewhere here. I validate.
I have my ecotax, I'm going to enter my formula. So, for example, I would like my offset from my
sale price excluding tax. Well, I right-click, Magic Edit in the entire column. I go
here. Purchase price, rather sale price. I said 3% of the sale price. No,
not of the purchase price. So, I make my sale price plus what? So, actually 3% of the price.
purchase price. So here, I'm going to open a parenthesis or I'm multiplying by 1 + 3%. Well, there are
different ways to do percentages. I don't know how you do it.
I do it like this. I add it myself, so the sale price x 3 divided by 100.
So that's my price plus, uh, the price 3%. Well, actually, I made a mistake in the calculation.
I just want 3% of the purchase price. I'm telling you something stupid. I don't want the purchase price + 3
%. So, I'm going to remove that and I'm going to remove the end parenthesis. Otherwise, it's not wrong.
It's wrong. There you go, I apply it and immediately you have the result and I validate.
So when I validate, save it in the database. So note the saving time here,
it took about a second, a second and a half. It was for 67 lines. There you go,
that's roughly the order of magnitude of Magic Formula's speed, eh. That's what I told you
at the beginning. It's a little slower than the tool modified here by calculation, uh, older. But
on the other hand, we have incomparable flexibility. I hope you'll have understood that by now. So
there you go, I've filled in my ecotax equal to 3% of my selling price just with a very simple formula.
So, I'm now talking to you about using Magic Formula's calculated columns in
another context, but simply to mention that we can
also do Magic Formula calculations in the import part. Here, I've displayed the contents
of a file and if you look here, you have a column, it's the quantity column which has a
small logo with a calculator. This means that on this quantity column, I've already set up
a Magic Formula formula. So if I right-click here and select Magic Formula,
you don't see it, but it's at the bottom. I see that I actually have a setup formula
on this column. This formula is just an addition of 10. In fact, I take the
current value + 10. What does that mean in the context of a file? It means that it's the
value read in the file, so the quantity in stock in the file, and I add 10. Well,
it's a somewhat unusual use, but simply to show you that we can do
calculations on the data in the file before importing. So something that you were forced
to do until now in Excel, you couldn't do in Merlin. Now it's
possible. You can basically modify the data in the Excel file directly from Merlin in
real time, dynamically. And this formula will be applied automatically. So even if
tomorrow my file changes or I do automatic imports with the banificateur, when it
fetches tomorrow's file of the stock update, if I know that I have to
add 10 to my quantity because I don't know I have 10 in stock at home, well in fact I
can do it with a formula. So that's a first possible use. So it's this magic
edit, sorry, magic formula, not its name. So, it exists on all the columns of your
file. Uh for example, you could very well set up formulas uh like uh here,
I see low stock alert. Uh I can very well put a formula in it like if my quantity
is less than uh I don't know 10 at that moment checked looks like low stock. So it's going to be a
formula a little bit like that. There, I really do it in real time without having thought,
I admit. I'm going to put a condition with the question mark. Here, I'm going to
put in the condition if my quantity is less than 10 at that moment. So,
for the example, I see that I have numbers that are much larger. I'm going to put I'm going
to take the value of 2000. There you go. So if less than 2000, well I want to set
a low stock alert. So I put 1 otherwise I put zero. There you go, I do that. I preview
and look at this moment here I'm less than 2000 so it's checked. So I only applied it
to one line. I do I'm going to select all my lines. I'm going to reapply my formula.
I choose the image that says in the entire column.
My formula is still there. I reapply it
and this time, you see all the products whose quantity is lower than my
ceiling of 2000 are checked at the alert level and the others are not. So, what is
important to show you at this point, even if it remains an example, I will cancel, is that
here what was grayed out earlier, I didn't tell you about it, this little button, this little
automatic function. If I check it, it means that I put my formula in my, uh, my model.
That is to say that if, uh, tomorrow I actually reapply, I reimport another
file but with the same source, the same model, well, I will automatically have my formula which
will be reapplied. Hey, that means it's a little like I don't know if you remember when
you do a filter. For example, here, I can filter on this column. So to set
up a filter limited to certain lines in my import, I could memorize it in the
task settings and apply it automatically. There you go. So, this function is a bit
like this automatic tool that we find here in Magic Formula. So
if you want your formula to be applied automatically, you have to check automatic.
So, in addition to being able to apply formulas like this to your files, so to
your import tools, to columns in the file, uh Merlin goes a little further
and allows you to also add free calculated formulas in this table here. These are, as
I told you earlier, formulas that are not linked to the database
. The difference here is that we will be able to add column shapes, so in
the imported file. That is to say, currently, I am using a file that has certain columns,
those that are here. Let's imagine that a column is missing from my file. For example,
I don't have the references for the variations. At that point, I can build this column
automatically by adding a so-called calculated free column. To do this, I click here on the
new button there which is called calculated column which didn't exist until now. This adds a
column here at the end of my model which always has a generic name like layer. And then there,
it's the number of the column itself. And then this column, that's what's wonderful,
you can map it to a heading. So I'm going to say for example that this will be I don't know,
I could put the reference, I could put whatever I want. Uh well,
we'll say reference. Although I've already used this one, I think. So we'll put
minimum quantity for example. There you go. So I'm going to map that to minimum quantity. I click to
use it and I come back here. So what's going to happen? So I'm going to redisplay.
There you go, it's finished. So I end up with this time in my step 3 an
additional minimum quantity column which contains zero for the moment because it is not linked it
is not linked to anything in the file there is no minimum quantity column in the file and
then there is not yet a formula but let's say I can put a formula so magic formula
in the whole column. Let's imagine that I want as minimum quantity and well the quantity finally
or 10% of the quantity or the quantity - 20 whatever. Basically, you put your formula. So,
I take the quantity, I want to multiply it by go, I want I want 1% of the
minimum quantity the sorry, I want the minimum quantity to be equal to 1% of the quantity. So
quantity x 1%, I put it in automatic mode, I preview. It gives me my future quantity. I
validate. Well there, I could have added a rounding because it was not clean in my
formula. Well, however Merlin, he knows that it's a quantity so he rounds it up by himself. There you go.
So there, I have my automatic formula. What does that mean? It means that if I redisplay the
content, I can do it but you'll see, it will redisplay the quantity, it will
recalculate on its own. Similarly, if you take a new file with the same mapping model,
it will do the same calculation. So, well, while it recalculates, so what I was telling you,
these columns calculated with Magic Formula in the import tool, it allows you to add
columns that don't exist in your Excel file, to fill them with a formula,
so for texts or for numbers, it works for both. And then to import the
result of this calculation into one of the database sections. So, there are plenty of
possible examples. You can use this type of formula, for example, to calculate your sales prices
with a margin rate or a margin coefficient or a brand coefficient. So there,
you can really use any margin type formula you want. So more
simply, the margin rate on purchase price that Merlin offers you so far. So,
another example, a little more sophisticated but quite typical, I come across quite often. Let's imagine
that you have a variation file and instead of having a column with the sizes and a
column with the colors, for example, you have like here, you see, a single column
that contains the sizes, comma, colors, comma, another attribute, etc. Basically,
a kind of mix of all the attributes in the same column. Well, until now, we were
forced manually in Excel to go, uh, add two columns, to do catenation corners,
string explosions, etc. to go and put everything before the first comma in one
column, everything after the second, sorry, after the first comma in another column.
Well, basically, that was work up front. Well now, you can do it directly in
Merlin here with calculated columns and one of the magic formulas. So, how do we do
that? Well, for example here, so this column that contains the S, the oranges. At the mapping level.
Well, you map it to anything. Here, I mapped it, where do I find it? Uh,
here, it's called the attribute list. I mapped it to location. Okay, choose something
in the database that can receive this data that doesn't have any impact on you.
It can be a characteristic, it can be something else. Once you've done that,
what you do is click on calculated column. This button twice, it will adjust
two columns like this. uh columns, layers, columns, you map them to attribute ID or not,
as if you had columns containing your attributes, one to size, the other to color and
you check them. What does that give you? When you display, you see your
list of attributes here that are mapped to location. Okay, that's not very important. And then above all
you have here your two other columns, size and color. So I've already done the work for
one and I haven't done it for the other. So what does the work consist of? It consists
of putting a formula in the column. So I'm going to show you what this formula is.
It's a formula that uses extract string. So extract string is one of the functions
you have here. So extract string extracts a string from a substring from another
string using a separator. So basically, I put extract string location which is my
column here. So I'm going to look for the value it has in it and I ask it to take the first
string before the separator. Here in quotation marks, it's a comma. So I ask it to take
the first part after before the first comma. So I checked that as automatic
and it gives me exactly this result. And then for the second column, we're going to do it again
together. Magic formula in the whole column. And there the formula is the same. It's going to be extracted
string location but I put two in place of 1. So I the second string so the one
after the first comma. Same comma separator, I have to check automatic.
You see that when we check automatic, that we apply the formula, we have this calculator logo
which applies and I preview and I validate. And when I validate, what happens? Look,
uh, it even recognized the color values ​​among the color attributes
that already exist. Here we find the color IDs. So exactly as if we had
two columns, a size, a color in our file. Except that here, we exploded it.
By dynamically exploding it into two separate columns. So we can really
do everything with this tool. We can even, as I was telling you, do without having a key column for the
variations. For example, it's often the case that we have a column that contains the product references
and then we are missing the column containing the variation references or the opposite.
Well, with the autocalculated columns plus Magic Formula, you can create the
column you are missing and map it to one of the two synchronization keys. That's it,
I'll stop there for this tool. The number of possibilities is almost infinite,
so I'll let you discover them. Please give me feedback on whether you like the tool or not. If you have
any comments or new ideas, I will be happy to implement them.