@ Filtering in Business Central, how hard can it be?

Here’s a little Christmas trivia challenge (that I do not have the solution for), @ filtering, how hard can it be? Check the video to find out:

https://youtu.be/b8GJowyJ5Is

In this video, Erik explores a deceptively tricky problem in Business Central: how do you filter on a value that starts with the @ symbol? What seems like it should be straightforward turns into a surprisingly stubborn challenge that even code-level workarounds can’t easily solve.

The Setup: Four Test Customers

Erik creates four customers on the Customer List, each with a slightly different name:

  • test (lowercase)
  • Test (normal case)
  • TEST (uppercase)
  • @test (with an @ sign in front)

The goal is simple: filter the list to show only the customer whose name is @test. Sounds easy, right?

The Problem: @ Is a Filter Operator

In Business Central’s filtering system, the @ symbol has special meaning — it makes a filter case-insensitive. So when Erik types @test into the Name filter, Business Central interprets it as “find all records where the name matches ‘test’ regardless of case.” The result? Three records come back: test, Test, and TEST. The actual @test record is excluded.

Erik then tries several approaches:

  • *test — Returns all four records, since the wildcard matches everything before “test”
  • ?test — Returns the @test record, since ? matches any single character, but it would also match other five-character names ending in “test” (like &test)
  • “Filter to this value” — Even using the built-in “filter to this value” option from the UI when the @test row is selected still returns the three case-insensitive matches

Trying Quotes and Documentation

A common suggestion for filtering on special characters is to wrap the value in quotes. Business Central’s documentation specifically mentions that if field values contain certain symbols — ampersand (&), parentheses (()), equals (=), and pipe (|) — you should place the filter expression in single quotes.

However, the @ symbol is not on this list. Erik tries wrapping the filter in single quotes anyway, but it still returns the same three case-insensitive results. The @ continues to be interpreted as a filter operator regardless of quoting.

Interestingly, the ampersand (&) is on the documented list. Erik demonstrates that filtering on &test without quotes produces an invalid filter, but wrapping it in single quotes works correctly. The @ symbol doesn’t get the same treatment.

Trying from AL Code: SetFilter vs. SetRange

Erik then moves to the code level, reasoning that surely AL code would provide more control. He creates a page extension on the Customer List to experiment:

namespace DefaultPublisher.AtFiltering;

using Microsoft.Sales.Customer;

pageextension 50100 CustomerListExt extends "Customer List"
{
    trigger OnOpenPage();
    begin
        Rec.SetRange(Name, 'test|Test|TEST');
    end;
}

The key insight here is the difference between SetFilter and SetRange:

  • SetFilter treats the value as a filter expression, so @test would be interpreted as a case-insensitive filter — same problem as the UI.
  • SetRange is supposed to take a value, not a filter expression. Erik reasons that passing @test to SetRange should treat it as a literal value.

But surprisingly, even SetRange interprets the @ as a filter operator. The case-insensitive behavior persists. When Erik inspects the resulting filter, he can see that Business Central wraps the value in single quotes — but the @ is still processed as a special character.

To further prove the point, when he passes a pipe-separated value like 'test|Test|TEST' to SetRange, it gets properly escaped with single quotes and treated as a literal string (returning nothing, since no customer has that exact name). But the @ symbol doesn’t receive the same escaping treatment.

The Unsolved Mystery

Erik is left genuinely stumped. Every approach he tries — UI filtering, quoting, SetFilter, SetRange — fails to treat the @ symbol as a literal character. The core issue is that @ appears to be deeply baked into Business Central’s filter parsing as a case-insensitivity operator, and there’s no documented escape mechanism for it.

Summary

This video highlights a genuine edge case in Business Central’s filtering system. While the documentation covers escaping for symbols like &, (), =, and |, the @ symbol — which serves as a case-insensitive filter operator — has no documented escape mechanism. This means that values beginning with @ (such as Twitter/X handles or email-related data) may be effectively unfilterable through normal means. Erik invites the community to weigh in with solutions — so if you know the answer, this is your chance to be the hero.

Picture of hougaard

hougaard