WEBVTT Kind: captions Language: en-US 00:00:10.720 --> 00:00:14.840 Hello, I'm Franck, the director of Sitolog. I'm going to talk to you today 00:00:14.840 --> 00:00:21.040 about the Magic Formula tool. Magic Formula is a complementary tool to the one I showed in 00:00:21.040 --> 00:00:26.400 the tutorial just before. Remember, if you looked at it, it was the tool called 00:00:26.400 --> 00:00:31.560 modified by calculation that appears here below the tables. And so this 00:00:31.560 --> 00:00:37.560 modified by calculation tool allows you to mass modify numbers, numerics. For example, if I 00:00:37.560 --> 00:00:46.800 want to modify these few sales prices here, I could, for example, add 5% like this. 00:00:46.800 --> 00:00:54.080 sales price + 5%, I do more and I had my prices here increasing by 5%. So, 00:00:54.080 --> 00:01:00.800 this calculation tool is very interesting because of its speed, that is to say that when you perform 00:01:00.800 --> 00:01:05.600 this type of calculation on hundreds or even thousands of lines, it's almost instantaneous. 00:01:05.600 --> 00:01:12.400 It is really optimized to perform calculations very, very quickly. However, this tool for 00:01:12.400 --> 00:01:17.080 me has some weak points. The first weakness is that it only works on 00:01:17.080 --> 00:01:22.640 numbers. Okay? You can't perform operations with this tool on 00:01:22.640 --> 00:01:28.280 text columns. You have to use Magic Edit. But sometimes, we need to perform certain operations 00:01:28.280 --> 00:01:32.560 that mix text and numbers. We'll see that later. So, we can't do that 00:01:32.560 --> 00:01:37.800 with this tool below. The second weakness is that it's limited to only a few columns. 00:01:37.800 --> 00:01:41.640 That's because the scripts behind it are hyper-optimized and are therefore 00:01:41.640 --> 00:01:46.080 done one by one for each column. So I couldn't do it for all the columns. I 00:01:46.080 --> 00:01:51.440 did it on the main numeric columns. And then the third weakness of this tool 00:01:51.440 --> 00:01:56.200 is that it's not multi-column. That is to say, here I performed an operation that modifies the 00:01:56.200 --> 00:02:00.920 sale price using the sale price as a parameter. But if I wanted, for example, 00:02:00.920 --> 00:02:07.920 to calculate the cotaxes here based on the sale price, well, I can't. Okay? We don't 00:02:07.920 --> 00:02:13.200 have multiple column headings to use as parameters in the 00:02:13.200 --> 00:02:18.800 formula. So that's why we developed a new tool called Magic Formula 1 00:02:18.800 --> 00:02:23.720 that compensates for all these flaws. So for me, the two are complementary. Magic Formula 1 is 00:02:23.720 --> 00:02:27.520 much more powerful, you'll see that it allows you to do a lot more things. On the other hand, 00:02:27.520 --> 00:02:32.600 it's slower to execute. So, that doesn't mean it's slow, but simply 00:02:32.600 --> 00:02:37.840 instead of executing something, a complicated calculation on a hundred lines in less than 2 seconds, 00:02:37.840 --> 00:02:41.920 maybe it will take 15 seconds. That's still quite reasonable when you see the 00:02:41.920 --> 00:02:49.560 time savings it brings, but it's still a fairly different approach to this calculation tool. 00:02:49.560 --> 00:02:54.240 So, the first thing we're going to see is how to execute Magic Formula. Magic Formula 00:02:54.240 --> 00:02:59.760 is like Magic Edit. It's executed by right-clicking in a column. For example, in 00:02:59.760 --> 00:03:04.360 the sales price column. Here, if I want to modify the few rows that I have selected there, 00:03:04.360 --> 00:03:10.080 I right-click on one of these rows. I have my context menu that appears and you 00:03:10.080 --> 00:03:15.200 see here two menus that appear. Magic Formula in all the selected cells 00:03:15.200 --> 00:03:19.760 of this column which will act only on the grayed-out rows and then Magic Formula in 00:03:19.760 --> 00:03:25.920 the entire column which will act on all the rows currently present in the table. So I will 00:03:25.920 --> 00:03:35.800 choose the first one and I have my tool that appears here. Know that Magic Formula is available in 00:03:35.800 --> 00:03:41.120 almost all the columns of the table or tables, including the text columns. We will see that 00:03:41.120 --> 00:03:48.880 in a moment. So, first way of using. So what you need to understand rather is that 00:03:48.880 --> 00:03:56.280 in this interface here, you have tools at the top that allow you to enter a formula without 00:03:56.280 --> 00:04:03.480 typing on the keyboard. We can say it like that, or uh directly edit with the keyboard in this field 00:04:03.480 --> 00:04:09.840 here, so which contains the formula. For example, if I do a really simple formula 00:04:09.840 --> 00:04:19.640 like 2 + 3 and I do phase 1 preview, here you see that in my five fields, 00:04:19.640 --> 00:04:26.720 I have my current number which will be replaced by the result of operation 5. If I cancel, 00:04:26.720 --> 00:04:32.320 I'm going back. So this is the same principle as Magic Edit. You have a und so 00:04:32.320 --> 00:04:40.200 a preview of the result, a possible cancellation or a validation if I click on phase 00:04:40.200 --> 00:04:46.480 2. At that moment, my result is saved in my database as I asked it. So there, 00:04:46.480 --> 00:04:50.480 I really started with a very simple mathematical formula. 00:04:50.480 --> 00:04:56.320 Let's suppose that I want, a bit like the mass calculation tool that I showed you before, 00:04:56.320 --> 00:05:00.640 to perform an operation from the number or numbers that are already present, 00:05:00.640 --> 00:05:06.560 that is to say from the numbers 5 here. At that moment, I will use here the button called 00:05:06.560 --> 00:05:12.240 current value. So I click on it, I have this symbol here VA for current value in 00:05:12.240 --> 00:05:16.480 brackets which appears in my formula and this time I can do what I want. For example, 00:05:16.480 --> 00:05:22.440 if I want to make this value even more 4, I type my mathematical formula, I 00:05:22.440 --> 00:05:29.280 preview and I have 5 + 4 each time that appears, it will give me 9. If I validate, 00:05:29.280 --> 00:05:34.880 it's saved. Okay? So we are already in something that looks like the 00:05:34.880 --> 00:05:39.440 mass calculation tool. But the big difference is that, as I was saying, it is multi-column. 00:05:39.440 --> 00:05:43.920 That is to say, instead of the current value here, I can remove it, I will be able to use 00:05:43.920 --> 00:05:51.520 another column for example. Let's suppose that I want, uh, not to start from the sale price but 00:05:51.520 --> 00:05:57.480 to start from another column and add something to it. Add + 4. I come here in 00:05:57.480 --> 00:06:05.120 this tool where it is marked column to look for one of the columns already present in the table. Any one. 00:06:06.640 --> 00:06:13.520 For example, if I want to take the value of the barcode, I can select it here. Here, 00:06:13.520 --> 00:06:17.240 I'm going to do an operation in this sales price column that will be equal to the value of the barcode 00:06:17.240 --> 00:06:21.840 + 4. Well, that might not make sense, I'm not even sure that the barcodes are filled in. 00:06:21.840 --> 00:06:27.280 We'll take something different. We'll take for example the final price including tax. There you go. 00:06:27.280 --> 00:06:33.800 So here, I'm going to fill in my sales price excluding tax with my sale price including tax + 4. I apply, 00:06:33.800 --> 00:06:41.680 it tells me the result and I validate. So something very simple to make 00:06:41.680 --> 00:06:48.320 formulas, write formulas using the numbers coming from the numbers or even the 00:06:48.320 --> 00:06:54.640 texts coming from another column present in the table. So of course, your column must 00:06:54.640 --> 00:06:58.160 be displayed. That is to say, here, this list is dynamic. It only contains 00:06:58.160 --> 00:07:01.840 the columns that you have included in your column configuration. If there is one that 00:07:01.840 --> 00:07:11.760 is missing, well, you will create a new configuration or add the column in it. 00:07:11.760 --> 00:07:14.520 As I was telling you, to type your formulas, enter your formulas, 00:07:14.520 --> 00:07:19.080 you can either type on the keyboard, so here, I use my computer keyboard, 00:07:19.080 --> 00:07:26.320 or use these little buttons here which allow you to insert what we call 00:07:26.320 --> 00:07:34.200 operands. Uh so calculation tools. So the plus is obtained here, the minus, the multiply, 00:07:34.200 --> 00:07:42.600 the divide, the percentage. You can open parentheses so uh to uh do I 00:07:42.600 --> 00:07:47.240 don't know how to write formulas with parentheses, so formulas that are a little more 00:07:47.240 --> 00:07:52.040 complicated. You have the period, you have the quotation mark, so we'll see that 00:07:52.040 --> 00:07:57.240 later, which allows you to enter character strings between quotation marks. We'll see that 00:07:57.240 --> 00:08:02.360 we can actually create formulas on character strings. You have a tool here, 00:08:02.360 --> 00:08:08.880 the question mark which is very powerful which allows you to create and write a condition of the 00:08:08.880 --> 00:08:20.880 style. So for example, if 0 = 1, at that point, we insert the value, I don't know, 5. Otherwise, here, 00:08:20.880 --> 00:08:28.760 I insert the value, for example, 6. Okay? So, writing conditional calculation formulas like this is very easy 00:08:28.760 --> 00:08:37.240 . So with a test part here, a result part if the test is successful 00:08:37.240 --> 00:08:44.400 is true, and then the value if the result is not true. In the test part here, you can 00:08:44.400 --> 00:08:50.800 of course use an equal condition, but also a type condition like this, 00:08:50.800 --> 00:09:00.440 less than or greater than 1, at that point, 00:09:00.440 --> 00:09:08.040 I insert 5, otherwise I insert 6. You can, uh, put greater than or equal. You can 00:09:08.040 --> 00:09:14.000 put negations. So, it's this symbol here, exclamation point. So if I insert 00:09:14.000 --> 00:09:21.200 an exclamation point here, that means it will insert not. So if 0 is not greater than or equal to 1, 00:09:21.200 --> 00:09:25.520 at that point, I insert 5. Otherwise, I insert 6. Well, I'm not going to go into detail but see some 00:09:25.520 --> 00:09:33.760 fairly sophisticated formulas. You also have the possibility of putting conditions of type E. 00:09:34.440 --> 00:09:40.560 So if I insert E here in fact I will have my condition I will not put it there I will 00:09:40.560 --> 00:09:45.600 put it here in my condition I will remove the not to understand better here 00:09:45.600 --> 00:09:51.400 here if 0 greater than equal to 1, I put myself just after I type and I put here a second 00:09:51.400 --> 00:09:59.960 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 00:09:59.960 --> 00:10:04.920 so condition of type e you can also have a condition of type or it is this sign which is 00:10:04.920 --> 00:10:14.400 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. 00:10:14.400 --> 00:10:17.840 So, when all these mathematical tools here, let's say simple, are not 00:10:17.840 --> 00:10:25.560 enough, you have the possibility of using more sophisticated mathematical tools here. 00:10:26.400 --> 00:10:35.200 So the list is quite long. For example, absolute value. Calculate a duration, an age. 00:10:35.200 --> 00:10:41.600 Have to obtain the current year, calculate cosine, sine and so on. Do rounding, rounding, 00:10:41.600 --> 00:10:50.160 rounding down, rounding up. H compare two dates, obtain a result, obtain the 00:10:50.160 --> 00:10:55.040 current date, check if a number is even or odd. So that is quite practical sometimes. 00:10:55.760 --> 00:11:00.640 in precisely in combination with the question mark tool. So if 00:11:00.640 --> 00:11:06.160 the number is even, obtain do such calculation. If the number is odd, do such other calculation. 00:11:06.840 --> 00:11:13.160 exponential factorial the formulas log max and mine between two values. So that is very useful for 00:11:13.160 --> 00:11:21.720 comparing two columns. You can very well say if I don't know the current stock is lower or finally 00:11:21.720 --> 00:11:25.920 what is the greater between the current stock and the reserved stock for example and then depending on 00:11:25.920 --> 00:11:32.920 that and well set up or not a discount a whole bunch of possibilities with max and mine. calculate 00:11:32.920 --> 00:11:37.520 averages, take only the decimal part or only the integer part of a number, obtain the 00:11:37.520 --> 00:11:45.200 power, the root, the sines, chance which will give you a random number and then val which 00:11:45.200 --> 00:11:50.360 is the absolute value. Sorry, excuse me, it's not the absolute value. Val will convert you, 00:11:50.360 --> 00:11:57.040 excuse me, a text string into its numeric value. So that's very practical to 00:11:57.040 --> 00:12:01.440 take for example, I don't know a barcode. a barcode which in fact is not a number but 00:12:01.440 --> 00:12:05.640 a is not a number but it's a character string. Well, you can convert it 00:12:05.640 --> 00:12:14.280 into a numeric value from these numbers. So there are a whole bunch of mathematical tools. 00:12:14.280 --> 00:12:17.560 So this tool, I told you earlier, it also works on 00:12:17.560 --> 00:12:21.880 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 00:12:21.880 --> 00:12:26.280 to the rental column. Here, still on these five lines. I'm going to right-click. Same 00:12:26.280 --> 00:12:35.320 thing, Magic Formula in the selected lines and the tool opens. So, one first thing in 00:12:35.320 --> 00:12:40.680 passing, you'll notice that the contents of the formula field are not the same as before. 00:12:40.680 --> 00:12:46.600 Why? Because in fact, Magic Formula memorizes the formulas for each column independently. 00:12:46.600 --> 00:12:50.960 So you find there, I find the column that I used before the formula that I used 00:12:50.960 --> 00:12:56.760 previously when I tested on the rental column. If I close it and come back 00:12:56.760 --> 00:13:05.960 here in the sale price, right-click, I relaunch Magic Formula, I find my previous formula. 00:13:05.960 --> 00:13:13.040 There you go. Magic Formula on all these lines and I have my field which is empty again. So I'm going 00:13:13.040 --> 00:13:18.360 to type a formula this time of text type. So what I would like, for example, in 00:13:18.360 --> 00:13:23.560 this example, I'm going to take the product name and I'm going to put it. I want this 00:13:23.560 --> 00:13:30.680 product name to be copied into the location column. So for that, I go to my column list. Here, 00:13:30.680 --> 00:13:36.280 I'm looking for my column that contains the product name, it's this one, and I include it. 00:13:36.280 --> 00:13:41.640 So it appears here. So don't be surprised every time if the name that's there, the value 00:13:41.640 --> 00:13:46.680 What's there isn't exactly the column title. In fact, it's simply because it 00:13:46.680 --> 00:13:54.880 uses the name of the field in the database, and that's the name of my table. So ultimately, 00:13:54.880 --> 00:13:59.720 it doesn't matter, you don't need to memorize or know this type of terminology by 00:13:59.720 --> 00:14:05.320 using the list directly, well, you choose the right column directly. So here, 00:14:05.320 --> 00:14:10.600 I have my product name which will be copied into location, but I can concatenate or add 00:14:10.600 --> 00:14:17.000 something else. For example, if I do more, and then I'll look for another column. For example, I'll 00:14:17.000 --> 00:14:25.160 take the reference or supplier reference. Reference, I go here. There you go. If 00:14:25.160 --> 00:14:31.040 I do that, I ask it to preview here, I have the name of my product plus the reference 00:14:31.040 --> 00:14:36.920 which, which, which are contained. So I'm going to validate. We're going to check. I'm not even sure that 00:14:36.920 --> 00:14:47.400 the products here have a reference. So 7 11 if look there for example if I take this one 00:14:47.400 --> 00:14:58.040 mug is yet to come demo 11 I think that demo 11 is the reference of my product must be 00:14:58.040 --> 00:15:07.560 somewhere here here demo 11 so he has concatenated my product name with my reference 00:15:07.560 --> 00:15:09.520 here he has done that all on my c lines 00:15:12.160 --> 00:15:20.320 So, what is interesting is that on this type of column, 00:15:20.320 --> 00:15:24.000 you can do something other than simply concatenation. So concatenation 00:15:24.000 --> 00:15:28.800 is the number plus. In fact, you have a whole bunch of possibilities. Here, 00:15:28.800 --> 00:15:36.880 in this tool called string, you have a set of text processing tools. 00:15:37.720 --> 00:15:42.000 For example, extract string, you will be able to extract a string from a finally a substring 00:15:42.000 --> 00:15:49.000 of a string. Right, you will be able to take the last X characters. Left, the first X. 00:15:49.000 --> 00:15:54.280 Size will give you the size in number of characters of a string. Uppercase or lowercase, 00:15:54.280 --> 00:15:59.200 you will do string conversions. Uh here, you have a replacement tool. So, 00:15:59.200 --> 00:16:04.560 you're looking for a substring in a string and replacing it with another string of 00:16:04.560 --> 00:16:10.120 your choice. Convert the string, remove it by removing all accents or certain 00:16:10.120 --> 00:16:16.120 characters or certain characters from right to left. You'll even be able, if you 00:16:16.120 --> 00:16:20.480 're good at regular expressions, to type regular expressions to convert your 00:16:20.480 --> 00:16:28.720 strings, modify your strings with a slightly more sophisticated formula. 00:16:28.720 --> 00:16:32.320 So here's a set of string management tools. 00:16:33.120 --> 00:16:40.280 Some, for example, strings start with "this is a test, this is a condition." 00:16:40.280 --> 00:16:44.480 So you'll be able to use this in conjunction with the question mark. 00:16:44.480 --> 00:16:51.000 So if my string starts with the character or the following string, then 00:16:51.000 --> 00:16:56.360 fill my field with such a value, otherwise fill with another value. So there's really no 00:16:56.360 --> 00:17:01.360 limit. That's really the advantage of Magic Formula, is that you can really enter 00:17:01.360 --> 00:17:05.680 formulas, I don't want to say complicated but powerful enough to obtain any 00:17:05.680 --> 00:17:11.240 type of result. In particular, to fill fields according to certain conditions, 00:17:11.240 --> 00:17:17.080 you'll be able to do it. And then as you saw, we can add here an affinity of 00:17:17.080 --> 00:17:22.480 formulas, excuse me, columns. So I can concatenate not just two columns 00:17:22.480 --> 00:17:27.000 together, but I can add a third. I can concatenate with a number. So I can very 00:17:27.000 --> 00:17:34.080 well have my location which will be equal to my name plus the reference, uh, plus its size 00:17:34.080 --> 00:17:39.320 , width. So you can of course add text yourself, eh. For example here, if I 00:17:39.320 --> 00:17:47.480 want to include something between the two, I go to the keyboard, I use quotation marks here 00:17:47.480 --> 00:17:53.280 and I type my text, for example Toto, maybe a space before, a space after. At that point, 00:17:53.280 --> 00:18:01.440 well, my location would be equal to my name plus space Toto, space reference and so on. 00:18:01.440 --> 00:18:06.520 If I validate, then if you make a mistake, that's interesting. That's what 00:18:06.520 --> 00:18:12.560 I did here. If I didn't do it on purpose, if you make a mistake in your formula, Merlin will 00:18:12.560 --> 00:18:17.240 tell you. So there, he explains to me that this formula, uh, is not executable. I don't know, 00:18:17.240 --> 00:18:21.280 I don't know yet what I did wrong. We'll look. Uh, so, he gives me some 00:18:21.280 --> 00:18:25.480 Tips, things to check like using a period and not a comma for 00:18:25.480 --> 00:18:29.840 decimals, always using an asterisk for multiplication and not 00:18:29.840 --> 00:18:35.720 the letter X, and so on. Okay? All the classic typical mistakes you can 00:18:35.720 --> 00:18:41.360 make are explained here. So, what did I do? Well, there you go, we can see it here, 00:18:41.360 --> 00:18:47.720 it's obvious to me. I forgot to add a plus here. Uh, so it's not able to execute 00:18:47.720 --> 00:18:52.040 Toto nothing between the two references if that's not what I'm asking for, eh. I forgot 00:18:52.040 --> 00:18:57.280 to ask it to concatenate these two strings. Now if I validate, there you go, I get 00:18:57.280 --> 00:19:09.080 robot print here. It was my number here, total space, space, my reference, which was worth é. 00:19:09.080 --> 00:19:15.280 So I'm going to cancel. We're going to do some more concrete, more meaningful examples in a 00:19:15.280 --> 00:19:21.440 moment. But before that, I'd like to finish showing you the interface, in particular, come back 00:19:21.440 --> 00:19:28.080 to these three little buttons that you see here. So this one, question mark in blue, 00:19:28.080 --> 00:19:32.720 simply allows you to access the online help for Magic Formula, so a page that is being 00:19:32.720 --> 00:19:38.360 created on the c sitologue site. So that's interesting. I advise you to go 00:19:38.360 --> 00:19:47.720 and take a look at the rest of this tutorial. The eraser pen here simply allows you to erase the formula. 00:19:47.720 --> 00:19:56.560 Okay? And then this, which is very important, this double chevron allows you to access what in fact? 00:19:56.560 --> 00:20:03.480 Here is a list of formulas of other formulas. In fact, all the formulas that have already been 00:20:03.480 --> 00:20:10.280 entered and automatically memorized by Magic Formula 1 for the column in question. So 00:20:10.280 --> 00:20:16.480 here, this left formula VA5, uh, it's a formula that I had typed previously. So I 00:20:16.480 --> 00:20:23.200 can reselect it and I can reapply it. There you go. So left, I come back, I'm going to cancel. 00:20:23.200 --> 00:20:28.520 What does it do? It takes the first five characters of what? Of VA. VA means 00:20:28.520 --> 00:20:38.680 current value. So if I close, I take the five lines here and apply my 00:20:38.680 --> 00:20:45.320 left formula V to 5. If I preview, I have the first five characters of my location. 00:20:46.080 --> 00:20:48.960 If now I want to type another formula than this one, 00:20:48.960 --> 00:20:55.640 then I can either modify it, but at that point it is lost. Or I click on this chevron 00:20:55.640 --> 00:21:01.200 and I click here on the plus button. Plus allows you to add a new formula. 00:21:01.200 --> 00:21:06.360 Minus allows you to delete the formula that is preselected here, and then this last button 00:21:06.360 --> 00:21:10.360 here allows you to duplicate it. We'll see what that's for in a moment. So for example, if 00:21:10.360 --> 00:21:17.240 I do plus, so here again I have my blank field and I'll be able to type my 00:21:17.240 --> 00:21:26.120 other formula. So typically I'll take the I'll go to the right. Here I type right. 00:21:26.120 --> 00:21:30.040 Right of what? Well, right of the current value, for example. 00:21:30.600 --> 00:21:35.920 And then how many characters to the right? I'll take the last three characters. 00:21:35.920 --> 00:21:42.560 If I ask for that, I'll get something like here - 7 No, there I'll get -11 and so 00:21:42.560 --> 00:21:48.680 on. Let's preview. Here we go - 7 to 11 and then here I have the last three characters of 00:21:48.680 --> 00:21:55.320 the printed word. So I'll show you again if I click on the chevron. Here, I have in 00:21:55.320 --> 00:22:03.000 my list now my two formulas right and left that were entered on this column. 00:22:03.000 --> 00:22:11.480 So, if I come back here and go to another column, for example, the name, right-click, 00:22:11.480 --> 00:22:17.880 I don't find my left and right. Okay? Because left and right were formulas 00:22:17.880 --> 00:22:23.120 that I entered for the location column. Here, I find a list of other formulas 00:22:23.120 --> 00:22:27.000 that I previously typed for the no column. So once again, the formulas, 00:22:27.000 --> 00:22:33.280 even on the texts are associated with a particular column. Now, let's imagine that I want to 00:22:33.280 --> 00:22:39.320 use this formula here which is a little complicated, but I'm going to use it in the table 00:22:39.320 --> 00:22:45.520 or in the location column. How can I do that? So, if I come to my location column, 00:22:45.520 --> 00:22:52.280 I launch Magic Edit, Magic Formula, excuse me, don't get confused. I click on my double chevron and 00:22:52.280 --> 00:22:57.160 here I realize that I don't have my formula. So how do I get it? Well, actually you 00:22:57.160 --> 00:23:04.560 have to uncheck the filter. The filter here is this function that limits the list here to formulas. 00:23:04.560 --> 00:23:10.400 which were created specifically for the location column. So I click on filter 00:23:10.400 --> 00:23:16.720 to remove the filter. At that point, I end up with all the formulas in this table. 00:23:16.720 --> 00:23:20.320 So all the formulas in all the columns in this table. Well, there are a bunch of them, 00:23:20.320 --> 00:23:27.520 it's all the tests I did during development. So, you can reuse 00:23:27.520 --> 00:23:33.640 the one you want. Okay? So, to help you, you can search manually 00:23:33.640 --> 00:23:43.560 like this. You can also type here in the search field here between the two, it's not very 00:23:43.560 --> 00:23:47.120 visible. I typed, I looked for a formula that begins with chance. I saw that there was one. 00:23:47.120 --> 00:23:53.120 I type chance. And there, I find my Azer 515 formula directly in my list. So, 00:23:53.120 --> 00:23:59.480 what is this formula? It's a formula that looks for a number that gives a random number 00:23:59.480 --> 00:24:06.240 between 5 and 15. So if I preview, here I have a random number in my 00:24:06.240 --> 00:24:14.760 location column between 5 and 15 different for each row. However, it's important to mention 00:24:14.760 --> 00:24:20.840 that when you want to reuse a formula that was created in for another column, 00:24:20.840 --> 00:24:26.200 I really advise you before using it in another column for which it 00:24:26.200 --> 00:24:32.240 was created, is to duplicate it. Duplicating it means that the column, finally the formula, 00:24:32.240 --> 00:24:37.920 excuse me, will become specific or specific to the new column. Because otherwise, in fact 00:24:37.920 --> 00:24:43.840 when you modify the formula that was created here on for example for the column no, you 00:24:43.840 --> 00:24:49.040 modify it and use it in location, in fact this formula will become specific to location and therefore 00:24:49.040 --> 00:24:54.600 you will no longer find it in the column no. So in fact, you will constantly move 00:24:54.600 --> 00:24:58.880 formulas from one column to another and in the end you will not really find it. So I 00:24:58.880 --> 00:25:08.080 advise you if you want to use, for example, I don't know this column here, well before making 00:25:08.080 --> 00:25:12.080 a forecast, applying it, etc., is to duplicate it. So I click here on the 00:25:12.080 --> 00:25:21.000 duplicate button and at that moment I find myself here with the list of columns specific to location, 00:25:21.000 --> 00:25:28.280 including the one I just duplicated. So there I can reuse it. But if I reuse it, 00:25:28.280 --> 00:25:33.840 it will now be specific to the occasion, and even if I modify it, 00:25:33.840 --> 00:25:39.880 it will not modify the column or the formula in the column in which I 00:25:39.880 --> 00:25:43.160 initially created this formula. I don't know if I'm very clear, but basically the idea is 00:25:43.160 --> 00:25:49.320 when you want to reuse formulas between several columns, choose it. So for that, 00:25:49.320 --> 00:25:54.800 you have to remove the filter. Choose your formula and then immediately afterward, duplicate it. 00:25:56.360 --> 00:25:59.760 So, I told you at the beginning that Magic Formula is also available not 00:25:59.760 --> 00:26:03.280 only on the products table, but also on other tables. For example, 00:26:03.280 --> 00:26:10.080 here the variations table. So, I don't know if these products have variations. 00:26:10.080 --> 00:26:14.920 I'll show you a little example. Here, for example, this one, brown bear cushion. Ah, 00:26:14.920 --> 00:26:20.520 it has two variations, one in black and one in white. Uh so as I was saying, 00:26:20.520 --> 00:26:29.360 we can use Magic Formula in this type of column. No problem. Uh we enter the formula 00:26:29.360 --> 00:26:35.720 we want exactly like at the top. There you go, I just type a number. I fill in my value with 00:26:35.720 --> 00:26:40.160 my number. Well, however, what I'm going to show you is that it's a little more powerful than 00:26:40.160 --> 00:26:47.440 that. That is to say, the columns of the table here, which we call a child table, uh, can be 00:26:47.440 --> 00:26:53.960 filled using data taken from the top table, in the product table. Typically, 00:26:53.960 --> 00:26:58.960 we'll do the example on the reference column here, variation reference. Let's imagine that in 00:26:58.960 --> 00:27:07.160 my variation reference, I want to add here, sorry, I want to add at the end, uh, the name of the 00:27:07.160 --> 00:27:12.800 manufacturer. So, the manufacturer's name, well, you don't have it in the bottom table, it doesn't exist 00:27:12.800 --> 00:27:17.160 in this variation table. Here, we can't display it, it's not an available section. 00:27:17.160 --> 00:27:21.880 On the other hand, we can have it in the top table. I think I've already added it. Here 00:27:21.880 --> 00:27:28.120 , I have the manufacturer's name. Well, look, I'm going to the variation reference. Right click, 00:27:28.120 --> 00:27:35.200 I run Magic Formula in the entire column and I come here. I'm going to delete this formula and I 00:27:35.200 --> 00:27:40.840 'm going to tell it that I want to replace this reference with itself. So I click on 00:27:40.840 --> 00:27:46.040 current value. I've already told you about that. I concatenate with something else. Plus I'm going to add 00:27:46.040 --> 00:27:51.840 a space so quotation mark space quotation mark plus. And now I want to put the name of the manufacturer. So 00:27:51.840 --> 00:27:57.840 , I come here in column and look, I haven't shown but at the top you have all 00:27:57.840 --> 00:28:03.680 the columns up to here which are the columns of the current table in which I'm working and then 00:28:03.680 --> 00:28:09.440 below up to the product table separator, you have the columns of the top table. So there, 00:28:09.440 --> 00:28:17.600 I can go and find the manufacturer that I add here. And that's it. So, that's almost it. 00:28:17.600 --> 00:28:22.480 You'll see, it's not exactly well it's not going to give exactly the result that I want. There, 00:28:22.480 --> 00:28:29.200 if I preview, it adds me a, it doesn't add the name of the manufacturer but it adds me 00:28:29.200 --> 00:28:35.920 its ID. This is normal because the formula uses the column actually called Manufacturer ID. 00:28:35.920 --> 00:28:41.440 In fact, this column here, manufacturer, supplier, manufacturer, contains the ID. It displays the name because 00:28:41.440 --> 00:28:46.200 it's more practical, but in fact the column itself, the value, is the manufacturer's ID. 00:28:46.200 --> 00:28:52.840 So if I don't want to have the ID but the name, there's actually a little trick. I come 00:28:52.840 --> 00:29:00.560 here at the end and I add it. So I can do it either by hand by typing a period and then 00:29:00.560 --> 00:29:08.200 you type the displayed value like this. Or, and I think this will be more practical because 00:29:08.200 --> 00:29:13.200 you won't remember it. So I remove that. I come here to the 00:29:13.200 --> 00:29:22.280 channel management tools and I have to have it somewhere. Here it is at the very end. Displayed value. So I click 00:29:22.280 --> 00:29:27.000 here. So it adds it to the end. So I'm going to remove the space to make it cleaner and 00:29:27.000 --> 00:29:33.920 I preview it. And this time I have my manufacturer name displayed here. If I validate, 00:29:33.920 --> 00:29:38.960 it saves in the database. So what I wanted to show you is this, is that Magic Formula 00:29:38.960 --> 00:29:46.080 also allows you to fill data from the table of variations or the table of specific prices 00:29:46.080 --> 00:29:53.360 with data read from the columns of the product table. And so there are 00:29:53.360 --> 00:29:57.880 absolutely infinite possible uses. I'll show you one for references, 00:29:57.880 --> 00:30:01.360 but it can also work on numbers. So, we can fetch prices, weights, 00:30:01.360 --> 00:30:06.200 things like that at the top, quantities to fill data, attributes, 00:30:06.200 --> 00:30:13.280 whatever, in short, whatever you want in the table of variations or specific prices. 00:30:13.280 --> 00:30:16.200 So, to fully understand the full power of Magic Formula, I must now tell you 00:30:16.200 --> 00:30:20.360 about another concept in Merlin which is the concept of what we call 00:30:20.360 --> 00:30:25.200 calculated free columns. So a calculated free column, as its name suggests, is a free column, 00:30:25.200 --> 00:30:31.200 that is to say, one that is not linked to the database and is only filled via a calculation. 00:30:31.200 --> 00:30:35.040 So calculated columns, you can put five in each of the produced tables, 00:30:35.040 --> 00:30:41.040 this one in the variation tables. So the variation table is the one 00:30:41.040 --> 00:30:46.520 here in the image variation tab and also in the specific price table here. So 00:30:46.520 --> 00:30:51.360 these are free columns that you can fill using a formula created in Magic Formula. 00:30:52.200 --> 00:30:55.240 So to add calculated columns in the tables, well, as usual, 00:30:55.240 --> 00:31:01.880 we go to column, the column configurator. We go to the product, variation or 00:31:01.880 --> 00:31:05.760 specific price tab depending on where we want to add another column. For example, 00:31:05.760 --> 00:31:09.480 in the product table, I'm going to add a calculated column. So, the calculated column 00:31:09.480 --> 00:31:13.080 or calculated columns are present in this long list. Once again, 00:31:13.080 --> 00:31:17.120 I remind you that to make searching easier here, I advise you to click on this little 00:31:17.120 --> 00:31:22.080 button here which expands the complete list. And at that point, it's much easier 00:31:22.080 --> 00:31:28.760 here to locate the so-called calculated column group. I expand it and I have my five possible columns 00:31:28.760 --> 00:31:35.040 here. I'll take the second one for example, I'll put it here anywhere in your list and 00:31:35.040 --> 00:31:42.160 I validate. So it refreshes the display and I end up with my calculated column 00:31:42.160 --> 00:31:48.760 here automatically filled. So why is it automatically filled? Because in this 00:31:48.760 --> 00:31:55.440 column, I had already entered a formula. If, on the other hand, I use a calculated column that 00:31:55.440 --> 00:32:02.080 does not yet have a formula, we will do it right away, and well, we will have no content in it. 00:32:02.080 --> 00:32:06.480 So to see the formula that was entered in the calculated column, 00:32:06.480 --> 00:32:10.960 I right-click Magic Formula either in the selected cells, that is to say, 00:32:10.960 --> 00:32:17.560 the first row or here in the entire column. Select all the rows and I see here the 00:32:17.560 --> 00:32:23.800 formula that was used previously. If I want to create my own formula, for example, 00:32:23.800 --> 00:32:31.840 I would like to fill the calculated column with the weight, the value of the weight multiplied by 2. Well 00:32:31.840 --> 00:32:37.840 , as usual, I create a new formula. I click on more here and I type my formula. 00:32:37.840 --> 00:32:42.680 So I go to my weight. So the weight is a column. So I will look for my weight here in 00:32:42.680 --> 00:32:49.240 the list of columns. I'm going to multiply it by 2. I click here on the multiplication by 2 and 00:32:49.240 --> 00:32:55.720 I preview. So I have my result here. Please understand that this result is not stored 00:32:55.720 --> 00:32:59.240 in the database. Once again, this is not a calculate two section, it's not a 00:32:59.240 --> 00:33:05.480 database section. It's not a column linked to the database. So validating here has no effect and 00:33:05.480 --> 00:33:12.280 no impact on your site, eh. It's finally in any case it's purely informative. So 00:33:12.280 --> 00:33:18.280 here I have my calculation result which is indeed equal to my weight multiplied by 2. So what 00:33:18.280 --> 00:33:23.200 is the point of the calculated formulas? Well there are plenty of them I'll quote you even if it's just one. 00:33:23.200 --> 00:33:28.720 I'm often asked to add a column in Merlin displaying what is called the 00:33:28.720 --> 00:33:34.560 markup rate, which is different from the margin rate. Or some people ask me I would like to use 00:33:34.560 --> 00:33:39.240 I would like to see my margin rate on my selling price and not my margin rate on my 00:33:39.240 --> 00:33:43.640 purchase price. So this is a different formula from the margin rate that I've used so far. 00:33:43.640 --> 00:33:48.920 So all this type of need can perhaps be addressed using calculated formulas. Another 00:33:48.920 --> 00:33:53.440 example, some of you need to calculate and display the volume of 00:33:53.440 --> 00:33:58.960 your parts so that you can define the additional shipping costs. 00:33:58.960 --> 00:34:03.600 Well, with a calculated formula, a column calculated using a magic formula, 00:34:03.600 --> 00:34:07.280 you can calculate the volume, which is the multiplication, as you know, of width, height, 00:34:07.280 --> 00:34:12.880 and depth. Lots of possible uses. We'll see some examples in a moment. 00:34:15.440 --> 00:34:20.520 So the first concrete example now, I'm going to add a column 00:34:20.520 --> 00:34:27.520 of the type "calculate" so that I have as information the turnover achieved 00:34:27.520 --> 00:34:37.640 for each product. So I go back to my column configurator. 00:34:37.640 --> 00:34:43.200 So to calculate the turnover, what do I need? I need to know the 00:34:43.200 --> 00:34:48.960 purchase price, the sale price. So that gives me my margin, my profit if you prefer, and then I 00:34:48.960 --> 00:34:54.200 finally need to know my sales volume. So I'm going to create a new configuration, 00:34:54.200 --> 00:35:04.880 it might be simpler, which I'll call, uh, turnover. 00:35:04.880 --> 00:35:09.280 I validate. So that allows me to start from a slightly simpler configuration. 00:35:09.280 --> 00:35:15.120 So I already have my Tortax sales price, I already have my purchase price. Very good. Uh, I 00:35:15.120 --> 00:35:20.400 now need to know the quantity sold. So we're going to wrap everything up. 00:35:20.400 --> 00:35:24.960 The quantity sold, where are we going to find that? We're probably going to go to the stock 00:35:24.960 --> 00:35:34.040 or warehouse section here, and I have my quantity sold here. Perfect. So I'm going to put it there. 00:35:34.840 --> 00:35:40.560 And finally, I need a column called calculated to display the result of my calculation. So 00:35:40.560 --> 00:35:46.200 I go to calculate and I'm going to take a formula, or one of the columns 00:35:46.200 --> 00:35:56.400 . I'm going to take the first one, I'm going to put it there, and I validate. So it refreshes. 00:35:57.040 --> 00:36:04.240 So we're going to classify the display according to the quantity sold. There's no point in having calculations 00:36:04.240 --> 00:36:08.200 on the products that have been sold. So here we have some up here, 00:36:08.200 --> 00:36:16.280 8 items, 3, 2, etc. Um, and then we're going to enter our formula. So before that, 00:36:16.280 --> 00:36:20.640 I remind you that the quantity sold here is a function of a certain duration, 00:36:20.640 --> 00:36:25.120 eh. So quantity sold here in the last 30 days. If you don't know, the duration of 30 days 00:36:25.120 --> 00:36:29.840 here may change. You go to the left control panel, you go to 00:36:29.840 --> 00:36:38.240 calculation options and a little further down here you have the duration. So in the inventory management section, here you 00:36:38.240 --> 00:36:42.600 have the duration determining the quantity sold. So here, I'm on the last month, but it 00:36:42.600 --> 00:36:52.160 can be just the day, the week up to the full year, see all the possible dates. 00:36:52.160 --> 00:36:57.480 So our formula now. So I come to my calculated column, I right-click, 00:36:57.480 --> 00:37:06.200 Magic Edit in the entire column. Here, I have my previous formula. So, just an aside. 00:37:06.200 --> 00:37:11.400 When the formula appears in red, it means that it uses a column that no longer exists in 00:37:11.400 --> 00:37:15.640 the display. So there, I don't know which one it is. It might be this one, the physical quantity. I 00:37:15.640 --> 00:37:19.160 think it's not included in my display. So the current formula can't be 00:37:19.160 --> 00:37:25.520 used. So, however, it doesn't matter. I'm going to create a new one. I'll do more and 00:37:25.520 --> 00:37:29.400 here I have my field towards my new formula. So what do I want to calculate? I said 00:37:29.400 --> 00:37:36.080 the turnover. So what is the turnover? So I open a parenthesis, 00:37:36.080 --> 00:37:45.800 it's my selling price minus my purchase price. So the selling price excluding tax minus 00:37:45.800 --> 00:37:56.640 my purchase price. I close my parenthesis. So that's my profit. And I multiply that by the 00:37:56.640 --> 00:38:04.000 number of items sold. So the quantity sold in the last 30 days. Quantity sold in the last 30 days 00:38:04.000 --> 00:38:11.680 here. There, that's my turnover over this period. I validate. And there, 00:38:11.680 --> 00:38:16.040 I validate. So we're on a calculated type column. So once again, it's not saved 00:38:16.040 --> 00:38:20.840 in the database but it's information. So what's also important to understand is 00:38:20.840 --> 00:38:27.240 that if at some point, uh, I redisplay on refresh the display or I change category 00:38:27.240 --> 00:38:32.760 et cetera, I display other products, my calculation in my calculated formula is still there. eh, 00:38:32.760 --> 00:38:39.240 that's one of the advantages of calculated formulas, is that the formula is memorized in the column 00:38:39.240 --> 00:38:45.080 and always reapplied. This is not the case for other formulas. So if, for example, I put a 00:38:45.080 --> 00:38:51.200 formula on the reference column, uh, there is certainly one, you see, there is one 00:38:51.200 --> 00:38:55.560 here. In fact, this formula is not automatically reapplied each time I refresh 00:38:55.560 --> 00:39:00.720 the display because otherwise, I would systematically modify the data in the database each time 00:39:00.720 --> 00:39:05.840 I redisplay it. Let's imagine that I have a formula that makes purchase price equal purchase price + 2. 00:39:05.840 --> 00:39:11.360 Well, each time I redisplay the purchase price, I will add €2 to my purchase price. So in the end, 00:39:11.360 --> 00:39:14.920 I would have anything as data. So that's why, once again, the 00:39:14.920 --> 00:39:19.800 formulas on the database columns are not automatically reapplied. 00:39:19.800 --> 00:39:24.000 They are only applied when you privatize and validate. 00:39:24.000 --> 00:39:30.560 This is not the case for calculated columns. A calculated column has a formula that is 00:39:30.560 --> 00:39:41.480 here. And each time you refresh the display, this formula is automatically reapplied. 00:39:41.480 --> 00:39:47.800 So, a second fairly simple example. Let's imagine that you need to fill in your column and 00:39:47.800 --> 00:39:53.120 cotax from the purchase price or the sale price. So, still the same, 00:39:53.120 --> 00:39:58.200 I go to my column configuration, I'm going to add the section that I want to 00:39:58.200 --> 00:40:05.560 modify. So in this case, the purchase price. So, still the same technique. I wrap it, 00:40:05.560 --> 00:40:12.880 I go to the prices, I'm going to add my ecotax. I put it somewhere here. I validate. 00:40:15.800 --> 00:40:24.320 I have my ecotax, I'm going to enter my formula. So, for example, I would like my offset from my 00:40:24.320 --> 00:40:30.120 sale price excluding tax. Well, I right-click, Magic Edit in the entire column. I go 00:40:30.120 --> 00:40:34.600 here. Purchase price, rather sale price. I said 3% of the sale price. No, 00:40:34.600 --> 00:40:42.000 not of the purchase price. So, I make my sale price plus what? So, actually 3% of the price. 00:40:42.000 --> 00:40:47.000 purchase price. So here, I'm going to open a parenthesis or I'm multiplying by 1 + 3%. Well, there are 00:40:47.000 --> 00:40:50.560 different ways to do percentages. I don't know how you do it. 00:40:50.560 --> 00:41:02.760 I do it like this. I add it myself, so the sale price x 3 divided by 100. 00:41:02.760 --> 00:41:10.240 So that's my price plus, uh, the price 3%. Well, actually, I made a mistake in the calculation. 00:41:10.800 --> 00:41:14.600 I just want 3% of the purchase price. I'm telling you something stupid. I don't want the purchase price + 3 00:41:14.600 --> 00:41:20.560 %. So, I'm going to remove that and I'm going to remove the end parenthesis. Otherwise, it's not wrong. 00:41:20.560 --> 00:41:27.040 It's wrong. There you go, I apply it and immediately you have the result and I validate. 00:41:27.760 --> 00:41:33.000 So when I validate, save it in the database. So note the saving time here, 00:41:33.000 --> 00:41:37.880 it took about a second, a second and a half. It was for 67 lines. There you go, 00:41:37.880 --> 00:41:41.680 that's roughly the order of magnitude of Magic Formula's speed, eh. That's what I told you 00:41:41.680 --> 00:41:50.200 at the beginning. It's a little slower than the tool modified here by calculation, uh, older. But 00:41:50.200 --> 00:41:55.720 on the other hand, we have incomparable flexibility. I hope you'll have understood that by now. So 00:41:55.720 --> 00:42:04.800 there you go, I've filled in my ecotax equal to 3% of my selling price just with a very simple formula. 00:42:04.800 --> 00:42:09.680 So, I'm now talking to you about using Magic Formula's calculated columns in 00:42:09.680 --> 00:42:14.560 another context, but simply to mention that we can 00:42:14.560 --> 00:42:22.320 also do Magic Formula calculations in the import part. Here, I've displayed the contents 00:42:22.320 --> 00:42:29.160 of a file and if you look here, you have a column, it's the quantity column which has a 00:42:29.160 --> 00:42:35.080 small logo with a calculator. This means that on this quantity column, I've already set up 00:42:35.080 --> 00:42:41.080 a Magic Formula formula. So if I right-click here and select Magic Formula, 00:42:41.080 --> 00:42:45.640 you don't see it, but it's at the bottom. I see that I actually have a setup formula 00:42:45.640 --> 00:42:50.600 on this column. This formula is just an addition of 10. In fact, I take the 00:42:50.600 --> 00:42:56.440 current value + 10. What does that mean in the context of a file? It means that it's the 00:42:56.440 --> 00:43:02.240 value read in the file, so the quantity in stock in the file, and I add 10. Well, 00:43:02.240 --> 00:43:05.760 it's a somewhat unusual use, but simply to show you that we can do 00:43:05.760 --> 00:43:12.000 calculations on the data in the file before importing. So something that you were forced 00:43:12.000 --> 00:43:16.280 to do until now in Excel, you couldn't do in Merlin. Now it's 00:43:16.280 --> 00:43:22.120 possible. You can basically modify the data in the Excel file directly from Merlin in 00:43:22.120 --> 00:43:27.160 real time, dynamically. And this formula will be applied automatically. So even if 00:43:27.160 --> 00:43:33.080 tomorrow my file changes or I do automatic imports with the banificateur, when it 00:43:33.080 --> 00:43:37.600 fetches tomorrow's file of the stock update, if I know that I have to 00:43:37.600 --> 00:43:43.920 add 10 to my quantity because I don't know I have 10 in stock at home, well in fact I 00:43:43.920 --> 00:43:49.840 can do it with a formula. So that's a first possible use. So it's this magic 00:43:49.840 --> 00:43:54.680 edit, sorry, magic formula, not its name. So, it exists on all the columns of your 00:43:54.680 --> 00:44:02.200 file. Uh for example, you could very well set up formulas uh like uh here, 00:44:02.200 --> 00:44:07.880 I see low stock alert. Uh I can very well put a formula in it like if my quantity 00:44:07.880 --> 00:44:14.440 is less than uh I don't know 10 at that moment checked looks like low stock. So it's going to be a 00:44:14.440 --> 00:44:19.520 formula a little bit like that. There, I really do it in real time without having thought, 00:44:19.520 --> 00:44:23.600 I admit. I'm going to put a condition with the question mark. Here, I'm going to 00:44:23.600 --> 00:44:35.240 put in the condition if my quantity is less than 10 at that moment. So, 00:44:35.240 --> 00:44:38.920 for the example, I see that I have numbers that are much larger. I'm going to put I'm going 00:44:38.920 --> 00:44:48.040 to take the value of 2000. There you go. So if less than 2000, well I want to set 00:44:48.040 --> 00:44:56.280 a low stock alert. So I put 1 otherwise I put zero. There you go, I do that. I preview 00:44:56.280 --> 00:45:01.480 and look at this moment here I'm less than 2000 so it's checked. So I only applied it 00:45:01.480 --> 00:45:09.960 to one line. I do I'm going to select all my lines. I'm going to reapply my formula. 00:45:11.240 --> 00:45:17.520 I choose the image that says in the entire column. 00:45:17.520 --> 00:45:22.480 My formula is still there. I reapply it 00:45:22.480 --> 00:45:27.680 and this time, you see all the products whose quantity is lower than my 00:45:27.680 --> 00:45:32.520 ceiling of 2000 are checked at the alert level and the others are not. So, what is 00:45:32.520 --> 00:45:37.680 important to show you at this point, even if it remains an example, I will cancel, is that 00:45:37.680 --> 00:45:42.720 here what was grayed out earlier, I didn't tell you about it, this little button, this little 00:45:42.720 --> 00:45:54.000 automatic function. If I check it, it means that I put my formula in my, uh, my model. 00:45:54.840 --> 00:46:01.240 That is to say that if, uh, tomorrow I actually reapply, I reimport another 00:46:01.240 --> 00:46:04.920 file but with the same source, the same model, well, I will automatically have my formula which 00:46:04.920 --> 00:46:08.920 will be reapplied. Hey, that means it's a little like I don't know if you remember when 00:46:08.920 --> 00:46:14.720 you do a filter. For example, here, I can filter on this column. So to set 00:46:14.720 --> 00:46:18.840 up a filter limited to certain lines in my import, I could memorize it in the 00:46:18.840 --> 00:46:23.200 task settings and apply it automatically. There you go. So, this function is a bit 00:46:23.200 --> 00:46:32.480 like this automatic tool that we find here in Magic Formula. So 00:46:32.480 --> 00:46:42.120 if you want your formula to be applied automatically, you have to check automatic. 00:46:42.120 --> 00:46:51.120 So, in addition to being able to apply formulas like this to your files, so to 00:46:51.120 --> 00:46:56.760 your import tools, to columns in the file, uh Merlin goes a little further 00:46:56.760 --> 00:47:02.920 and allows you to also add free calculated formulas in this table here. These are, as 00:47:02.920 --> 00:47:06.040 I told you earlier, formulas that are not linked to the database 00:47:06.040 --> 00:47:11.040 . The difference here is that we will be able to add column shapes, so in 00:47:11.040 --> 00:47:19.000 the imported file. That is to say, currently, I am using a file that has certain columns, 00:47:19.000 --> 00:47:24.120 those that are here. Let's imagine that a column is missing from my file. For example, 00:47:24.120 --> 00:47:28.920 I don't have the references for the variations. At that point, I can build this column 00:47:28.920 --> 00:47:35.600 automatically by adding a so-called calculated free column. To do this, I click here on the 00:47:35.600 --> 00:47:41.200 new button there which is called calculated column which didn't exist until now. This adds a 00:47:41.200 --> 00:47:46.000 column here at the end of my model which always has a generic name like layer. And then there, 00:47:46.000 --> 00:47:50.520 it's the number of the column itself. And then this column, that's what's wonderful, 00:47:50.520 --> 00:47:57.000 you can map it to a heading. So I'm going to say for example that this will be I don't know, 00:47:57.000 --> 00:48:00.400 I could put the reference, I could put whatever I want. Uh well, 00:48:00.400 --> 00:48:05.440 we'll say reference. Although I've already used this one, I think. So we'll put 00:48:05.440 --> 00:48:10.680 minimum quantity for example. There you go. So I'm going to map that to minimum quantity. I click to 00:48:10.680 --> 00:48:20.320 use it and I come back here. So what's going to happen? So I'm going to redisplay. 00:48:20.320 --> 00:48:25.640 There you go, it's finished. So I end up with this time in my step 3 an 00:48:25.640 --> 00:48:32.160 additional minimum quantity column which contains zero for the moment because it is not linked it 00:48:32.160 --> 00:48:36.160 is not linked to anything in the file there is no minimum quantity column in the file and 00:48:36.160 --> 00:48:41.840 then there is not yet a formula but let's say I can put a formula so magic formula 00:48:41.840 --> 00:48:47.640 in the whole column. Let's imagine that I want as minimum quantity and well the quantity finally 00:48:47.640 --> 00:48:54.000 or 10% of the quantity or the quantity - 20 whatever. Basically, you put your formula. So, 00:48:54.000 --> 00:48:59.360 I take the quantity, I want to multiply it by go, I want I want 1% of the 00:48:59.360 --> 00:49:04.000 minimum quantity the sorry, I want the minimum quantity to be equal to 1% of the quantity. So 00:49:04.000 --> 00:49:14.240 quantity x 1%, I put it in automatic mode, I preview. It gives me my future quantity. I 00:49:14.240 --> 00:49:20.480 validate. Well there, I could have added a rounding because it was not clean in my 00:49:20.480 --> 00:49:25.440 formula. Well, however Merlin, he knows that it's a quantity so he rounds it up by himself. There you go. 00:49:25.440 --> 00:49:29.600 So there, I have my automatic formula. What does that mean? It means that if I redisplay the 00:49:29.600 --> 00:49:34.920 content, I can do it but you'll see, it will redisplay the quantity, it will 00:49:34.920 --> 00:49:40.120 recalculate on its own. Similarly, if you take a new file with the same mapping model, 00:49:40.120 --> 00:49:47.840 it will do the same calculation. So, well, while it recalculates, so what I was telling you, 00:49:47.840 --> 00:49:53.400 these columns calculated with Magic Formula in the import tool, it allows you to add 00:49:53.400 --> 00:49:59.840 columns that don't exist in your Excel file, to fill them with a formula, 00:49:59.840 --> 00:50:04.720 so for texts or for numbers, it works for both. And then to import the 00:50:04.720 --> 00:50:08.440 result of this calculation into one of the database sections. So, there are plenty of 00:50:08.440 --> 00:50:14.080 possible examples. You can use this type of formula, for example, to calculate your sales prices 00:50:14.080 --> 00:50:19.120 with a margin rate or a margin coefficient or a brand coefficient. So there, 00:50:19.120 --> 00:50:23.200 you can really use any margin type formula you want. So more 00:50:23.200 --> 00:50:28.800 simply, the margin rate on purchase price that Merlin offers you so far. So, 00:50:28.800 --> 00:50:33.960 another example, a little more sophisticated but quite typical, I come across quite often. Let's imagine 00:50:33.960 --> 00:50:39.960 that you have a variation file and instead of having a column with the sizes and a 00:50:39.960 --> 00:50:43.920 column with the colors, for example, you have like here, you see, a single column 00:50:43.920 --> 00:50:50.000 that contains the sizes, comma, colors, comma, another attribute, etc. Basically, 00:50:50.000 --> 00:50:54.000 a kind of mix of all the attributes in the same column. Well, until now, we were 00:50:54.000 --> 00:51:01.400 forced manually in Excel to go, uh, add two columns, to do catenation corners, 00:51:01.400 --> 00:51:05.480 string explosions, etc. to go and put everything before the first comma in one 00:51:05.480 --> 00:51:09.640 column, everything after the second, sorry, after the first comma in another column. 00:51:09.640 --> 00:51:14.000 Well, basically, that was work up front. Well now, you can do it directly in 00:51:14.000 --> 00:51:21.680 Merlin here with calculated columns and one of the magic formulas. So, how do we do 00:51:21.680 --> 00:51:27.360 that? Well, for example here, so this column that contains the S, the oranges. At the mapping level. 00:51:27.360 --> 00:51:32.920 Well, you map it to anything. Here, I mapped it, where do I find it? Uh, 00:51:32.920 --> 00:51:37.840 here, it's called the attribute list. I mapped it to location. Okay, choose something 00:51:37.840 --> 00:51:43.600 in the database that can receive this data that doesn't have any impact on you. 00:51:43.600 --> 00:51:48.120 It can be a characteristic, it can be something else. Once you've done that, 00:51:48.120 --> 00:51:54.160 what you do is click on calculated column. This button twice, it will adjust 00:51:54.160 --> 00:52:02.920 two columns like this. uh columns, layers, columns, you map them to attribute ID or not, 00:52:02.920 --> 00:52:09.040 as if you had columns containing your attributes, one to size, the other to color and 00:52:09.040 --> 00:52:16.760 you check them. What does that give you? When you display, you see your 00:52:16.760 --> 00:52:23.480 list of attributes here that are mapped to location. Okay, that's not very important. And then above all 00:52:23.480 --> 00:52:29.120 you have here your two other columns, size and color. So I've already done the work for 00:52:29.120 --> 00:52:32.480 one and I haven't done it for the other. So what does the work consist of? It consists 00:52:32.480 --> 00:52:37.640 of putting a formula in the column. So I'm going to show you what this formula is. 00:52:38.960 --> 00:52:43.480 It's a formula that uses extract string. So extract string is one of the functions 00:52:43.480 --> 00:52:48.880 you have here. So extract string extracts a string from a substring from another 00:52:48.880 --> 00:52:53.440 string using a separator. So basically, I put extract string location which is my 00:52:53.440 --> 00:52:58.480 column here. So I'm going to look for the value it has in it and I ask it to take the first 00:52:58.480 --> 00:53:02.680 string before the separator. Here in quotation marks, it's a comma. So I ask it to take 00:53:02.680 --> 00:53:08.440 the first part after before the first comma. So I checked that as automatic 00:53:08.440 --> 00:53:15.600 and it gives me exactly this result. And then for the second column, we're going to do it again 00:53:15.600 --> 00:53:23.320 together. Magic formula in the whole column. And there the formula is the same. It's going to be extracted 00:53:23.320 --> 00:53:27.560 string location but I put two in place of 1. So I the second string so the one 00:53:27.560 --> 00:53:34.000 after the first comma. Same comma separator, I have to check automatic. 00:53:34.000 --> 00:53:38.320 You see that when we check automatic, that we apply the formula, we have this calculator logo 00:53:38.320 --> 00:53:46.640 which applies and I preview and I validate. And when I validate, what happens? Look, 00:53:46.640 --> 00:53:50.680 uh, it even recognized the color values ​​among the color attributes 00:53:50.680 --> 00:53:54.840 that already exist. Here we find the color IDs. So exactly as if we had 00:53:54.840 --> 00:53:58.600 two columns, a size, a color in our file. Except that here, we exploded it. 00:53:59.400 --> 00:54:03.840 By dynamically exploding it into two separate columns. So we can really 00:54:03.840 --> 00:54:10.720 do everything with this tool. We can even, as I was telling you, do without having a key column for the 00:54:10.720 --> 00:54:15.280 variations. For example, it's often the case that we have a column that contains the product references 00:54:15.280 --> 00:54:19.320 and then we are missing the column containing the variation references or the opposite. 00:54:19.320 --> 00:54:26.000 Well, with the autocalculated columns plus Magic Formula, you can create the 00:54:26.000 --> 00:54:30.240 column you are missing and map it to one of the two synchronization keys. That's it, 00:54:30.240 --> 00:54:33.880 I'll stop there for this tool. The number of possibilities is almost infinite, 00:54:33.880 --> 00:54:38.480 so I'll let you discover them. Please give me feedback on whether you like the tool or not. If you have 00:54:38.480 --> 00:54:43.040 any comments or new ideas, I will be happy to implement them.