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.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.