Business central blog
Subscribe to blog and get news about new posts.

FilterGroup(-1), cross-column search in Business Central and Navision.

We all know how to work with FilterGroup method in development, but sometimes we forget about specific -1 FilterGroup, which is powerfull tool for filtering. Let's figure out what it is and learn how to work with it using examples.
From the Microsoft documentation, we see only a short description of FilterGroup (-1):
    Used to support the cross-column search.
    This is not enough to understand how it works. But, for our luck we can find example from documentation.
    • The following example finds all customers where the Customer Name or Contact Name contains the string John.
        SearchString: Text;
        Customer.FilterGroup := -1;  
        SearchString := '@*John*';  
        Customer.SetFilter(Customer.Name, SearchString);  
        Customer.SetFilter(Customer.Contact, SearchString);  
    This makes it more obvious how it works. Usually, we use AND statement for filtering, but in this case it is OR. It is important to remember that, operations with tables are sent to the SQL server in SQL syntax. So let's write pseudo-SQL code for our case, it will look like this:
    • Filtering Customer by Name/Contact with FilterGroup(-1)
    SELECT *
    FROM Customer  
    WHERE (Name like @'John') OR   
      (Contact like @'John')
    Let's replace Customer.FilterGroup(-1) to default Customer.FilterGroup(0)
    • Filtering Customer by Name/Contact with FilterGroup(0)
    SELECT *
    FROM Customer  
    WHERE (Name like @'John') AND   
      (Contact like @'John')
    Important: using * and forcing a case insensitive search (@) will effectively turn of indexing, this can negatively affect performance for large tables.

    Practical examples

    Find records with any match by filter

    Let's imagine that we need to find all records that contain specific data in any of the fields. In our specific example, let's look for the word 'export' inside any fields in table Customer:
    procedure CrossColumnSearchCustomers()
            RecRef: RecordRef;
            FieldRefVar: FieldRef;
            Counter: Integer;
            FilterTxt: Text;
            FilterTxt := '@*export*';
            FOR Counter := 1 TO RecRef.FieldCount() DO BEGIN
                FieldRefVar := RecRef.FieldIndex(Counter);
                if FieldRefVar.Class() <> FieldClass::FlowFilter then
                    if (FieldRefVar.Type() in [FieldType::Code, FieldType::Text])
                     and (FieldRefVar.Length() >= StrLen(FilterTxt)) then begin
                        if FieldRefVar.Class() = FieldClass::FlowField then
            if RecRef.FindSet() then
                until RecRef.Next() = 0;
    This code will find all records in the same way as Search on list pages, but for all text and code fields (page search only for page fields).

    Excluding a record from a table

    Let's say we have a set of Sales Line records from the entire base, and we need to exclude one specific record from the set. The easiest and fastest way to do this is to use FilterGroup (-1). Here is partial list of Sales Lines from my DB, let's say I want to exclude Sales Line from red box:
    procedure ExcludeEntryFromSalesLine()
            SalesLine: Record "Sales Line";
            //Full set of records, count is 33
            SalesLine.SetFilter("Document Type", '<>%1', SalesLine."Document Type"::Invoice);
            SalesLine.SetFilter("Document No.", '<>%1', '103205');
            SalesLine.SetFilter("Line No.", '<>%1', 20000);
            //Set of records exlude one specific entry, count is 32
    Important: Cross-column search does not work on UI side, it means, if you want to show filtered data on a page, you must do it in different way, for example, filter with FilterGroup(-1) and mark the records which you want to show.


    As you can see from the examples, we must not forget about the power of FilterGroup (-1), it is a powerful filtering tool that can solve many complex problems. My examples are just some of the possible uses of this group, this is just the tip of the iceberg.
    SUNDAY, October 04, 2020