A cool feature of the Business Central platform are the virtual tables. Virtual tables can give you data on demand, in this video I showcase a couple of them.
In this video, Erik explores virtual tables in Business Central — tables that behave like regular tables but don’t actually exist in the SQL Server database. Their content is generated on demand at runtime, making them incredibly useful for a variety of scenarios. Erik walks through two key examples: the Integer virtual table and the Date virtual table, demonstrating how to use them in AL pages and how applying filters dynamically changes their content.
What Are Virtual Tables?
In Business Central, we all know the “real” tables — Customer, Vendor Ledger Entries, and so on. These are physical tables that exist in the SQL Server database. But there are also tables that are not so real. These are virtual tables.
Virtual tables don’t exist on SQL Server at all. If you install Business Central and browse through all the tables in the database, you won’t find them. Instead, their content is generated on demand at runtime. There’s no place in memory where these tables sit pre-populated with data — the records are created dynamically based on the filters you apply.
You can find these virtual tables by browsing the System symbols in Visual Studio Code. Using a tool like the AL Code Outline extension, you can double-click on the System symbol file and browse its contents to see the two-billion-numbered objects. Some of them, like the Company table, are real tables backed by the database. Others, like the Integer table and the Date table, are virtual.
The Integer Virtual Table
The Integer table is one of the simplest virtual tables. It has just one field — Number — which is also its primary key. The table contains every possible integer value, ranging from approximately negative one billion to positive one billion. That’s a lot of records, but remember, none of them actually exist anywhere until you query for them.
Basic Usage
Typically, when you use the Integer table, you apply a filter to narrow down the range of numbers you need. For example, if you need to process something 419 times, you’d set a filter from 1 to 419. The table then behaves exactly like any other filtered record set.
Using Integer as a Data Source for a Dictionary
Erik demonstrates a creative use case: using the Integer virtual table to display data from a Dictionary on a page. Since there’s no way in the UI to bind a Dictionary directly as a source table, you can use the Integer table as a proxy — each integer key maps to a dictionary entry.
Here’s the complete Integer Page example:
page 52300 "Integer Page"
{
PageType = List;
SourceTable = Integer;
SourceTableView = where(Number = filter('-7..40'));
layout
{
area(Content)
{
repeater(Rep)
{
field(Number; GetValue(Rec.Number))
{
Caption = 'This is the caption!';
ApplicationArea = all;
}
}
}
}
procedure GetValue(i: integer): Text
begin
if Dict.ContainsKey(i) then
exit(Dict.Get(i));
end;
trigger OnOpenPage()
begin
Dict.Add(-1, 'Peter');
Dict.Add(2, 'Jens');
Dict.Add(-3, 'Viggo');
Dict.Add(4, 'Karin');
Dict.Add(-5, 'Sofie');
Dict.Add(6, 'Sigurd');
Dict.Add(7, 'Owen');
Dict.Add(8, 'Jack');
Dict.Add(9, 'Hank');
Dict.Add(10, 'Will');
Dict.Add(11, 'Bill');
Dict.Add(12, 'Olena');
Dict.Add(13, 'Henrik');
Dict.Add(14, 'Jesper');
Dict.Add(15, 'Anders');
Dict.Add(16, 'Hougaard');
Dict.Add(17, 'Erik');
Dict.Add(18, 'Alice');
Dict.Add(19, 'Inger');
Dict.Add(20, 'Anton');
Dict.Add(21, 'Rikke');
Dict.Add(22, 'Maria');
Dict.Add(23, 'Carl');
Dict.Add(24, 'Mozart');
Dict.Add(25, 'Amadeus');
Dict.Add(26, 'Buzz');
Dict.Add(27, 'Louis');
Dict.Add(28, 'Lance');
Dict.Add(29, 'Wergner');
Dict.Add(30, 'Blackadder');
Dict.Add(31, 'Baldrick');
Dict.Add(32, 'Percy');
Dict.Add(33, 'Harry');
end;
var
Dict: Dictionary of [Integer, Text];
}Handling Missing Keys Gracefully
An important lesson Erik discovered during the demo: if you call Dictionary.Get() with a key that doesn’t exist, it returns an error. When this happens inside a repeater, the entire page breaks and shows nothing.
The solution is to wrap the dictionary lookup in a helper procedure that first checks whether the key exists:
procedure GetValue(i: integer): Text
begin
if Dict.ContainsKey(i) then
exit(Dict.Get(i));
end;With this approach, the filter range is set to -7..40, but only the integer keys that exist in the dictionary (including negative numbers like -1, -3, and -5) will display names. The remaining rows simply show blank values, leaving “holes” in the data — which is perfectly fine. This technique lets you display non-table data (like a Dictionary) as if it were a table.
The Date Virtual Table
The Date virtual table is another extremely useful virtual table, especially for reports and statistics. It contains several fields:
- Period Type — Date, Week, Month, Quarter, or Year
- Period Start — The start date of the period
- Period End — The end date of the period
- Number — A numeric identifier
- Name — A descriptive name for the period
Basic Date Table Page
page 52301 "Date Virtual"
{
PageType = List;
SourceTable = Date;
SourceTableView = where("Period Type" = const(Month));
layout
{
area(Content)
{
repeater(Rep)
{
field("Period Type"; "Period Type")
{
Caption = 'Period Type';
ApplicationArea = all;
}
field("Period Start"; Rec."Period Start")
{
Caption = 'Start';
ApplicationArea = all;
}
}
}
}
}How Filters Transform the Content
Without any filter on Period Type, the Date table returns individual dates — starting from January 3rd, Year 1, all the way to January 1st, Year 9999. That’s the full range of dates the system supports.
The magic happens when you apply filters on the Period Type field:
- Set
Period Type = Weekand the table suddenly contains weekly periods. The first record jumps to January 8th (the first week boundary) instead of January 3rd. - Set
Period Type = Monthand you get monthly periods. The first month returned is February 1st (since January 1st of Year 1 doesn’t qualify as a full month boundary). - You can also use Quarter or Year to get those respective period breakdowns.
This is incredibly powerful: just by applying different filters, the same virtual table produces completely different data sets. This makes it perfect for reports and statistics where you need to iterate over date ranges, weeks, months, quarters, or years.
Other Virtual Tables
There are other virtual tables in the system as well. For example, the File and Drive virtual tables used to be very useful on-premises — you could set a filter on the path, and the virtual table would magically populate with the file names found at that path. However, since there are no files and drives in the cloud, these tables are no longer relevant for Business Central SaaS deployments.
Summary
Virtual tables in Business Central are tables that aren’t really tables — they don’t exist in the database, but they behave exactly like tables in your AL code. Their content is generated at runtime based on the filters you apply. The two most commonly used virtual tables are:
- Integer — provides a sequence of integer numbers, perfect for iterating a specific number of times or using as a proxy data source for non-table data like Dictionaries.
- Date — provides date-based periods (days, weeks, months, quarters, years) that are invaluable for reports and statistical processing.
The key takeaway is that applying filters to virtual tables doesn’t just narrow down results — it can fundamentally change the content that’s generated. This makes virtual tables a versatile and powerful tool in any AL developer’s toolkit.
