Cerca
  • marco ferrari

Grouping records using a List

I don't know why, but grouping records in in C/AL in the past had always been a difficult topic to understand for new programmers during my development courses.


In C/AL the best method was using temporary tables, as in the example below, in which I group the Sales Orders by Customer and then I make a loop over the customers I found to make something.

Please note that using the Customer table as temporary is not strictly necessary. The point is: declare temporary any table having a Code(20) as primary key. This because you just need to group your records by a field with this data type, so virtually any master table in Business Central could be used.


The List data type

Now in AL has been intrduced a new data type that can be used for the same purpose: the List.

This data type is simply a list of ordered objects that can be read by an index. As a first view one can think is like an Array, but the main difference is that the array has a lenght that must be declared before using it, while the List does not require to know in advance how many objects you'll have.

So in the example above, the array cannot be used to group records, simply beacuse you don't know in advance how many customers you'll find inside the loop.

Now I want to show you how to use a List data type to get the same result we obtained above using temporary tables.

As you can see, I declared a variable of type List made of Code(20) (the list must be made only by simple data type, like Integer, Code, Text etc).


Then the grouping is made in a similar way as before, by checking if the customer is in the list or not using the method Contains and then by adding it to the list using the Add method.


Finally I use a for loop for looping over records and make something with them. As you can see, the List is read by index and the method Get returns the customer number associated with that index.


Conclusions

I didn't make any test on performance difference between temporary tables and lists, but probably are similar, stating that are just in memory variables without access to the database, (temporary table is more complex than the list) but should be a good idea to make some tests over many records.


In any case lists dont' replace temporary tables, because with lists you just store a simple data type, while with the temporary tables you can store more information associated to a record, or take in memory the entire record instead of its primary key.


643 visualizzazioni

©2020 di Marco Ferrari