Use RecordRefs to create a Search App in Business Central – Video Series

In this series, we’ll explore how to create a search app for Microsoft Dynamics 365 Business Central using RecordRef to make the search functionality generic.

Episode 2 where I build a data caption function:

Source code can be found here: https://github.com/hougaard/Youtube-Video-Sources/

In this video, Erik demonstrates how to use RecordRef, KeyRef, and FieldRef — the dynamic record access types in Business Central — to build a practical search application from scratch. The app allows users to configure which tables to search, performs primary key lookups and full text searches across those tables, and navigates to the appropriate card page when a result is clicked.

Why RecordRef?

Erik has a well-known fondness for RecordRefs — the Business Central data type that lets you dynamically open different tables and access their fields and keys at runtime. Rather than creating hard-coded references to specific tables, RecordRefs allow you to write generic code that works against any table. This makes them perfect for building a search app that can search across customers, vendors, items, or any other table the user configures.

The Architecture

The search app consists of four main components, which Erik pre-built to save time on boilerplate:

  • Search Setup table — Defines which tables to search, how to search them, and which card page to open for navigation
  • Search Result table — A temporary table that holds the search results for display
  • Search UI page — The main search interface with a search term field and results list
  • Search Management codeunit — Contains all the search logic and navigation code

Search Setup Table

The setup table lets users specify which tables to search and how to search them. It includes a table number, boolean flags for primary key search and full text search, a full text search limit, and a card page number for navigation:

table 51100 "Search Setup"
{
    Caption = 'Search Setup';
    DataClassification = ToBeClassified;

    fields
    {
        field(1; "Table No."; Integer)
        {
            Caption = 'Table No.';
            DataClassification = SystemMetadata;
            TableRelation = AllObjWithCaption."Object ID" where("Object Type" = const(Table));
        }
        field(10; "Search Primary Key"; Boolean)
        {
            Caption = 'Search Primary Key';
            DataClassification = SystemMetadata;
        }
        field(20; "Full Text Search"; Boolean)
        {
            Caption = 'Full Text Search';
            DataClassification = SystemMetadata;
        }
        field(21; "Full Text Search Limit"; Integer)
        {
            Caption = 'Full Text Search Limit';
            DataClassification = SystemMetadata;
        }
        field(50; "Card Page"; Integer)
        {
            Caption = 'Card Page No.';
            DataClassification = SystemMetadata;
            TableRelation = AllObjWithCaption."Object ID" where("Object Type" = const(Page));
        }
    }
    keys
    {
        key(PK; "Table No.")
        {
            Clustered = true;
        }
    }
}

A key detail here is the use of TableRelation pointing to the AllObjWithCaption system table. By filtering on "Object Type" = const(Table) for the table number field and "Object Type" = const(Page) for the card page field, users get nice lookups showing only tables or pages respectively. For example, you’d configure table 18 (Customer) with card page 21 (Customer Card), table 23 (Vendor) with the Vendor Card, and table 27 (Item) with the Item Card.

Search Result Table

The search result table is designed to be used only as a temporary table — there’s no reason to store search results in the database. It captures the line number, table number, a record ID (the primary key of the found record), and a data caption for display:

table 51101 "Search Result"
{
    Caption = 'Search Result';
    DataClassification = ToBeClassified;

    fields
    {
        field(1; "Line No."; Integer)
        {
            Caption = 'Line No.';
            DataClassification = SystemMetadata;
        }
        field(10; "Table No."; Integer)
        {
            Caption = 'Table No.';
            DataClassification = SystemMetadata;
        }
        field(11; "Table Name"; Text[50])
        {
            Caption = 'Table Name';
            FieldClass = FlowField;
            CalcFormula = lookup (AllObjWithCaption."Object Name" where("Object Type" = const(Table), "Object ID" = field("Table No.")));
        }
        field(15; "Record ID"; RecordId)
        {
            Caption = 'Record ID';
            DataClassification = SystemMetadata;
        }
        field(20; "Data Caption"; Text[250])
        {
            Caption = 'Data Caption';
            DataClassification = SystemMetadata;
        }
    }
    keys
    {
        key(PK; "Line No.")
        {
            Clustered = true;
        }
    }
}

Notice the clever use of a FlowField on the “Table Name” field (field 11). It performs a lookup to AllObjWithCaption to automatically resolve the table number into a human-readable name like “Customer” or “Vendor” — no extra code needed.

The Search UI Page

The search page uses an interesting pattern to handle editability. The page itself is marked as Editable = true, but InsertAllowed = false and DeleteAllowed = false — so users can’t modify the result data. The reason for keeping the page editable is the search term field: if you set the entire page to Editable = false, you can’t override individual fields back to editable. However, you can go from editable to non-editable on individual fields.

page 51101 "Search"
{
    Caption = 'Search';
    PageType = List;
    SourceTable = "Search Result";
    UsageCategory = Tasks;
    ApplicationArea = All;
    SourceTableTemporary = true;
    Editable = true;
    InsertAllowed = false;
    DeleteAllowed = false;

    layout
    {
        area(content)
        {
            Group(SearchGrp)
            {
                Caption = 'Search';
                field(Search; SearchTxt)
                {
                    Caption = 'Search term';
                    ApplicationArea = all;
                    Editable = true;
                    trigger OnValidate()
                    var
                        Mgt: Codeunit "Search Management";
                    begin
                        Mgt.Search(SearchTxt, Rec);
                    end;
                }
            }
            repeater(General)
            {
                field("Data Caption"; "Data Caption")
                {
                    ApplicationArea = All;
                    Editable = false;
                    DrillDown = true;
                    trigger OnDrillDown()
                    var
                        Mgt: Codeunit "Search Management";
                    begin
                        Mgt.NavigateTo(Rec);
                    end;
                }
                field("Table Name"; "Table Name")
                {
                    ApplicationArea = All;
                    Editable = false;
                }
            }
        }
    }
    actions
    {
        area(Processing)
        {
            action(Setup)
            {
                Caption = 'Setup';
                ApplicationArea = all;
                ToolTip = 'Setup tables to search in';
                Image = Find;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                PromotedOnly = true;
                RunObject = Page "Search Tables";
            }
        }
    }
    var
        SearchTxt: Text;
}

The search is triggered on the OnValidate trigger of the search term field — so you type your search term, press Enter (or Tab, or click elsewhere), and the search executes. The Data Caption field has a DrillDown trigger that calls NavigateTo to open the appropriate card page.

Erik also notes an interesting quirk: when the page first loads as part of a Role Center, it appears non-editable because Role Center pages are by definition not editable. Opening the page directly (via search or URL) restores the expected editable behavior.

The Search Management Codeunit — The Heart of RecordRef

This is where all the dynamic magic happens. The codeunit contains three key procedures: Search, DataCaption, and NavigateTo.

codeunit 51100 "Search Management"
{
    procedure Search(SearchTerm: Text; var Result: Record "Search Result" temporary)
    var
        SearchSetup: Record "Search Setup";
        Ref: RecordRef;
        FullRef: RecordRef;
        KRef: KeyRef;
        FRef: FieldRef;
        LowerSearchTerm: Text;
        LastResult: Integer;
        FullCounter: Integer;
        OnlyONeFieldPrimaryKeyErr: Label 'Primary key search is only supported on tables with 1 field in the key.';
    begin
        Result.Deleteall();
        if SearchSetup.findset() then
            repeat
                Ref.OPEN(SearchSetup."Table No.");
                if SearchSetup."Search Primary Key" then begin
                    KRef := Ref.KeyIndex(1);
                    if Kref.FieldCount() <> 1 then
                        error(OnlyOneFieldPrimaryKeyErr);
                    FRef := KRef.FieldIndex(1);
                    FRef.SetRange(SearchTerm);
                    if Ref.FindFirst() then begin
                        LastResult += 1;
                        Result.INIT();
                        Result."Line No." := LastResult;
                        Result."Table No." := SearchSetup."Table No.";
                        Result."Data Caption" := CopyStr(DataCaption(Ref), 1, maxstrlen(Result."Data Caption"));
                        Result."Record ID" := Ref.RecordId;
                        Result.Insert();
                    end else
                        if SearchSetup."Full Text Search" then begin
                            FullRef.OPEN(SearchSetup."Table No.");
                            LowerSearchTerm := SearchTerm.ToLower();
                            FullCounter := 0;
                            if FullRef.FindSet(false, false) then
                                repeat
                                    if format(FullRef).ToLower().Contains(LowerSearchTerm) then begin
                                        LastResult += 1;
                                        Result.INIT();
                                        Result."Line No." := LastResult;
                                        Result."Table No." := SearchSetup."Table No.";
                                        Result."Data Caption" := CopyStr(DataCaption(FullRef), 1, maxstrlen(Result."Data Caption"));
                                        Result."Record ID" := FullRef.RecordId;
                                        Result.Insert();
                                        FullCounter += 1;
                                    end;
                                until (FullRef.Next() = 0) or (FullCounter = SearchSetup."Full Text Search Limit");
                            FullRef.Close();
                        end;
                end;
                Ref.Close();
            until SearchSetup.Next() = 0;
    end;

Key RecordRef Concepts Demonstrated

  1. Opening and Closing — You must call Ref.OPEN(TableNo) to tell the RecordRef which table to work with, and Ref.Close() when you’re done. Erik emphasizes that forgetting to close RecordRefs is the number one mistake developers make. When a RecordRef is closed, any associated KeyRef or FieldRef variables become undefined.
  2. KeyRef for accessing keys — Ref.KeyIndex(1) retrieves the first key, which by definition is always the primary key. From a KeyRef, you can get the field count and access individual fields.
  3. FieldRef for filtering — Filters in RecordRef are applied to fields, not to the record itself. You get a FieldRef from the KeyRef (KRef.FieldIndex(1)) and then apply the filter: FRef.SetRange(SearchTerm).
  4. Format on RecordRef — Calling Format(Ref) on a RecordRef returns the entire record as a comma-separated text string. This is used for the full text search to check if any field in the record contains the search term.

The Classic FindSet/Repeat Pattern

The code uses the standard Business Central pattern for iterating through records:

if SearchSetup.FindSet() then
    repeat
        // process each record
    until SearchSetup.Next() = 0;

Erik explains that Next() without parameters is equivalent to Next(1). It returns the number of steps actually taken — so when there are no more records, it returns 0 and the loop exits. You could also use Next(-1) to go in reverse.

Full Text Search

The full text search is a fallback when the primary key search doesn’t find results. It opens a separate RecordRef, iterates through all records in the table, converts each record to a lowercase string using Format(FullRef).ToLower(), and checks if it contains the lowercase search term. A configurable limit prevents the search from returning too many results:

if format(FullRef).ToLower().Contains(LowerSearchTerm) then begin
    // add to results
    FullCounter += 1;
end;
until (FullRef.Next() = 0) or (FullCounter = SearchSetup."Full Text Search Limit");

Building a Smart Data Caption

The DataCaption procedure creates a readable description of each found record. Rather than just dumping all field values, it uses a more intelligent approach with dictionaries to track which fields and field types have already been included:

procedure DataCaption(Ref: RecordRef): Text
var
    i: Integer;
    Fref: FieldRef;
    Result: Text;
    UsedFields: Dictionary of [Integer, Text];
    UsedFieldTypes: Dictionary of [Text, Text];
begin
    // First: add all primary key fields
    for i := 1 to Ref.KeyIndex(1).FieldCount() do begin
        Fref := Ref.FieldIndex(i);
        UsedFields.Add(Fref.Number(), Fref.Name());
        if not UsedFieldTypes.ContainsKey(format(Fref.Type())) then
            UsedFieldTypes.Add(Format(Fref.Type()), format(Fref.Type()));
        if Result <> '' then
            result += ' ' + format(FRef.Value())
        else
            Result += format(Fref.Value());
    end;

    // Then: add remaining fields based on type
    for i := 1 to Ref.FieldCount() do begin
        Fref := Ref.FieldIndex(i);
        if not UsedFields.ContainsKey(Fref.Number()) then begin
            case Fref.Type of
                FieldType::Text:
                    begin
                        if Result <> '' then
                            result += ' ' + format(FRef.Value())
                        else
                            Result += format(Fref.Value());
                    end;
                else begin
                    if not UsedFieldTypes.ContainsKey(format(Fref.Type())) then begin
                        if Result <> '' then
                            result += ' ' + format(FRef.Value())
                        else
                            Result += format(Fref.Value());
                        UsedFieldTypes.Add(Format(Fref.Type()), format(Fref.Type()));
                    end;
                end;
            end;
        end;
    end;
    exit(Result);
end;

It starts with all primary key fields, then adds all Text fields (since they typically contain the most useful descriptive data), and finally adds one field of each other type that hasn’t been included yet. This produces captions like “10000 Fabrikam” which are much more useful than a raw data dump.

Navigation with the Variant Trick

The NavigateTo procedure demonstrates an important technique for using Page.Run with a RecordRef. Normally, Page.Run expects a specific Record type, but since we’re working dynamically, we use the Variant data type as a bridge:

procedure NavigateTo(Rec: Record "Search Result")
var
    Setup: Record "Search Setup";
    Ref: RecordRef;
    RefVari: Variant;
begin
    Setup.get(Rec."Table No.");
    Setup.TestField("Card Page");
    Ref.OPEN(Setup."Table No.");
    Ref.Get(Rec."Record ID");
    RefVari := Ref;
    Page.run(Setup."Card Page", RefVari);
end;

The procedure retrieves the setup to find the card page number, opens a RecordRef to the same table, uses Ref.Get(Rec."Record ID") to load the specific record (since we stored the Record ID in our search results), assigns the RecordRef to a Variant, and passes that Variant to Page.Run. This allows dynamic navigation to any card page — Customer Card, Vendor Card, Item Card — all with the same generic code.

Seeing It in Action

With the setup configured for Customers (table 18), Vendors (table 23), and Items (table 27), searching for “10000” returns results across all three tables. The data caption shows meaningful descriptions, the table name column shows which table each result came from, and clicking on a result navigates directly to the appropriate card page.

Summary

This episode covers the foundational architecture of the search app and the primary key search method. The key takeaways for working with RecordRefs are:

  • Always close your RecordRefs — This is the most common source of bugs when working with dynamic references
  • Filters go on FieldRefs, not RecordRefs — Get the field first, then apply the filter
  • KeyIndex(1) is always the primary key — Use this to access the primary key fields dynamically
  • Format(RecordRef) returns a text representation — Useful for full text searching and display
  • Use Variant as a bridge — When you need to pass a RecordRef to functions that expect a Record type, assign it to a Variant first
  • AllObjWithCaption — A handy system table for looking up table and page names dynamically

In the next episode, Erik plans to dive deeper into full text search and explore other creative search methods. The complete source code provides a solid foundation for anyone wanting to build dynamic, table-agnostic functionality in Business Central.

Picture of hougaard

hougaard