Looking for records that don’t exist?

Sometimes, you have to go for records that don’t exist. But how to do that in the most effective way? In this video, I present two different patterns to check for records that don’t exist effectively. Check it out:

https://youtu.be/nNeQujT2DYo

In this video, Erik explores common patterns for checking whether records exist (or don’t exist) in AL for Business Central. He covers the most efficient ways to determine if records are present within a filtered set, explains why some approaches are costly, and demonstrates the “upsert” pattern — creating a record if it doesn’t exist or modifying it if it does.

Finding a Sales Order

Erik starts by working with the Sales Header table, which contains multiple document types (orders, invoices, quotes, etc.). To find a specific sales order, you first need to apply a filter on the document type:

SalesOrder.SetRange("Document Type", SalesOrder."Document Type"::Order);

He explains the difference between SetRange and SetFilter: use SetRange when you have a simple filter where the filter value is the same type as the field. SetFilter is for more complex, combined filter expressions.

FindLast and Implicit Error Handling

To retrieve the last sales order, you can write:

if SalesOrder.FindLast() then begin
    // Do something
end else
    Error('Could not find a sales order');

Alternatively, if you simply call SalesOrder.FindLast() without capturing the Boolean return value, AL will automatically throw an error if no record is found. This is a unique characteristic of AL compared to most other languages — the compiler is aware of the context in which a function is called. If a Boolean return value is not captured (either in an if statement or assigned to a variable), the runtime treats a false result as an error and displays a message like “Cannot find Sales Header with filters…”

This is effectively an implicit try/catch mechanism built into the language.

Checking If Records Exist: IsEmpty

A very common scenario is needing to check whether a sales order has any lines. There’s no built-in HasLines function, so you need to query the Sales Line table. First, apply filters to match the parent sales order:

SalesLine.SetRange("Document Type", SalesOrder."Document Type");
SalesLine.SetRange("Document No.", SalesOrder."No.");

Erik makes an important point about referencing the parent record’s field values rather than hard-coding enum values. If you hard-code "Document Type"::Order and later change the code to work with invoices, you’d have to remember to update the filter too. By referencing SalesOrder."Document Type", the filter automatically matches whatever document type you’re working with.

He also cautions about field naming: the "No." field on Sales Line is the item number, not the document number. You need to use "Document No." instead. A helpful tip: hover over your record variable in VS Code to see the primary key fields (PK1, PK2, PK3), which for Sales Line are Document Type, Document No., and Line No.

The Best Way: IsEmpty

Now, to check if there are any lines, the most efficient approach is:

if SalesLine.IsEmpty() then begin
    // No lines exist — do something
end;

IsEmpty is optimized in two important ways:

  • It only checks for the existence of a single record — it doesn’t matter if there’s one record or a million, it just needs to determine “at least one or none.”
  • It doesn’t retrieve any record data from the database, so there’s no overhead of transferring data through the stack, populating cache memory, or creating record objects.

Approaches Ranked by Efficiency

  1. IsEmpty — The cheapest option. Only checks existence, retrieves no data.
  2. FindFirst — Before IsEmpty existed, if not SalesLine.FindFirst() was the cheapest alternative. It only finds one record, but it still transfers that record’s data back to the client — slightly more expensive than IsEmpty.
  3. FindSet — Retrieves a page of records from the database. If there are thousands of records, it grabs the first page worth. Wasteful if you only need to know whether any records exist.
  4. Count = 0 — The most expensive option. Count must scan and tally every matching record in the database before returning a number. Never use this just to check for existence.

The Upsert Pattern: Get, Insert, or Modify

A common requirement is: if a record exists, update it; if it doesn’t, create it. In database terminology this is called an “upsert” (update + insert), though that term isn’t commonly used in the Business Central community.

The basic pattern looks like this:

if not Item.Get('123') then begin
    Item.Init();
    Item."No." := '123';
    Item.Insert(true);
end;
Item."Unit Price" := 233;
Item.Modify(true);

There are two paths through this code:

  • Item doesn’t exist: The Get returns false, so we enter the if block, initialize the record, set the primary key, and insert it. Then we set the unit price and call Modify.
  • Item already exists: The Get returns true (and populates the Item variable with the existing record), so we skip the if block. We set the unit price and call Modify.

This mirrors how Business Central pages normally work: when you type a primary key value into a card page, the record is immediately inserted with just the primary key. Every subsequent field change triggers a modify. A few pages use the DelayedInsert property, which postpones the insert until all fields are filled out.

Optimized Upsert for High-Volume Scenarios

In high-throughput scenarios with thousands of records, you may want to avoid the extra Modify call after an Insert. In that case, you can populate all fields before the insert and use an if/else structure. Erik’s final source code demonstrates this approach:

pageextension 50100 CustomerListExt extends "Customer List"
{
    trigger OnOpenPage();
    var
        Item: Record Item;
    begin
        if Not item.Get('123') then begin
            item.Init();
            Item."No." := '123';
            Filloutfields(Item);
            Item.Insert(true);
        end else begin
            Filloutfields(Item);
            Item.Modify(true);
        end;
    end;

    local procedure Filloutfields(var Item: Record Item)
    begin
        Item."Unit Price" := 233;
    end;
}

Notice how the field population logic is extracted into a separate procedure Filloutfields. This avoids duplicating code across both branches and keeps things maintainable — especially important when you have many fields to set rather than just one.

Summary

When working with records that may or may not exist in AL:

  • Use IsEmpty when you only need to know whether any records match your filters — it’s the most efficient check available.
  • Avoid Count = 0 for existence checks — it’s the most expensive approach.
  • Use the upsert pattern (if not Get then Insert else Modify) when you need to create or update a record based on whether it already exists.
  • Extract shared field assignment logic into a separate procedure to keep your code DRY and maintainable.
  • Remember AL’s unique behavior: when a function returns a Boolean and the return value isn’t captured, the runtime will automatically error on failure.
Picture of hougaard

hougaard