Finding duplicates with AL

In this video, I demonstrate a simple way to catch duplicate records during input. Check it out:

https://youtu.be/d3hvbxiBpXo

In this video, Erik walks through a common question from channel subscribers: how to detect and warn about duplicate records in Business Central using AL. He demonstrates the approach by building a table extension on the Customer table that checks for existing customers with the same name (and optionally address) whenever a user enters or modifies those fields.

The Problem: Duplicate Customer Records

It’s easy for users to accidentally create duplicate customer records. For example, a user might create a new customer called “Alpine Ski House” without realizing that one already exists in the system. The goal is to alert the user during data entry that a potential duplicate has been found, so they can decide whether to proceed or delete the new record.

Approach: Table Extension with Field Modification

Erik chooses to implement the duplicate detection at the table level using a table extension. While you could also handle this on a page, putting it on the table ensures the logic runs regardless of which page is used to create or modify customers.

A key design decision is using the modify keyword inside the fields section of the table extension. This doesn’t add new fields — instead, it lets you attach new triggers to existing fields. In this case, Erik adds an OnBeforeValidate trigger to both the Name and Address fields.

Why OnBeforeValidate?

Erik opts for OnBeforeValidate rather than OnAfterValidate. The reasoning is that if a duplicate is found, there’s no need to run any of the standard validation logic (such as CRM-related processing) before the user has been warned. This way, the check happens first.

The GuiAllowed Guard

Since this logic lives on the table, it could be triggered from non-UI contexts — API calls, background sessions, job queues, and so on. In those scenarios, showing a message to a user doesn’t make sense and could cause errors. That’s why the very first check is:

if GuiAllowed() then begin
    // duplicate detection logic
end;

This is a best practice whenever you place user-interactive business logic on a table: always wrap it in GuiAllowed() to ensure it only runs when there’s actually a user interface present.

Building the Duplicate Check

The core technique involves creating a second record variable of the same type and setting filters to find potential matches:

  1. Filter on the field(s) to match — using SetFilter with the @ prefix for case-insensitive matching and wildcards (*) for partial matching.
  2. Exclude the current record — filter on the primary key ("No.") to exclude the record you’re currently editing. This avoids the record matching itself as a duplicate.
  3. Set an appropriate key — use SetCurrentKey to help the database perform the lookup efficiently.
  4. Check if a match exists — use FindFirst() to see if any matching record is found.

Why Exclude the Current Record?

Erik explains an important subtlety: depending on the page’s behavior (delayed insert vs. immediate insert), the current record may or may not already exist in the database when the validation trigger fires. Rather than trying to detect this and using Count > 1, it’s cleaner and more efficient to simply exclude the current record’s primary key from the filter. This way, regardless of insert timing, you only find other records that match.

IsEmpty vs. FindFirst

Erik briefly discusses the choice between IsEmpty and FindFirst(). Using IsEmpty is more efficient when you only need to know whether a match exists, since it doesn’t retrieve any data. However, in this case we need the duplicate’s name and number for the warning message, so FindFirst() is the right call — it retrieves the first matching record so we can reference its fields.

The Final Source Code

Here is the complete table extension that checks for duplicates on both the Name and Address fields:

tableextension 50103 "My Customer" extends Customer
{
    fields
    {
        modify(Name)
        {
            trigger OnBeforeValidate()
            var
                CustomerDup: Record Customer;
            begin
                if GuiAllowed() then begin
                    CustomerDup.Setfilter(Name, '@*' + Rec.Name + '*');
                    CustomerDup.Setfilter(Address, '@*' + Rec.Address + '*');
                    CustomerDup.Setfilter("No.", '<>%1', Rec."No.");
                    CustomerDup.SetCurrentKey(Name);
                    if CustomerDup.FindFirst() then begin
                        message('A customer with the name %1 (No = %2) already exists, consider deleting this one!',
                            CustomerDup.Name, CustomerDup."No.")
                    end;
                end;
            end;
        }
        modify(Address)
        {
            trigger OnBeforeValidate()
            var
                CustomerDup: Record Customer;
            begin
                if GuiAllowed() then begin
                    CustomerDup.Setfilter(Name, '@*' + Rec.Name + '*');
                    CustomerDup.Setfilter(Address, '@*' + Rec.Address + '*');
                    CustomerDup.Setfilter("No.", '<>%1', Rec."No.");
                    CustomerDup.SetCurrentKey(Name);
                    if CustomerDup.FindFirst() then begin
                        message('A customer with the name %1 (No = %2) already exists, consider deleting this one!',
                            CustomerDup.Name, CustomerDup."No.")
                    end;
                end;
            end;
        }
    }
}

Key Details in the Filter Syntax

  • @ — makes the filter case-insensitive, so “alpine ski house” matches “Alpine Ski House”.
  • * — wildcard for partial matching, allowing substring matches.
  • <>%1 — the “not equal to” filter that excludes the current record’s number.

Erik notes a quirk with filter syntax: the @ sign must come before the * wildcard. Older versions of NAV were more forgiving about the order, but in Business Central the @ needs to be first for the filter to work correctly.

Performance Considerations

For databases with large numbers of customers, Erik recommends:

  • Using SetCurrentKey(Name) so the database can use an appropriate index for the lookup rather than scanning the entire table.
  • Considering whether IsEmpty is sufficient before resorting to FindFirst() — though in this case both are needed.
  • Avoiding Count when you only need to know if at least one record exists, since it forces the database to count all matching rows.

Extending the Pattern

The duplicate check on both Name and Address demonstrates how you can combine multiple field filters to reduce false positives. You could extend this further by:

  • Only running the combined check when both fields are filled in, regardless of the order they were entered.
  • Adding additional fields to the check (city, phone number, VAT registration number, etc.).
  • Presenting a more sophisticated UI — for example, showing a list of potential duplicates and letting the user choose which one to keep.
  • Using fuzzy search techniques for “almost matching” records, which Erik covers in a separate video on the topic.

Summary

The core pattern for finding duplicates in AL is straightforward: create a second record variable of the same type, set filters on the fields you want to match (using @ for case-insensitivity and * for wildcards), exclude the current record’s primary key, and check if any results exist. Wrap any user-facing logic in GuiAllowed() when working at the table level, use SetCurrentKey for performance, and consider combining multiple fields to make the duplicate detection more accurate. From this foundation, you can build increasingly sophisticated duplicate management features tailored to your specific business requirements.

Picture of hougaard

hougaard