Temporary tables are a powerful tool in any AL developer’s toolbox. In this video, I take a look at how temporary tables works and the different ways temporary tables show up in the application.
In this video, Erik explores temporary tables in AL for Business Central — a technology that has been around for decades but has received some improvements over time. He walks through three different ways to work with temporary records, demonstrates common patterns and pitfalls, and highlights critical gotchas that can catch developers off guard.
What Are Temporary Tables?
Temporary tables (or temporary records) in Business Central are in-memory data structures that behave like regular database tables but never touch the actual SQL database. The data is stored in the service tier’s memory, and you can perform nearly all standard record operations on them — Init, Insert, Delete, FindFirst, Count, and so on.
There is one critical difference: temporary records are not part of transactions. They don’t participate in rollbacks, commits, or any transactional behavior. They exist entirely outside of that scope.
Three Ways to Use Temporary Records
Erik identifies three main approaches to working with temporary records in AL:
- Classic temporary variables — declaring a record variable with the
temporarymodifier - Temporary table type — defining a table object with
TableType = Temporary - Source table temporary on pages — marking a page’s source table as temporary using
SourceTableTemporary = true
Method 1: Classic Temporary Variables
The most traditional approach is to declare a record variable with the temporary keyword. This creates an in-memory instance of the table that you can populate and manipulate without affecting the real database.
Here’s the action from the Customer List extension that demonstrates this pattern:
pageextension 50100 CustomerListExt extends "Customer List"
{
actions
{
addfirst(processing)
{
Action(Test)
{
Caption = 'Temporary';
ApplicationArea = all;
trigger OnAction()
var
TempCust: Record Customer temporary;
begin
TempCust.Init();
TempCust."No." := '10000';
TempCust.Insert();
TempCust.Init();
TempCust."No." := '20000';
TempCust.Insert();
TempCust.Init();
TempCust."No." := '30000';
TempCust.Insert();
TempCust.Init();
TempCust."No." := '40000';
TempCust.Insert();
page.run(50110, TempCust);
end;
}
}
}
}Even though this variable references the Customer table, calling TempCust.DeleteAll() would not delete any actual customers. It only affects the in-memory temporary data. You can verify this by checking TempCust.Count — it reflects only what’s been inserted into the temporary instance.
A common use case for this pattern is when you iterate through a set of records, apply complex logic that can’t be expressed as a simple filter, and store the qualifying records in a temporary table for later processing.
Multiple Temporary Variables Are Now Independent
Erik points out an important improvement in modern Business Central. If you declare two separate temporary variables of the same table type, they each get their own independent storage:
var
TempCust: Record Customer temporary;
TempCust2: Record Customer temporary;In prehistoric Business Central (NAV) days, these two variables would have shared the same underlying temporary storage, meaning inserting the same primary key in both would cause an error. Today, they are completely independent, which is the behavior you’d naturally expect.
The IsTemporary Function
You can check whether any record variable is temporary by calling IsTemporary:
if TempCust.IsTemporary then
// handle temporary-specific logicThis is particularly useful inside trigger code when a table might be used both as a regular record and a temporary record in different contexts.
Method 2: TableType = Temporary
The second approach is to define an entire table object as permanently temporary by setting TableType = Temporary. This means the table will never be created in SQL Server — it can only ever exist in memory.
table 50100 "New Table"
{
Caption = 'New Table';
DataClassification = ToBeClassified;
TableType = Temporary;
fields
{
field(1; "Funky Field"; Code[13])
{
Caption = 'Funky Field';
DataClassification = ToBeClassified;
}
field(2; "Nice Field"; Text[100])
{
Caption = 'Nice Field';
DataClassification = ToBeClassified;
}
}
keys
{
key(PK; "Funky Field")
{
Clustered = true;
}
}
}When you declare a variable of this table type, it has the exact same functionality as a classic temporary variable. You don’t even need to add the temporary keyword to the variable declaration — it’s inherently temporary. Adding temporary wouldn’t hurt, but it’s not necessary since the table type enforces it for all instances.
This approach is useful when you’re creating a purpose-built buffer table that should never persist data. It makes the intent explicit at the table level rather than relying on every consumer to remember to mark the variable as temporary.
Method 3: SourceTableTemporary on Pages
The third approach is to mark a page’s source table as temporary using the SourceTableTemporary = true property. This is also applicable to reports and XMLports.
page 50110 "Temp Customer"
{
Caption = 'Temp Customer';
PageType = List;
SourceTable = Customer;
SourceTableTemporary = true;
layout
{
area(content)
{
repeater(General)
{
field(Address; Rec.Address)
{
ToolTip = 'Specifies the value of the Address field';
ApplicationArea = All;
}
field("Address 2"; Rec."Address 2")
{
ToolTip = 'Specifies the value of the Address 2 field';
ApplicationArea = All;
}
field("Net Change (LCY)"; Rec."Net Change (LCY)")
{
ToolTip = 'Specifies the value of the Net Change (LCY) field';
ApplicationArea = All;
}
field("No."; Rec."No.")
{
ToolTip = 'Specifies the value of the No. field';
ApplicationArea = All;
}
field(Name; Rec.Name)
{
ToolTip = 'Specifies the value of the Name field';
ApplicationArea = All;
}
}
}
}
}When this page opens, Rec is a brand new temporary variable — it starts empty. You need to populate it somehow, typically by passing a pre-loaded temporary record set when running the page via Page.Run:
page.run(50110, TempCust);This pattern lets you build up a temporary data set in code and then display it to the user through a standard page interface.
Critical Pitfalls to Watch For
Trigger Code and Side Effects
One of the most dangerous aspects of temporary records is trigger code. When you call Insert(true) or Delete(true) on a temporary record, the true parameter causes the table’s trigger code to execute. That trigger code typically operates on real database tables.
For example, if you delete a temporary customer record with TempCust.Delete(true), the OnDelete trigger on the Customer table fires. That trigger deletes associated records in Comment Lines, Customer Bank Accounts, Ship-to Addresses, and many other tables — all of which are real database records. The fact that your customer record is temporary does not make any of those downstream operations temporary.
Erik’s rule of thumb: when working with temporary versions of real tables, never use trigger code (don’t pass true to Insert/Delete) unless you know with absolute certainty that the trigger only operates within the record itself. Even then, be cautious — there may be event subscribers that introduce unexpected side effects.
FlowFields and Calculated Values
Another subtle pitfall is FlowFields. Even on a temporary record, FlowFields like Net Change (LCY) still calculate their values from the real database. The CalcFields operation queries Detailed Customer Ledger Entries in SQL, regardless of whether the customer record is temporary. There is no way to plug a temporary version of the related table into the FlowField calculation.
This means you can end up with a page displaying a mix of temporary data (the records you inserted) and real data (the calculated field values), which can be very confusing and misleading.
Separate Temporary Instances Don’t Connect
If you have temporary Sales Headers and temporary Sales Lines in your code, the standard trigger code that deletes lines when you delete a header won’t find your temporary lines. The trigger creates its own new variable instances that reference empty temporary storage — not the same temporary storage you populated. Each temporary variable gets its own independent in-memory dataset.
When to Use Temporary Tables vs. Lists and Dictionaries
Erik notes that he has increasingly started using List and Dictionary data types (covered in other videos on the channel) for simpler scenarios. If you just need to track a list of 17 customer numbers you found during processing but don’t actually need the full record, a List or Dictionary is a slimmer, more dedicated data type with less potential for confusion.
However, when you need the full record structure with filtering, sorting, and field-level access, temporary tables remain the right tool for the job.
Summary
Temporary tables are a powerful and long-standing feature in Business Central development. The three approaches — temporary variables, TableType = Temporary, and SourceTableTemporary on pages — each serve different use cases. The key takeaways are:
- Temporary records live entirely in memory and are not part of database transactions.
- Multiple temporary variables of the same table now get independent storage (an improvement over older NAV behavior).
- Never pass
trueto Insert/Delete on temporary records of real tables unless you fully understand the trigger implications. - FlowFields on temporary records still calculate from the real database — be aware of the data mixing.
- Consider
ListandDictionarytypes for simpler scenarios where you don’t need full record functionality. - Use
IsTemporaryto check a record’s temporary status when writing code that might handle both scenarios.
