Filter Groups can give you the fabled “or-filter”

The filter syntax in AL is an eternal cause for discussion when non-AL developers first meet Business Central, and the discussions can become heated arguments. A classic attack on AL is that we cannot do an “or filter” between two fields. But that is not really true. Check out the video:

https://youtu.be/5wIfJJQ4GTI

In this video, Erik demonstrates a lesser-known but powerful feature in Business Central AL development: filter groups. Specifically, he shows how to achieve the often-requested “OR filter” between fields — something many assume is impossible in Business Central’s filtering system. If you’ve ever been told that SQL is superior because it can do WHERE field1 = x OR field2 = y, this technique is your answer.

The Problem: AND Filters Are the Default

In Business Central, when you apply filters to multiple fields on a record, they are combined with an AND condition. For example, if you filter the Customer List where Name contains “ski” AND Contact contains “Bond,” you get zero results — because no single customer satisfies both conditions simultaneously.

Erik demonstrates this directly in the Business Central UI: filtering the Customer List for a name containing “ski” and a contact containing “Bond” returns nothing, even though there’s a customer matching each individual criterion.

Understanding Filter Groups

Filter groups are a mechanism in Business Central that organizes filters into numbered groups. Each group can contain its own set of filters, and the way these groups interact with each other depends on the group number. According to the documentation:

“A filter group can contain a filter for a record that has been set earlier with the SetFilter method or the SetRange method. The total filter applied is the combination of all the filters set in all the filter groups.”

Microsoft reserves filter groups 0 through 7 for specific purposes:

  • Group 0 (Standard) — Default filter group. When you call SetFilter without changing the group, this is where your filter goes.
  • Group 1 — Global filters, form-level filtering.
  • Group 2 — Used for SourceTableView / SetTableView on pages.
  • Group 3 — Used for DataItemLink in reports.
  • Group 4 — Used for subpage links (the filter you see linking sales lines to a sales order header, for example).
  • Group 5 — Used for fact box state management.
  • Group 6 — Security filters. Erik mentions using this in his Advanced Cloud Security app — filters placed here cannot be removed by end users.
  • Group 7 — Additional system use.
  • Group 25+ — Available for custom use by developers.

You can see these filter groups in action throughout Business Central. For example, when you navigate from a Customer Card to Customer Ledger Entries, there’s a system filter on Customer Number — that filter lives in a specific filter group. On a Sales Order page, the lines subpage has a subform link filter tying lines to the header’s document number, residing in its own filter group.

The Magic Filter Group: -1 (Cross-Column)

The key to achieving an OR filter is filter group -1, known as the cross-column filter group. Filters placed in this group are combined with OR logic instead of the usual AND logic. This is the same mechanism that powered the cross-column search field in Business Central (prior to the new text search introduced in BC 2025/wave 1).

When you typed into the old search field at the top of a list page, Business Central would set filters on every searchable column using filter group -1, effectively creating: WHERE Name LIKE '%x%' OR Address LIKE '%x%' OR Contact LIKE '%x%'...

Implementing the OR Filter in AL Code

Here’s the complete example Erik walks through. The code applies an OR filter between the Name and Contact fields, then combines that with a standard AND filter on Address:

namespace DefaultPublisher.FilterGroup;

using Microsoft.Sales.Customer;

pageextension 50100 CustomerListExt extends "Customer List"
{
    trigger OnOpenPage();
    var
        Grp: Integer;
    begin
        Grp := Rec.FilterGroup();
        Rec.FilterGroup(-1);
        Rec.Setfilter(Name, '@*ski*');
        Rec.SetFilter(Contact, '@*bond*');
        Rec.FilterGroup(Grp);
        Rec.SetFilter(Address, '@*queen*');
    end;
}

Let’s break down what’s happening step by step:

  1. Grp := Rec.FilterGroup(); — Save the current filter group so we can return to it later. This is a good practice, especially when working with shared record references, to avoid inadvertently affecting other filters.
  2. Rec.FilterGroup(-1); — Switch to the magic cross-column filter group.
  3. Rec.SetFilter(Name, '@*ski*'); — Set an OR filter on the Name field (case-insensitive, contains “ski”).
  4. Rec.SetFilter(Contact, '@*bond*'); — Set an OR filter on the Contact field (case-insensitive, contains “bond”). Because both filters are in group -1, they are combined with OR logic.
  5. Rec.FilterGroup(Grp); — Switch back to the original filter group (typically group 0).
  6. Rec.SetFilter(Address, '@*queen*'); — Set a standard AND filter on the Address field. This filter is in group 0 and will be AND-combined with the result of the OR filters from group -1.

The Result

The effective filter logic becomes:

(Name LIKE '*ski*' OR Contact LIKE '*bond*') AND Address LIKE '*queen*'

Without the Address filter, the query returns two customers — one matching “ski” in the name and one matching “bond” in the contact. When the Address filter for “queen” is added as a standard filter (group 0), it narrows the result to just one customer, because only one of the two OR-matched customers has “queen” in their address.

Performance Considerations

Erik notes that while you can add many filters in filter group -1, performance can degrade. This is exactly what happened with the old cross-column search in BC 24 and earlier — it would apply a filter on every single searchable column in filter group -1 with whatever the user typed, which could lead to slow queries on large datasets. Keep this in mind when using this technique: it’s powerful, but use it judiciously.

Key Takeaways

  • Business Central can do OR filters between fields — it’s just not immediately obvious how.
  • Filter group -1 (cross-column) combines its filters with OR logic instead of AND.
  • You can combine OR filters (in group -1) with standard AND filters (in group 0) for sophisticated filtering logic.
  • Always save and restore the current filter group when switching groups to avoid unintended side effects.
  • Be mindful of performance when adding many filters to group -1.
  • Filter groups 0–7 are reserved by Microsoft for specific system purposes — use group 25+ for your own custom filter groups if needed.

This technique is a great addition to your AL development toolkit, especially when you need to implement search-like functionality or complex filtering logic that goes beyond simple AND conditions.

Picture of hougaard

hougaard