In the last episode, we were talking about the posting, the process of copying the documents data to registers. In order to make some important calculations in advance, so we could quickly get the ready to go results whenever we need. We are already using the inventory accumulation register to keep track of the number of products we have left. Let's now take a closer look at the accumulation register insights. [MUSIC] Let's create a new accumulation register to play with. There are two types of accumulation registers in the platform. Registers of the balance type keep track of how many things are left. The registers of the turnover type track the number of things spanned over a period of time. Let's start with the balance register. [MUSIC] We use balance accumulation registers when we need a quick answer to the question, how many things are left? [MUSIC] The most important things to understand about the accumulation register are dimensions and resources. They define what calculation there register does. So let's play with them and see how they work. I'm going to start with zero dimensions, although it's really hard to match on what tasks because these kind of register might be useful for. Anyway, I am creating no dimensions and the only resource quantity. And the register goes to the other subsystem. I don't have any real life task in mind, just want to show you around. So as a matter of example, I'm going to post purchase documents data into this register. Now I need a forum to show you the data flow from the documents to the register and inside of it. I don't need you to repeat after me. So let me skip a number of steps here. And here is our form. Let me show you what it looks like. The form has three dynamic lists. The upper one shows the products list of all posted purchase documents. Next tool is down show the two tables so the accumulation register, the main table and the balance table. The main table of the register gets the exact copy of all records posted. Except the reason our company and product attributes in the register, so these two just get lost at this point. Next, is these quantities get copied to the balance table and summed up here. The sum is broken down by the registers dimensions, which we don't have. So we end up with a single sum, the total amount of whole products bought. This number mixes up all pringles and old batteries and there is no way of separating one from another looking at the balance table alone. If you're familiar with a SQL, you can think of the balance table as the product of these query performed on the main table. Now I'm going to add the product dimension to the register. And copy the product attribute from the purchase document to the register here in the post and event handler. I repost the toll, the purchase documents behind the scene. And this is how the picture has now changed, all the records posted to get carpet to the registers main table as always. But now the product attribute is here. So this info doesn't get lost, but the company still does. Then these three main table records go to the balance table, this time the sum is broken down by the product. So these batteries get summed up into these balance record. And this single pringles line goes here. This is how our hypothetical SQL query has changed. It gets to the product held in the SELECT clause and GROUP BY clause where the product in it, okay? Let's add another dimension, company. Now I need to copy these attribute value to the register. The company attribute leaves on the documents level rather than in the products list. So this is where I'm taking it from and here we go. Now all the records posted our carpet to the main table with all the fields including product and company. Then they are copied to the balance table and this time no data gets lost in the process. Because the sum is broken down by both product and company. The SQL for these transformation looks like this, we have the company field added to the SELECT and GROUP BY clauses. So this is what dimensions do. They define how the balance records are broken down or, in other words, how detailed they are. You can think of dimensions as off binoculars focused ring. You can zoom all the way out by creating a register with no dimensions, and all you see is one big balance number without any details. You zoom in to one dimension, and the single balance value gets split into many. You zoom further in and these smaller balances get split more and more. But how do we decide how many and what dimensions we should have? Let's look once again and how we check if the products are available during the sales document posting. This is the query that reads the negative balances from the inventory register after the current document records are written. It takes all products that are listed in the sales document, then goes to the balance table and searches for the balances record for each product in the document. The search will be the fastest if there is only one balanced record for each product. This way, the query won't need to calculate anything. It will just read the number it needs directly. So this specific query needs the balance is broken down by products. And this is exactly what we implemented by adding the product as the only dimension to the inventory register. So we create a register to speed up some specific task on. And we look at the task requirements to decide what structure is the best. The ultimate goal is to provide the task with the final result that needs no further calculations. These were the register dimensions. What about the resources, what are they and how we use them. [MUSIC] Let me add another resource called price to our test register. I'm also going to delete all the dimensions again to make it more clear what exactly happens. And I'm fixing this posting procedure and adding the price assignment. Okay, lets see. Nothing's really changed, except for these price column which is now in the main table. And which gets summed up to the balance table the same way the quantity does. And this is how our hypothetical SQL query looks like now. So the resources are what gets summed up in the balance table, broken down by dimensions. It means that the resource can be only in numeric. Okay, this was the balance accumulation register. Let's now change its type to turnovers and see what happens. [MUSIC] We use turnover accumulation registers to get the quick answer to the question, how many things are spent over a period of time. [MUSIC] So the single balance record got split into two separate turnover records. Why? Because the turnover table calculates not the current balances, but the turnovers over periods of time we define. In this case, I said it to show the monthly turnovers. So these two records from June got summed up into these turnover records. And this single July records went here. Basically, you can think of the turnover register as of the balance register with defined dimension period at it. Except we can select the periodicity we need. Let me show you where it's done. The forum we just saw lives here in common forms. And this is the turnover table. It's a dynamic list that takes its data from this so called virtual table. But look at the real and virtual tables more closely in Module five. In a nutshell, it's table that can change its output depending on its parameters. But this is where we can say we want monthly turnovers. If we run the query builder and open this virtual table parameter list, we'll see other possible options right here. But what do we used the turnover registers for? Here is an example for you. Say I want to adopt a loyalty card program in my little but prosperous fictionary shop. For instance, I want to give one point for every dollar spent in my shop. Every customer who earned 100 points within a month gets a 5% discount till the end of this month. When the month is over, all points burn and next month starts from zero points. I just implemented this functionality behind the scene. And we'll show you the implementation in a moment. Now let's look at how we works. First things first, here I have a new catalog called Loyalty cards. And when my very loyal customer Mr. John Doe came to the shop, he quickly agreed to register one. So. So this is his card in the app. Mr. Doe goes out of town for the weekend. So he's buying a case of Pringle's, his favorite snack. Note that now I have a new field, a Loyalty card, in the sales document form. So I'm filling it out with John's card. The case of Pringle's goes to the list, as well as the bottle of Coke to wash it all down. Note that up here I have another new field, Amount paid, that sums up all prices from the list. Now I'm posting it. And here is this transaction in mine, your turnover register called Loyalty cards turnovers. Have a safe trip, Mr. Doe! Sorry, what? More coke. Of course, here you go, Mr. Doe. And congratulations, you just got a 5% discount on this purchase, and you owe me only 15.20 instead of 16. So this is how it works. Now let me show you how it's implemented. This is our internal register with the only dimension, loyalty cards catalog reference, and the only resource, AmountPaid. This structure means that we break turnovers down by loyalty cards. Note that there is no periodicity setting anywhere in the register, although turnovers are periodical by nature. We set it up as we wish when we read from register, meaning we can set it differently in different places. Next, a few changes I made live here in the sales document form. I added Amount paid and Loyalty card attributes to the document and the form. Whenever the price field and the products list gets changed or the product gets deleted, I recalculate the Amount paid attribute summing up all the prices. I do the same when the form gets open to calculate this amount for the old documents that were saved earlier when there were no such attributes yet. And I do the same in the Objects modules before writing to make sure this amount is always calculated, regardless of where the document was saved from. But the most interesting part has always lives here in the post an event handler. Nothing's really changed up here except, I'm not exiting the procedure in the case of the backdated posting anymore. And here is my LoyaltyCard program implementation part. I clear these document records from the turnover register. Then I check if the LoyaltyCard was used for these transaction. And then I read the Turnovers for these LoyaltyCard for the current Month. This is how I specified the LoyaltyCard, and this is the beginning and the end of these documents' month. I'm pretty sure there might be the only one record in the result in the record set. So I read the first record and add the current document's amount to it. If the monthly amount already spent plus the current document's amount is greater than 100, then I discount the current document's amount by 5% and congratulate the customer. And finally, I write the recalculated amount to the turnover register like this. As easy as that. [MUSIC]
