When we open a document form, all its data gets read at once. This includes all the tabular sections with all the lines they contain regardless of how many there are. This happens because the Platform deems a document an atomic, indivisible entity and prevents it from being read or written partially. This what makes this tabular section a static list. All its data sits in my local computer memory, and there is no way on Earth anyone but me can sneak a new line in here. But this is not the case when it comes to the documents lists like this or this one. Because they are dynamic lists. So, this is what we are here to talk about today.
So, what’s so dynamic about this list here? Two things. First of all, while I’m looking at it, somebody else can change its content. So, when I refresh the list, it isn’t the same. Second of all, as the app works new docs are created, and it’s just a matter of time until there are hundreds and thousands of them.
There is no way (and no point, actually) to have them all in the memory, so the Platform keeps there only what is visible to a user right now, while the rest of the data just sits in the database waiting for its turn. When a user scrolls the table the dynamic list downloads new data from the database and at the same time deletes from the memory the data that gets out of the screen. So the data is read on demand, in other words dynamically.
OK. Let’s take a look at the dynamic list in Designer and see what we can do with it. Let’s take a quick look at the Sales documents list form, for example. Here is its List attribute. It has the Dynamic List type, and its settings are pretty straightforward. The most important setting is this MainTable guy which we use to tell the dynamic list where to read the data from. We can select any table of any metadata object like this. Very simple, very effective, works perfectly as long as we don’t need anything complex, like a reading of the data from several tables or sorting list differently, etc. But the beauty of the dynamic list is that we can customize it to solve all of these problems and then some more. Let me show you.
So, here is a task for us. Every time I select a product for a Sales document I can open this Choice form to look up the Product I need. What I’m missing here is the amount of the Product I have currently left. So, let’s fix it using the dynamic list settings, shall we? I’m going to the Products catalog and creating an explicit Choice form like this. I don’t need the Code but could use the UoM and Brand fields. And here is my form. This List attribute reads its data from the Products catalog which does not know the amount left, so there is no QuantityLeft field or anything like this here.
So, this is what I do. I’m ticking this CustomQuery checkbox, going to the List setup and now I can have any query I want. So, I’m running the Query Builder, adding the InventoryBalance table to the Tables list and adding QuantityBalance field to the Fields list. This is where the amount left lives. Now I’m going to Joins page to tell the query what condition it should use looking up the record in the Inventory but turns out the Query Builder already figured it out. It looked at all the fields in these two tables and found a pair of fields having the same type. So now, the query will look for the Inventory balance record belonging to each specific product in the list.
The only thing I need to change here is these All flags. I need all the Products even those not in the Inventory balance, and I don’t need the Inventory balance records not belonging to any of my Products. And here is our query. Now I have a QuantityBalance attribute in the dynamic list, so I’m just dropping it to the form, and that’s it.OK, let’s see. I’m opening this Sales document, clicking "Show all" and now I have the inventory balance for every Product in the list. Great.