The work continues on the Search App. In this episode I tackle the full text search.
In this third installment of the Search App series, Erik demonstrates how to add full text search capabilities to the Business Central search application. Building on the primary key search from previous episodes, this video walks through implementing a search that scans entire records by leveraging the Format function on RecordRef variables, along with adding a result limit to keep performance in check.
Where We Left Off
The Search App already has a working foundation: a search UI, search setup table, result page, and a central code unit containing all the search logic. The search setup table defines which tables should be searched, and up to this point, only primary key searching has been implemented. In this episode, the goal is to add full text search — the ability to find records based on any field value, not just the primary key.
The Search Architecture
The main Search procedure in the Search Management codeunit accepts a search term and a temporary record variable for results. It loops through all configured search setup records, opens a RecordRef to each table, and performs the search. The full text search is implemented as a fallback — if a primary key search doesn’t find anything and full text search is enabled for that table, the app will scan all records in the table.
Implementing Full Text Search
The Core Technique: Format on RecordRef
The key insight behind the full text search is a clever use of the Format function on a RecordRef variable. When you call Format on a RecordRef, it returns a tab-separated string containing all field values from that record. This gives you a single string representation of the entire record that you can search against.
It’s worth noting that this used to return a comma-separated string before NAV 2013 R2, but was changed to tab-separated — which is actually better since it avoids conflicts with commas that might appear in your data.
The Search Loop Pattern
For traversing an entire table in AL, the proper pattern is:
if FindSet() then
repeat
// process each record
until Next() = 0;Since we’re only reading data (not updating), we can pass false, false to FindSet to optimize how the backend retrieves and caches the data.
Case-Insensitive Searching
To ensure the search is case-insensitive, both the formatted record string and the search term are converted to lowercase. An important optimization here: rather than converting the search term to lowercase on every iteration of the loop, it’s prepared once before the loop begins:
LowerSearchTerm := SearchTerm.ToLower();Erik also points out the two equivalent syntax options in AL for string operations:
// Dot syntax (method style)
LowerSearchTerm := SearchTerm.ToLower();
// Function syntax (classic style)
LowerSearchTerm := LowerCase(SearchTerm);Both produce the same result — it’s a matter of preference. The video uses the dot syntax consistently since it chains nicely with the Contains method used later.
Using Contains Instead of Position-Based Search
The Contains function is used rather than a position-finding function because we only care about whether the search term exists somewhere in the record — we don’t need to know where. This avoids wasting CPU cycles on determining the exact position.
The Complete Search Procedure
Here is the full search codeunit with both primary key and full text search implemented:
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;
// ... DataCaption and NavigateTo procedures
}Why a Separate RecordRef Variable?
Notice that a separate FullRef variable is used for the full text search rather than reusing the existing Ref. This is intentional — the primary key search already applied a filter range on Ref, so reusing it would require resetting filters. More importantly, keeping the full text search self-contained in its own variable makes the code more portable. If you ever need to extract it into its own procedure, you won’t have hidden dependencies on variable state set elsewhere.
Important: Closing RecordRef Variables in Loops
A critical gotcha when working with RecordRef variables inside loops: a RecordRef can only be opened once. Since the full text search code sits inside a loop that iterates through search setup records, you must close the RecordRef at the end of each iteration. Forgetting to call FullRef.Close() will cause a runtime error on the second iteration when you try to open it again.
Adding a Result Limit
Full text search can potentially return a huge number of results, so a limit field is added to the search setup to cap the number of results per table. The setup table gets a new field:
field(21; "Full Text Search Limit"; Integer)
{
Caption = 'Full Text Search Limit';
DataClassification = SystemMetadata;
}This field is also exposed on the setup page so administrators can configure it per table. The loop termination condition then incorporates this limit:
until (FullRef.Next() = 0) or (FullCounter = SearchSetup."Full Text Search Limit");For example, setting the limit to 5 means the search will stop adding results from that table after finding 5 matches, even if more exist.
The Search Setup Table
The complete setup table now supports three key configurations per table:
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;
}
}
}Lessons from Live Coding
The video provides a candid look at common development mistakes and how to debug them:
- Forgetting to close RecordRef in a loop — causes a “record is already open” runtime error on the second iteration
- Using the wrong variable after copy-paste — formatting
Refinstead ofFullRefin the search loop, which meant searching an empty/filtered record instead of the actual data - Copy-paste errors with result recording — saving the record ID from the wrong RecordRef variable, leading to incorrect navigation when clicking results
As Erik puts it: copy-paste is the source of about 80% of all bugs, because you copy something without fully considering all the references you’re bringing along.
Potential Optimizations
While the implementation works, Erik mentions several areas for future improvement:
- Timeout/record count limit — skip a table if too many records have been scanned without finding matches
- Sort order control — optionally search newest records first instead of following the default sort order
- DateTime field handling — the caption function currently includes datetime fields, which may produce noisy captions
- Search across data types — since
Formatconverts everything to a string, you can search for numbers, dates, and any other field type as long as the formatting matches
Summary
The “secret sauce” of this full text search implementation is the Format function on a RecordRef, which gives you a string representation of an entire record. Combined with ToLower() for case-insensitive comparison and Contains() for efficient substring detection, you get a surprisingly simple yet effective full text search for Business Central — all without needing external search services or complex indexing. The next episode in this series will cover how to make this search functionality accessible from outside of Business Central.
