Sorting is a classic computer science challenge, and sometimes in AL you also need to sort. In this video, I take a look at that challenge from an AL perspective. Check it out:
In this video, Erik explores a fundamental computer science topic — sorting data — within the context of AL and Business Central. He walks through why common AL data structures like Lists and Dictionaries don’t support sorting out of the box, and demonstrates the tried-and-true approach of using temporary tables with defined keys to sort data efficiently.
The Sorting Challenge in AL
Sorting algorithms are a classic computer science discipline, and in many programming languages you can simply call a .Sort() method on a collection. In AL, however, the built-in data structures don’t offer this luxury. Erik explores what options are available and what their limitations are before arriving at the recommended approach.
Lists: Insertion Order Only
The first thing Erik tries is using a List of [Date]. He populates it with three dates in a non-sorted order:
var
L: List of [Date];
D: Date;
begin
L.Add(20230101D);
L.Add(20220101D);
L.Add(20240101D);
foreach D in L do
Message('%1', D);
end;The output comes back in exactly the order the items were added: 2023, 2022, 2024. There’s no L.OrderBy() or sorting method available on a List. You can call L.Reverse(), but that simply flips the insertion order — it doesn’t sort. Without implementing your own sorting algorithm (like a quicksort), Lists are not useful for sorting purposes.
Dictionaries: No Guaranteed Order
Next, Erik tries a Dictionary of [Date, Boolean] and populates it with the same three dates. He retrieves the keys into a List using .Keys():
var
L: Dictionary of [Date, Boolean];
Keys: List of [Date];
D: Date;
begin
L.Add(20230101D, true);
L.Add(20220101D, true);
L.Add(20240101D, true);
L.Keys(Keys);
foreach D in Keys do
Message('%1', D);
end;The results again came back in insertion order (2023, 2022, 2024), but Erik points out an important caveat: the order of keys in a Dictionary is not guaranteed. Behind the scenes, a Dictionary is implemented with a hash-based structure that ensures uniqueness but makes no promises about ordering. It happened to preserve insertion order in this case, but you should never rely on that behavior.
The Solution: Temporary Tables with Keys
Since Lists and Dictionaries can’t sort, the answer in AL is to leverage the database engine — or more precisely, temporary record variables with defined keys. This is a pattern that experienced NAV/Business Central developers have used for years.
Microsoft’s Built-in Sorting Table
Erik first shows that Microsoft themselves use this pattern. There’s a table called “Sorting Table” in the base application that has integer, decimal, and code fields with separate keys defined for each. It’s used for generating charts and graphs, but the concept is the same: define a table with keys for each field you might want to sort by, then use SetCurrentKey to choose your sort order.
Here’s how Erik demonstrates it with the built-in sorting table:
var
Sorting: Record "Sorting Table" temporary;
begin
Sorting.Init();
Sorting.Integer := 1;
Sorting.Code := 'Erik';
Sorting.Insert();
Sorting.Init();
Sorting.Integer := 2;
Sorting.Code := 'Annika';
Sorting.Insert();
Sorting.Init();
Sorting.Integer := 3;
Sorting.Code := 'Benny';
Sorting.Insert();
Sorting.Init();
Sorting.Integer := 4;
Sorting.Code := 'Peter';
Sorting.Insert();
// Without SetCurrentKey - returns in primary key order: 1, 2, 3, 4
// (Erik, Annika, Benny, Peter)
Sorting.SetCurrentKey(Code);
// Now sorted alphabetically by Code: Annika, Benny, Erik, Peter
if Sorting.FindSet() then
repeat
Message('%1 %2', Sorting.Code, Sorting.Integer);
until Sorting.Next() = 0;
end;Without SetCurrentKey, records come back in primary key order (integer 1 through 4). After calling SetCurrentKey(Code), the records are returned sorted alphabetically: Annika, Benny, Erik, Peter.
Creating Your Own Sorting Table
If you need to sort by a data type that isn’t covered by the built-in sorting table (like dates), you can create your own temporary table. Erik demonstrates this by defining a simple table with integer, date, and datetime fields:
table 50100 "My Sorting Table"
{
TableType = Temporary;
fields
{
field(1; Integer; Integer) { }
field(2; Date; Date) { }
field(3; DateTime; DateTime) { }
}
keys
{
key(PrimaryKey; Integer) { }
key(DateKey; Date) { }
key(DateTimeKey; DateTime) { }
}
}Setting TableType = Temporary ensures this table never gets persisted to the database. Erik notes that the Clustered key setting is a SQL-only concept and has no effect on temporary tables.
With this table defined, sorting dates becomes straightforward:
var
Sorting: Record "My Sorting Table" temporary;
begin
Sorting.Init();
Sorting.Integer := 1;
Sorting.Date := 20230101D;
Sorting.Insert();
Sorting.Init();
Sorting.Integer := 2;
Sorting.Date := 20220101D;
Sorting.Insert();
Sorting.Init();
Sorting.Integer := 3;
Sorting.Date := 20210101D;
Sorting.Insert();
Sorting.Init();
Sorting.Integer := 4;
Sorting.Date := 20200101D;
Sorting.Insert();
Sorting.SetCurrentKey(Date);
if Sorting.FindSet() then
repeat
Message('%1', Sorting.Date);
until Sorting.Next() = 0;
// Output: 2020, 2021, 2022, 2023
end;The dates come back in ascending order: 2020, 2021, 2022, 2023 — perfectly sorted despite being inserted in reverse order.
Does SetCurrentKey Require a Defined Key?
Erik tests an interesting edge case: what happens if you remove the date key definition from the table and still call SetCurrentKey(Date)? The result is that the sorting still works. However, Erik cautions that having a defined key is important for performance, especially with larger datasets. The key allows the in-memory table to create an index for that data, making sort operations more efficient.
Using Existing Tables for Sorting
You don’t always need to create a new table. The source code includes an example of working directly with the Customer table to demonstrate how SetCurrentKey changes record traversal order:
pageextension 50100 CustomerListExt extends "Customer List"
{
trigger OnOpenPage()
var
C: Record Customer;
begin
c."No." := '30000';
c.find('=');
message('%1', C.Name);
c.next();
message('%1', C.Name);
c.SetCurrentKey(Name);
c."No." := '30000';
c.find('=');
message('%1', C.Name);
c.next();
message('%1', C.Name);
end;
}This example finds customer 30000, shows its name, moves to the next record (which is the next by “No.” — the primary key), then switches the current key to Name. After finding the same customer again, calling Next() now moves to the next customer alphabetically by name rather than by number.
If you already have data in a table and your sorting criteria can be expressed as a key (with optional filters), you may not need a temporary table at all. But when you need to sort a custom subset of data that can’t be defined by simple filters, building up a temporary table with the appropriate records is the way to go.
Summary
Sorting data in AL requires a different approach than what developers from C# or other backgrounds might expect:
- Lists maintain insertion order only — no built-in sorting capability.
- Dictionaries have no guaranteed key order and cannot be sorted.
- Temporary tables with defined keys are the standard approach for sorting in AL. Use
SetCurrentKeyto choose your sort order. - You can use Microsoft’s built-in
"Sorting Table"for integers, decimals, and codes, or create your own temporary table with keys for any data types you need. SetCurrentKeycan work even without a formally defined key, but defining keys improves performance on larger datasets.- When possible, leverage existing tables with appropriate keys and filters rather than creating new ones.
