SetFilter or SetRange, what to use and when?

In this video, I take a shot at explaining the difference between SetFilter and SetRange and when to use which one, check it out:

https://youtu.be/4aQFgji1SDM

In this video, Erik tackles a common source of confusion (and bugs!) in AL development for Business Central: the difference between SetFilter and SetRange, and when you should use each one. He walks through multiple ways of setting a filter on a record, demonstrates the pitfalls of each approach, and establishes a clear rule for which method to prefer.

The Problem: Multiple Ways to Set a Filter

When you need to filter records in AL, you have several options at your disposal. Erik demonstrates four different ways to set a filter on the "Posting Date" field of a G/L Entry record — but not all of them are created equal.

pageextension 50143 CustomerListExt extends "Customer List"
{
    trigger OnOpenPage();
    var
        GL: Record "G/L Entry";
        D: Date;
        D2: Date;
        D3 : Date;
        T1,T2,T3 : Text;
    begin
        D := 20220505D;
        D2 := 20220606D;
        GL.SetFilter("Posting Date", '5/5/22..6/6/22'); // BAD
        GL.SetFilter("Posting Date", format(D) + '|' + format(D2)); // semi-BAD
        GL.SetFilter("Posting Date", '%1|%2|%3', D, D2,D3); // Not-Bad
        GL.SetRange("Posting Date", D);

        GL.SetFilter(Description,'%1','sdfgsdfg');
    end;
}

Let’s break down each approach and understand why some are dangerous and others are safe.

Approach 1: Hardcoded String Filter — BAD

GL.SetFilter("Posting Date", '5/5/22..6/6/22'); // BAD

This is bad on multiple levels:

  • Date format ambiguity: Is 5/5/22 May 5th or the 5th of May? You don’t really know whether it’s month/day or day/month without knowing the system’s locale settings.
  • No compile-time validation: You could type complete nonsense into that string and the compiler wouldn’t complain. For example, you could pass in a time value like '12:12:22' — the compiler accepts it happily, and AL may even interpret it as a date at runtime, leading to completely unexpected filter behavior.
  • Runtime evaluation only: The content of the string is evaluated at runtime, meaning errors only surface when users encounter them in production.

Erik demonstrated this vividly by passing a time-formatted string ('12:12:22') as a date filter. No error was thrown — the system simply interpreted the colons and set a date filter, producing silently wrong results.

Approach 2: Using Format() to Build the String — Semi-BAD

GL.SetFilter("Posting Date", format(D) + '|' + format(D2)); // semi-BAD

This is slightly better because at least the Format function ensures your date variable is converted to a string in the system’s expected format. However, it’s still problematic:

  • Still just a string: You’re still building a filter string manually. If you accidentally pass a decimal variable through Format(), it will happily convert it to a string and the compiler won’t catch the type mismatch.
  • No type safety: The compiler has no way to verify that the formatted value is appropriate for the field you’re filtering on.

Approach 3: SetFilter with Substitution Parameters — Not Bad

GL.SetFilter("Posting Date", '%1|%2|%3', D, D2, D3); // Not-Bad

Now we’re getting somewhere. With substitution parameters (%1, %2, %3), the compiler performs type checking. It examines the field ("Posting Date" is a Date), and then verifies that each substitution argument matches that type.

Erik demonstrated this by trying to pass a Decimal variable into a Date field’s substitution parameter:

“Argument three cannot convert from Decimal to the type of argument one” — a compile-time error, exactly what we want.

This approach is essential when you need complex filter expressions that SetRange can’t handle — such as filtering on three specific dates, or combining values with pipes (|) for “OR” logic.

Approach 4: SetRange — The Best Option

GL.SetRange("Posting Date", D);
GL.SetRange("Posting Date", D, D2); // for a range

SetRange provides the cleanest syntax and full compile-time type checking. It accepts either one parameter (for an exact match) or two parameters (for a from/to range). If you try to pass a Decimal into a Date field, you get the exact same compiler error as with substitutions.

The advantages of SetRange:

  • Cleanest syntax: No format strings, no substitution placeholders.
  • Full type checking at compile time: Mismatched types are caught before your code ever runs.
  • Protection against field type changes: If someone changes a field’s data type, the compiler will immediately flag all the places where SetRange is used with the wrong type.

What About Text Fields?

Erik anticipates the objection: “But what if I’m filtering on a text field? Can’t I just use the string-based approach?”

The answer is still no. Consider what happens if a field’s data type changes. If you’re using SetFilter with a raw string, the compiler won’t catch the mismatch — your users will discover it as a runtime error. But if you’re using substitution parameters or SetRange, the compiler catches it immediately:

GL.SetFilter(Description, '%1', 'sdfgsdfg'); // Safe — compiler checks the type

By forcing yourself to always use typed parameters, you ensure that any future type changes to fields are caught at compile time rather than in production.

The Rule

Erik’s hierarchy is clear and simple:

  1. Always prefer SetRange — Use it for single values or simple from/to ranges. It gives you the cleanest syntax and full compile-time type safety.
  2. Use SetFilter with substitution parameters (%1, %2, etc.) — When your filter is more complex than a simple range (e.g., filtering on multiple discrete values like '%1|%2|%3'), step up to SetFilter with substitutions. You still get compile-time type checking.
  3. Never use SetFilter with hardcoded strings — No type checking, locale-dependent date formats, and silent runtime errors make this approach a bug factory.
  4. Never use SetFilter with Format()-built strings — Marginally better than hardcoded strings, but still no type safety.

Summary

The key takeaway is straightforward: SetRange should be your default choice for filtering records in AL. It provides clean syntax and catches type mismatches at compile time. When you need more complex filter expressions that SetRange can’t express, use SetFilter with substitution parameters to maintain type safety. The string-based approaches — whether hardcoded or built with Format() — should be avoided entirely. They bypass compile-time checks and introduce subtle bugs that may not surface until your users encounter them in production. By following this simple rule, you’ll greatly reduce the number of random runtime errors in your Business Central extensions.

Picture of hougaard

hougaard