How to Group Data in Business Central Using Dictionary Data Types Instead of Temporary/Buffer Tables

In Microsoft Dynamics, a Dictionary data type is an AL data type that represents a collection of keys and values. This value can be a simple data type (such as text, code, integer, or decimal) or a more complex data type (like XMLDocument, JsonArray, among others). It could even contain another Dictionary or List.

When it comes to grouping your data, a Dictionary can be just as useful as a buffer table. In fact, the Dictionary can be even more flexible than the buffer table because sometimes you must create a table just to be used as a buffer for a report and that table will be consumed from your license.

In other cases, if you don’t want to create a new table, you must adapt another existing table to be used as a temporary buffer. With Dictionaries, you don’t need a predefined structure to be created. You can structure your Dictionary exactly as you need it. I’m not saying that Dictionaries are going to replace the use of temporary or buffer tables, but they certainly offer Business Central users a new way to group data.

How to Group Data in Business Central Using Dictionary Data Types

Let’s create a new version of the Customer/Item Sales report (which shows the quantities and sales amounts per customer/item) using a Dictionary data type.

This is a report that contains a basic example about how temporary tables can be used as buffer to group data.

The data items for this report are as follows:

Value entry is linked to the customer through the Source No. (plus more filters on source type, expected cost, and adjustment fields).

The temporary table Value Entry Buffer is filled with the amounts grouped by Item, using the Item No. as the key.

Finally, the integer table controls the looping over the Value entry buffer table to show the results.

We can replace the Value Entry Buffer table:

We do this by replacing it with a Dictionary using a code[20] key, which will be the Item No., and a List of decimals that represent the amounts to summarize.

This is a preview of the report:

Original Code Using a Temporary Table

First, the original code using a temporary table named Value Entry Buffer

As usual, the temporary table is deleted and reset at the beginning of the process.

As you can see, Item No. is used as the key to group and Entry No. is being inserted just because it’s the real primary key of the table. The other five fields are merely being summarized.

It’s summarizing the related item charges and Adjustments entries.

At the end, it’s inserting or modifying the temporary table depending on the existence of the Item No. entry.

The Integer dataItem controls the looping over the Value Entry Buffer.

New Code Using a Dictionary Data Type

Now the new Report using Dictionary data type:

I defined the Dictionary variable and 2 List to contain the amounts to summarize and the keys added to the Dictionary (Item Nos). We’ll see later what the use for them is.

You can see the old code commented out if you want to compare it.

This will be the equivalent to the DeleteAll function on the temporary table. It’s very important to clear the Dictionary as we normally do with other variables.

First, we summarize the data in the List. Then we add the List to the Dictionary.

The ContainsKey method is used to verify if the Item No. was already added.

The Get method gets the data saved in the list for a specific index. In this case, we have five values.

The Set method is used to update the existing amounts in the List. I initialized the list so I can use just the method Set instead of Add and Set.

Finally, you Set or Add the List to the Dictionary, depending on the existence of the key.

In order to loop over the Dictionary data, I get the list of the keys in the variable ItemKeysList using the method Keys.

The range on Integer is set using the method Count for that list of keys. You can use count on Dictionary as well.

As you can see, it is just a matter of getting every List of amounts related with each Item No. in the List of keys, and the List of keys is accessed by index using the Number of the integer.

Please note that the variable ItemSalesAmount List is global, and it can be used directly as datasource.

About the Author:

María Helena Ocampo is a senior developer at Advanced Business Systems with more than ten years of experience ranging from Microsoft Dynamics NAV version 5.0 all the way to Dynamics 365 Business Central.

Microsoft Gold Partner Logo_edited.png
  • LinkedIn Social Icon
  • Twitter Social Icon
  • Facebook Social Icon
  • YouTube Social  Icon

Austin  |  Dallas  |  Houston  |  New Orleans  |  Nashville