Using an ‘OR’ Filter in Dynamics 365 Business Central with FilterGroup(-1)

In Microsoft Dynamics 365 Business Central, a FilterGroup contains a filter set on a Record. The current filter group can be set or retrieved with the following syntax:

[CurrGroup] := Record.FILTERGROUP([NewGroup])

Record Filters are set with the SetFitler, or SetRange Functions of a Record.

Record.SetRange(Field [,FromValue] [,ToValue]) 
Record.SetFilter(Field, String, [Value],...)

The filters set across different groups are in effect at the same time. To work with filters in a specific FilterGroup, you must first set the group and then apply the filter. Microsoft Dynamics 365 Business Central uses several FilterGroups internally within the application. The FilterGroup 0 is the user’s group when filtering from the filter dialog. A user cannot change filters set in a group other than 0.

Setting Record filters in Microsoft Dynamics 365 Business Central on multiple columns, all column filters are in effect and act as a “and” filter; only records that match the criteria in all filtered columns will be displayed.

When using the Customer list above, if we apply the filter ‘@*John*’ on the name column, all Customers that contain ‘John’ are returned in the record set.

var
    Customer: Record Customer;
begin
    Customer.SetFilter(Name, '@*John*');
    Page.Run(Page::"Customer List", Customer);
end;

Applying a Salesperson Code filter of ‘JO’ to the same Customer list returns all Customers with the Salesperson Code ‘JO.’

var
    Customer: Record Customer;
begin
    Customer.SetFilter("Salesperson Code", 'JO');
    Page.Run(Page::"Customer List", Customer);
end;

If the two filters are set together, we would only see a list of customers that match BOTH filter criteria.

The FilterGroup -1 allows you to perform a cross-column filter, treating the filters as individual filters, which act as an “or” filter. Setting the FilterGroup to -1 and applying the same set of filters, ‘*@John*’ to the Name and ‘JO’ to the Salesperson Code, results in a Recordset of Customers that have either ‘John’ in the name or JO as a Salesperson.

var
    Customer: Record Customer;
begin
    Customer.FilterGroup(-1);
    Customer.SetFilter(Name, '@*John*');
    Customer.SetFilter("Salesperson Code", 'JO');
    If Customer.FindSet() then
        repeat
            Customer.Mark(true);
        until Customer.Next = 0;

    Customer.MarkedOnly(true);

    Page.Run(Page::"Customer List", Customer);
end;

Cross-column filters do not work within the user interface. If you’d like to display the records on a page, you must do it differently by marking them using a temporary record set or another option.

Note: The code and information discussed in this article are for informational and demonstration purposes only. This content was created referencing Microsoft Dynamics 365 Business Central 2022 Wave 2 online.