Dynamics 365 Business Central: Cross-column search (OR filters on multiple fields) ★FilterGroup(-1)

Hi, Readers.
Yesterday we discussed how to count the same value of different fields in one line (For example, how many “OK” in a line) in Business Central, I received a lot of feedback, which was much appreciated.

One of them is a very interesting topic raised by Jeremy Vyska.

He has provided us with the answer. More details: Twitter info
And the source code: Demo-FilterGroupNegativeOne

In his example, he used the Record.FilterGroup([Integer]) Method, Record.FilterGroup(-1).

According to the method he provided, I applied it to my test code yesterday.

Result:

Keypoint:

So in this post I would like to briefly talk about what is Record.FilterGroup(-1). Hope this will help.
Let’s take a look at MS Docs first.

Record.FilterGroup([Integer]) Method: Gets or sets the filter group that is applied to a table.

Dynamics 365 Business Central uses the following filter groups internally. (-1..7)

And Microsoft provides a good example.

The following example finds all customers where the Customer Name or Contact Name contains the string John.

var
    SearchString: Text;
begin
    Customer.FilterGroup := -1;  
    SearchString := '@*John*';  
    Customer.SetFilter(Customer.Name, SearchString);  
    Customer.SetFilter(Customer.Contact, SearchString);  
end;

Let’s see more details.
If you have filters on multiple fields in the same filter group, then only records matching all filters are visible. (And Condition)
For example,

The Customer table.

Add two filters, “Salesperson Code” is ‘JO’ and “Location Code” is ‘EAST’.

We will get a record.

The only exception to this is filtergroup -1 where records only need to match at least one of the filters. (OR Condition)
For example,

The Customer table.

Add FilterGroup(-1) and two filters, “Salesperson Code” is ‘JO’ or “Location Code” is ‘EAST’.

We will get three records.

Test Video:

Actually Record.FilterGroup(-1) is the same as the SQL query below.

SELECT [No_]
      ,[Name]
      ,[Salesperson Code]
      ,[Amount]
      ,[Location Code]
  FROM [Demo Database BC (20-1)].[dbo].[CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972]
  WHERE [Salesperson Code]='JO' OR [Location Code]='BLUE';

PS:
1. Record.FilterGroup(-1) cannot be used to add default filter in OnOpenPage trigger.

For example, I did the following test where we get three records, but still show all records on the page. I think this should be caused by the system’s inability to add this special filters. So please use the method I tested above.

General filtering:

2. Source Code:

page 50100 "New Customer List"
{
    ApplicationArea = All;
    Caption = 'New Customer List';
    PageType = List;
    SourceTable = Customer;
    SourceTableTemporary = true;
    UsageCategory = Lists;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field("No."; Rec."No.")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the number of the customer. The field is either filled automatically from a defined number series, or you enter the number manually because you have enabled manual number entry in the number-series setup.';
                }
                field(Name; Rec.Name)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the customer''s name.';
                }
                field("Responsibility Center"; Rec."Responsibility Center")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the code for the responsibility center that will administer this customer by default.';
                }
                field("Salesperson Code"; Rec."Salesperson Code")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies a code for the salesperson who normally handles this customer''s account.';
                }
                field("Location Code"; Rec."Location Code")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies from which location sales to this customer will be processed by default.';
                }
                field("Phone No."; Rec."Phone No.")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the customer''s telephone number.';
                }
                field(Contact; Rec.Contact)
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the name of the person you regularly contact when you do business with this customer.';
                }
                field("Balance (LCY)"; Rec."Balance (LCY)")
                {
                    ApplicationArea = All;
                    ToolTip = 'Specifies the payment amount that the customer owes for completed sales. This value is also known as the customer''s balance.';
                }
            }
        }
    }

    trigger OnOpenPage()
    var
        Cust: Record Customer;
    begin
        Cust.Reset();
        //Cust.FilterGroup(-1);
        Cust.SetRange("Salesperson Code", 'JO');
        Cust.SetRange("Location Code", 'EAST');
        if Cust.FindSet() then
            repeat
                Rec := Cust;
                Rec.Insert();
            until Cust.Next() = 0;
    end;
}

END

Hope this will help.

Thanks for reading.

ZHU