Solve a Venn Diagram with AL Code

In the past week, I did a live coding session at DynamicsCon 2025, including an interesting challenge with finding the common dataset between two independent datasets. Check out the video:

https://youtu.be/9BhDcuOoUvs

In this video, Erik walks through a practical AL coding challenge that came up during his live coding session at DynamicsCon 2025 in Chicago: how to find customers that exist in the intersection of two data sets — essentially solving a Venn diagram problem in Business Central. He demonstrates two different approaches, discusses their trade-offs, and shares some hard-won debugging lessons from coding live on stage.

Background: The Certification App from DynamicsCon 2025

At DynamicsCon 2025, Erik did a live coding session where he built a customer certification app in about an hour, producing roughly 300 lines of AL code. The app allows you to assign certifications to customers — think of scenarios where customers need to be certified to purchase certain items like explosives, medical supplies, or other regulated goods. When a sales order is created, the system checks whether the customer holds the required certification for the item being ordered.

The interesting question that came up at the end of the session was: what if you want to find customers who hold more than one certification? How do you search for customers that are in both sets — the intersection of a Venn diagram?

The Data Structure

The underlying table is straightforward. The Customer Certification table has three fields:

  • Customer Number — links to the customer
  • Certification — the certification code
  • Issued At — when the certification was issued

You can easily filter this table to see all customers with a specific certification. But finding the overlap — customers who hold both Certification A and Certification B — requires a different approach.

The search page itself is built on the Customer table, with two input fields at the top for the two certifications you want to intersect. The results use Business Central’s mark/filter mechanism to display only the matching customers.

Method 1: Direct Primary Key Lookup

The first approach is the simplest and most direct. Since the Customer Certification table has a composite primary key of Customer Number and Certification, you can loop through all customers in the first set and directly check if each one also exists in the second set using a Get call.

procedure SearchOne()
var
    CustCert: Record "Customer Certification";
    CustCert2: Record "Customer Certification";
begin
    ClearMarks();
    Rec.Reset();

    CustCert.SetRange(Certification, Cert1);
    if CustCert.FindSet() then
        repeat
            if CustCert2.Get(CustCert."Customer No.", Cert2) then begin
                Rec.Get(CustCert."Customer No.");
                Rec.Mark(true);
            end;
        until CustCert.Next() = 0;

    Rec.MarkedOnly(true);
end;

The logic is straightforward:

  1. Clear any existing marks and filters
  2. Loop through all customers who have the first certification
  3. For each customer found, check if a record also exists with the second certification
  4. If it does, get the customer record and mark it
  5. Set the page to show marked records only

The Rookie Mistake

During the session, Erik initially forgot a critical step — he marked Rec without first calling Rec.Get() to load the actual customer record. Without that call, Rec is empty, and marking it does nothing useful. As Erik put it: “Did you yell at the screen at this point? You should. I deserved a good yelling here.”

This is a great reminder that Rec.Mark(true) only works when Rec is positioned on the correct record. Always make sure you Get the record before marking it.

Method 2: Hash Table (Dictionary) Approach

The second method is what Erik actually built during the live conference session. It uses a Dictionary as a hash table to store the first set in memory, then checks membership while looping through the second set.

procedure SearchTwo()
var
    CustCert: Record "Customer Certification";
    HashTable: Dictionary of [Code[20], Byte];
begin
    ClearMarks();
    Rec.Reset();

    // Looping the first segment
    CustCert.SetRange(Certification, Cert1);
    if CustCert.FindSet() then
        repeat
            HashTable.Add(CustCert."Customer No.", 0);
        until CustCert.Next() = 0;

    // Looping the second segment
    CustCert.SetRange(Certification, Cert2);
    if CustCert.FindSet() then
        repeat
            if HashTable.ContainsKey(CustCert."Customer No.") then begin
                Rec.Get(CustCert."Customer No.");
                Rec.Mark(true);
            end;
        until CustCert.Next() = 0;

    Rec.MarkedOnly(true);
end;

Why a Dictionary?

Business Central’s AL language doesn’t have a native hash table type, but a Dictionary serves the same purpose. The key insight is that we only care about whether a key exists — we don’t care about the value at all. That’s why the value type is Byte, the smallest possible data type, to minimize memory usage. You could also use a Boolean, but a Byte is arguably the most memory-efficient choice.

How It Works

  1. Build the lookup: Loop through all customers in the first certification set and add each customer number to the dictionary
  2. Check the second set: Loop through all customers in the second certification set. For each one, check if the customer number exists in the dictionary using ContainsKey
  3. Mark matches: If a customer from the second set is found in the dictionary, they’re in both sets — get the record and mark it

When to Use Which Method

For this particular data structure, Method 1 is clearly superior — the direct primary key lookup is simple, clean, and efficient. However, Method 2 shines in scenarios where:

  • The process of determining set membership is complex and can’t be reduced to a simple Get call
  • You’re working with data that doesn’t have a convenient composite primary key
  • The first set requires complex filtering or processing to identify
  • You need to intersect more than two sets (the dictionary approach extends naturally)

Optimization Tips

Erik shared a few optimization considerations for both approaches:

  • Start with the smallest set. If one certification has 10 customers and another has 2,000, always loop through the smaller set first. This minimizes both the number of lookups and (in Method 2) the size of the hash table.
  • Extending to three or more sets. Both methods can be extended to handle additional certifications. With the dictionary approach, you could build up the hash table from the first set, filter it against the second, then filter again against the third.
  • Consider temporary tables as an alternative to dictionaries if you need to store more complex data during the comparison, though dictionaries will typically use less memory for simple key-existence checks.

Bonus: Don’t Forget Table Relations

While testing, Erik noticed that the search page didn’t offer lookups for the certification fields. This turned out to be a missing TableRelation property on the Customer Certification table — a classic oversight when building prototype code live on stage. As Erik wisely noted: “No prototype code written live on stage should go into production.”

Conclusion

Finding the intersection of two data sets is a common requirement in Business Central customizations, and AL gives you multiple ways to tackle it. The direct primary key lookup (Method 1) is ideal when the data structure supports it, while the dictionary-based hash table approach (Method 2) provides a flexible, memory-efficient solution for more complex scenarios. Both methods leverage Business Central’s mark/filter pattern to present clean results to the user. Whichever approach you choose, remember two things: always Get your record before marking it, and start with the smallest data set for optimal performance.

Picture of hougaard

hougaard