Volodymyr Dvernytskyi
Personal blog about Navision & Dynamics 365 Business Central

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.


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

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()
    var
        RecRef: RecordRef;
        FieldRefVar: FieldRef;
        Counter: Integer;
        FilterTxt: Text;
    begin
        FilterTxt := '@*export*';
        RecRef.Open(Database::Customer);
        RecRef.FilterGroup(-1);
        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
                        FieldRefVar.CalcField();
                    FieldRefVar.SETFILTER(FilterTxt);
                end;
        end;
        if RecRef.FindSet() then
            repeat
                Message(RecRef.GetPosition());
            until RecRef.Next() = 0;
        RecRef.Close();
    end;

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()
    var
        SalesLine: Record "Sales Line";
    begin
        //Full set of records, count is 33
        message(Format(SalesLine.Count()));

        SalesLine.FilterGroup(-1);
        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
        message(Format(SalesLine.Count()));
    end;

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.

Summary

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

Author: Volodymyr Dvernytskyi