Filtering records is a fundamental task in Business Central AL development. Two common methods – SetRange and SetFilter – both limit the record set by a field’s values, but they work differently. This post compares SetRange and SetFilter in AL, shows code examples, and explains when to prefer one over the other. We’ll cover syntax, flexibility, performance, and special cases (like wildcards) to help you choose the right method.
What Are SetRange and SetFilter?
SetRange: Sets a simple filter on a single field. It takes up to two values – a lower and upper bound – and filters records inclusively between them. For example:
Cust.SetRange("No.", 1000, 1500);filters customers with No. from 1000 through 1500. If only one value is given, it filters for equality (from and to are the same). Calling SetRange with no values clears any existing filter on that field.
SetFilter: Allows complex filter expressions using comparison operators, wildcards (*), AND (&), OR (|), and placeholders (%1, %2, etc.). For example:
Cust.SetFilter("No.", '>1000 & <>2000');filters customers with No. greater than 1000 and not equal to 2000. You can also use placeholders for parameters:
Cust.SetFilter("No.", '%1|%2', '1000', '1500');which filters for No. = 1000 OR 1500 (OR expressions use |). To reset a filter with SetFilter, provide an empty string (SetFilter(“Field”, ”)).
Each method applies its filter immediately to the record buffer. Internally, SetRange is essentially a specialized form of SetFilter (one developer notes that SETRANGE is compiled into a %1..%2 filter). However, this specialization brings usability and performance benefits for simple cases.
When to Use SetRange
Simple Ranges & Single Values: Use SetRange for an exact value or a contiguous range. E.g., filtering by dates between StartDate and EndDate, or a numeric range is easy with SetRange:
SalesLine.SetRange("Posting Date", FromDate, ToDate);This reads clearly and shows intent.
Clarity & Readability: Because SetRange doesn’t require writing a filter string, it’s easier to read and less error-prone for basic filters. For example, Cust.SetRange(“City”, “London”) immediately indicates filtering by city = London, whereas SetFilter would need a string expression.
Performance Considerations: In practice, SetRange may be marginally faster for simple filters since it avoids parsing a string expression. (Official sources note any performance difference is usually negligible in modern versions, but using SetRange is still a good practice for clarity and slight efficiency.)
Resetting Filters: Calling SetRange with no value (just the field name) clears the filter on that field. For example:
Cust.SetRange("No."); // removes any existing filter on "No."This is a convenient way to reset filters without referring to the original values.
When to Use SetFilter
Complex Conditions: Use SetFilter when you need advanced filtering that SetRange can’t do. For example, multiple non-contiguous values, wildcards, or comparisons:
Cust.SetFilter("Name", '%1|%2', 'ABC', 'XYZ'); // Name = 'ABC' OR 'XYZ'
Cust.SetFilter("Description", '%1', 'Item*'); // Name starts with 'Item'
Cust.SetFilter("Amount", '>1000 & <5000'); // Amount > 1000 AND < 5000These examples use OR (|), wildcard (*), and range operators, which are not possible with SetRange.
String & Wildcard Matching: SetFilter recognizes wildcard and special characters in filter expressions. For instance, ‘A*’ matches any value starting with A. (Note: % and _ are also wildcard characters). In contrast, SetRange treats wildcards as literals, so SetRange(“Code”, ‘A*’) would look for the literal code “A*”.
Multiple Values & Placeholders: You can filter on multiple values at once. SetFilter(“Field”, ‘%1|%2’, v1, v2) filters for v1 OR v2. You can even mix operators, e.g. %1..%2 for a range, or use %1…%n for multiple items. This flexibility is unique to SetFilter.
Existing Filters: Note that calling SetFilter replaces any previous filter on that field (just like SetRange does). If you need to combine with existing filters, use FilterGroup or the MAX filter group.
Code Examples
// Using SetRange to filter a date range
SalesHeader.SetRange("Document Date", StartDate, EndDate);
// Equivalent with SetFilter
SalesHeader.SetFilter("Document Date", '%1..%2', StartDate, EndDate);
// Filtering by a pattern (wildcard) – only SetFilter supports this
Customer.SetFilter("Name", '%1', 'Contoso*'); // Name starts with "Contoso"
// Filtering by multiple specific values – only SetFilter
Item.SetFilter("Category Code", '%1|%2', 'ELEC', 'FURN');
// Simple equality or range – use SetRange for clarity
ItemLedgerEntry.SetRange("Entry No.", 100, 200);
// Complex numeric condition – must use SetFilter
ItemLedgerEntry.SetFilter("Quantity", '>0&<1000'); // 0 < Quantity < 1000Each snippet shows typical use cases. SetRange is concise for simple ranges or equality, while SetFilter shines for patterns and composite criteria. Both methods generate appropriate SQL WHERE clauses under the hood (e.g. BETWEEN for SetRange, or >,<,LIKE for SetFilter).
Key Differences
Syntax:
SetRange(field, from, to) – easy, parameters only.
SetFilter(field, filterExpr, params…) – uses a filter string with % placeholders or literal operators.
Filter Type:
SetRange only handles a single contiguous range (or single value).
SetFilter handles full Navision filter expressions (>,<,=,*,%, &, |, etc.).
Wildcards:
SetRange does not support wildcards (* is treated as a character).
SetFilter supports % and * as wildcards (e.g. %1* matches any value starting with 1).
Multi-value (OR) Filters:
SetRange cannot filter for multiple separate values.
SetFilter can combine values with | for OR, and & for AND, allowing expressions like %1|%2.
Readability:
SetRange is straightforward and self-explanatory for ranges.
SetFilter strings can be harder to read and maintain, especially for long expressions.
Performance:
In modern BC, both compile to equivalent SQL, so performance is similar. However, SetRange avoids parsing a filter string, making it slightly faster for simple filters. In practice the difference is negligible unless you filter huge datasets in tight loops.
Resetting a Filter:
SetRange(“Field”) (no values) clears the filter on that field.
SetFilter(“Field”, ”) (empty string) also clears the filter (user consensus notes this pattern).
These differences are summarized below:
Comparison Table
| Feature | SetRange | SetFilter |
| Syntax | Record.SetRange(Field, from, to) <br>(or SetRange(Field) to clear) | Record.SetFilter(Field, filterExpr, [args]) <br>(clear by empty ”) |
| Filter Type | Simple range or single value | Flexible expressions (>, <, =, <>, *, %, &, |, etc.) |
| Use Case | Contiguous range or exact match | Complex filters: OR, wildcards, exclusions, etc. |
| Wildcards | Treated as literal (no wildcard match) | Supports %/* for pattern matching |
| Multi-value OR | Not supported | Supported via | (OR) or & (AND) |
| Performance | Slightly faster for simple filters (no string parse) | Slightly slower (parses string), but difference is usually negligible |
| Reset Filter | SetRange(Field); clears filter | SetFilter(Field, ”); clears filter |
| Readability | More readable for ranges | Harder to read when expressions grow |
Takeaway
Both SetRange and SetFilter are essential for AL filtering. Use SetRange whenever possible – it’s concise, safe (no surprises with special chars), and clearly communicates a range filter. Reserve SetFilter for advanced scenarios – when you need wildcards, OR conditions, or complex comparisons. By understanding their differences, you can write clearer, more efficient code in Business Central.
In summary: SetRange = Simple, Safe, Fast. SetFilter = Flexible, Powerful, Complex. Use the right tool for each filtering need to make your AL code robust and maintainable.
