Learn how to use the right Find* Function

In this video, I talk about the different Rec.Find functions and how to s figure out the right one to use.

https://youtu.be/0S16-Hx38Vc

If you want to learn more about how to work with records, check out my video on validation.

In this video, Erik walks through one of the most fundamental topics in Business Central AL development: how to correctly use the various Find functions when working with records. He covers the history behind these functions, explains when to use each one, and highlights common mistakes that can silently hurt performance without breaking functionality.

The Origins of the Find Command

To understand why there are multiple Find functions in AL, it helps to know a bit of history. When Navision was first created, SQL Server wasn’t really a thing yet, so the developers built their own proprietary database — the native database. This database was cursor-based by design, meaning the system always knew the current record’s position in a table and could move forward or backward from there.

The original Find command was built for this cursor-based model and accepted a parameter to control how records were located:

  • '-' — Find the first record
  • '+' — Find the last record
  • '=' — Find an exact match on the primary key (equivalent to Get)
  • '>=' — Equal or next
  • '<=' — Equal or previous
  • '>' — Next record
  • '<' — Previous record

The Next function takes a steps parameter. If you don’t pass one, it defaults to 1. It returns the number of steps it was actually able to take — so if you ask for 2 steps forward but only one record exists, it returns 1. When it returns 0, there are no more records to move to.

The classic loop pattern looked like this:

if Customer.Find('-') then
    repeat
        // do something
    until Customer.Next() = 0;

The Problem with SQL Server

When SQL Server support was introduced (around Navision Financials 2.5/2.6), a problem emerged: SQL Server is slow with cursor-based access. The entire AL language had been designed around cursors for the native database, so in most real-time usage scenarios, SQL Server was actually slower than the native database.

To solve this, Microsoft introduced three new Find methods that were optimized for set-based SQL operations. This is where much of the confusion around Find functions begins.

FindFirst and FindLast

FindFirst and FindLast do exactly what their names suggest — they find the first or last record matching the current filters. However, they come with an important rule:

Do not use FindFirst or FindLast if you plan to loop through records afterward. These functions should only be used when you genuinely need just the first or last record and nothing else.

// Correct usage: you only need the first matching record
if Customer.FindFirst() then
    // do something with this single record

IsEmpty: The Hidden “Find”

There’s another function that doesn’t have “Find” in its name but serves a similar purpose. Consider this pattern:

if Customer.FindFirst() then
    DoSomethingImportant();

If you’re not actually using the record data and you just want to know whether any matching records exist, there’s a much better option:

Customer.SetFilter(Name, 'Erik*');
if not Customer.IsEmpty() then
    DoSomethingImportant();

IsEmpty is faster because it doesn’t need to actually retrieve the record — it only checks for existence. If you’re not going to use the record’s fields, always prefer IsEmpty over FindFirst.

FindSet: The Right Choice for Loops

FindSet is the function you should always use when you plan to loop through a set of records with repeat...until Next() = 0. It tells the server tier to load records in bulk, making iteration significantly faster than the old Find('-') approach.

FindSet takes two optional Boolean parameters:

  1. ForUpdate — Set to true if you intend to modify the records in the loop. This immediately acquires a lock on the table before reading begins.
  2. UpdateKey — Set to true (in addition to the first parameter being true) if you know you’ll be modifying key field values. Note that false, true doesn’t make sense — the second parameter is always an addition to the first.
// Read-only loop — no modifications
if Customer.FindSet(false, false) then
    repeat
        // read-only operations
    until Customer.Next() = 0;

// Loop with modifications to non-key fields
if Customer.FindSet(true, false) then
    repeat
        // modify non-key fields
        Customer.Modify();
    until Customer.Next() = 0;

// Loop with modifications to key fields
if Customer.FindSet(true, true) then
    repeat
        // modify key fields
        Customer.Modify();
    until Customer.Next() = 0;

Erik mentions that he usually writes FindSet(false, false) explicitly in code, even though both parameters default to false, because it makes the intent immediately visible — you can see at a glance that the loop is read-only.

One Limitation of FindSet

FindSet always iterates from the first record to the last. If you need to loop backwards (from last to first), you still need to use the old Find('+') with Next(-1):

if Customer.Find('+') then
    repeat
        // processing in reverse order
    until Customer.Next(-1) = 0;

Optimizing with Filters and Keys

Before calling FindSet, you should set up your filters and, when beneficial, set the current key to help SQL Server execute the query efficiently. The Customer table, for example, has 16 keys defined. Keys serve two purposes:

  • Sorting — If there’s already a key matching your desired sort order, SQL Server doesn’t have to do extra work.
  • Filtering — The back end will try to provide SQL query hints based on keys. If you’re filtering on a field that has a key, setting that key explicitly will use fewer server resources and improve performance.

Here’s the complete recommended pattern from Erik’s source code:

codeunit 50134 "Find The Right Way"
{
    procedure Test()
    var
        Customer: Record Customer;
    begin
        Customer.SetFilter(Name, 'Erik*');
        Customer.SetCurrentKey(Name);
        if Customer.FindSet() then
            repeat
                // process records
            until Customer.Next() = 0;
    end;
}

The Dangerous Part: Nothing Breaks

Perhaps the most important takeaway from this video is that using the wrong Find function won’t break your code. All of these approaches will work correctly:

// Works, but slow (cursor-based)
if Customer.Find('-') then
    repeat
    until Customer.Next() = 0;

// Works, but not optimized for looping
if Customer.FindFirst() then
    repeat
    until Customer.Next() = 0;

// Best approach for looping
if Customer.FindSet() then
    repeat
    until Customer.Next() = 0;

The danger is that you won’t see an error or a warning — your code will simply run slower than it needs to. This makes it a silent performance issue that can accumulate across an entire application.

Quick Reference Summary

  • FindSet() — Use when looping through records. Always the right choice for repeat...until Next() = 0 patterns.
  • FindFirst() / FindLast() — Use only when you need a single record (the first or last). Do not loop after these.
  • IsEmpty() — Use when you only need to check whether records exist, without needing the actual data.
  • Find('+') / Find('-') — Legacy syntax. Still needed for reverse iteration (Find('+') with Next(-1)), but otherwise prefer the newer functions.
  • Get() — Use when you know the exact primary key value. Equivalent to Find('=').
  • SetCurrentKey() — Set before FindSet when you’re filtering or sorting on fields that have a defined key, to help SQL Server optimize the query.

Understanding these distinctions is one of those foundational skills in AL development that separates performant code from sluggish code. The functions all work, but choosing the right one ensures your extensions scale well as data volumes grow.

Picture of hougaard

hougaard