Enable optimized text search on table fields

5 minutes

Business Central includes an optimized text search feature on table fields, which uses the full-text search feature in SQL Server and Azure SQL Database databases. Optimized text search lets users and applications run full-text queries against character-based data in tables. This unit explains how to enable optimized text search on table fields and how you can use it to filter data in AL.

In the client, optimized text search is used in various search functionalities, such as in the company-wide data search and on lists that include optimized text search fields, referred to as modern search. See Search for a record in company data and Searching. By enabling optimized text search on fields in extensions, you can significantly optimize data search in your AL code.

To enable optimized text search on field, set the OptimizeForTextSearch Property to true. The following example shows how text search is enabled on the field level:

table 50100 Address
{
    Caption = 'Sample table';

    fields
    {
        field(1; Address; Text[50])
        {
            Caption = 'Address retrieved by Service';
            OptimizeForTextSearch = true;
        }
        field(2; Locality; Text[30])
        {
            Caption = 'Locality retrieved by Service';
            OptimizeForTextSearch = true;
        }
        field(3; "Town/City"; Text[30])
        {
            Caption = 'Town/City retrieved by Service';
            OptimizeForTextSearch = true;            
        }
        field(4; County; Text[30])
        {
            Caption = 'County retrieved by Service';
            OptimizeForTextSearch = true;
        }
        field(5; IsValidated; Boolean)
        {
            Caption = 'Address validated yet?';
        }        
    }

You can use the OptimizedForTextSearch property on the Field virtual table. You can also use the IsOptimizedForTextSearch() method on FieldRef type variables of the FieldRef data type.

To use optimized text search in AL, you can add the && operator when setting a filter, as shown in the following example:

// Optimized text search without wildcard
Rec.SetFilter(Rec.Field, '&&' + SearchString);

You can also include wildcards in full-text search by adding the '*' symbol to the search clause as shown in the following example:

// Optimized text search with wildcard
Rec.SetFilter(Rec.Field, '&&' + SearchString + '*' );

Optimized text search differs from the traditional wildcard search using the '*' symbol in the following ways:

Optimized text search is always case insensitive, where wildcard search is case sensitive unless either the dataset collation is insensitive or the @ operator is prefixed.
Optimized text search is always accent insensitive, where wildcard search is accent sensitive unless either the dataset collation is insensitive or the @ operator is prefixed.
Optimized text search searches for words within fields, where wildcard search for letters within fields. That means full-text search can find words or prefixes of words within a field, but wildcard search can find arbitrary substrings within words.

Enable optimized text search on table fields

Feedback