13.1 Record Operations: Get, FindSet, ModifyAll & Filter Functions

13.1 Record Operations: Get, FindSet, ModifyAll & Filter Functions

In Microsoft Dynamics 365 Business Central, the Record data type is the core abstraction through which AL code interacts with persistent SQL database tables. Understanding how AL record methods translate into underlying Transact-SQL (T-SQL) queries, how locking behaviors impact multi-user concurrency, how filter groups protect business rules, and how virtual FlowFields are evaluated is essential for designing scalable enterprise extensions and achieving success on the MB-820 certification exam.

1. Finding & Navigating Records in AL

AL provides a rich set of retrieval and navigation functions. Selecting the optimal method directly dictates SQL Server query execution plans, middle-tier Navision Server Tier (NST) data caching, network payload size across Tabular Data Stream (TDS) connections, and multi-user concurrency.

local procedure DemonstrateRecordRetrievalPatterns()
var
    Customer: Record Customer;
    SalesLine: Record "Sales Line";
    ItemLedgerEntry: Record "Item Ledger Entry";
begin
    // 1. Direct Primary Key Lookup with Get()
    // Bypasses any existing filters on the record variable
    if Customer.Get('10000') then
        Message('Customer Name: %1', Customer.Name);

    // 2. Traversing Filtered Recordsets with FindSet()
    Customer.Reset();
    Customer.SetRange("Customer Posting Group", 'DOMESTIC');
    Customer.SetRange("Blocked", Customer.Blocked::" ");
    if Customer.FindSet(false, false) then
        repeat
            // Read-only processing of each customer record
        until Customer.Next() = 0;

    // 3. Single Boundary Retrieval with FindFirst() and FindLast()
    SalesLine.SetRange("Document Type", SalesLine."Document Type"::Order);
    SalesLine.SetRange("Document No.", 'SO-1001');
    if SalesLine.FindFirst() then
        Message('First Line Item: %1', SalesLine."No.");

    if SalesLine.FindLast() then
        Message('Last Line No.: %1', SalesLine."Line No.");

    // 4. High-Performance Existence Check with IsEmpty()
    ItemLedgerEntry.SetRange("Item No.", 'ITEM-001');
    ItemLedgerEntry.SetRange(Open, true);
    if not ItemLedgerEntry.IsEmpty() then
        Message('Active open ledger entries exist for this item.');
end;

Record Retrieval Methods Reference

Deep Dive: FindSet(ForUpdate, UpdateKey)

The FindSet() method is specifically optimized for looping through recordsets. It takes two optional Boolean arguments that control database locking and cursor stabilization:

1. ForUpdate (Boolean, default false):

   • When set to true, the SQL query generated by the NST includes the UPDLOCK (and potentially ROWLOCK) table hint.
   • This instructs SQL Server to acquire update locks immediately during data read, signaling an intention to modify or delete the records later in the transaction.
   • Why this matters: Placing UPDLOCK during FindSet(true) prevents deadlocks when two concurrent sessions read the same records with shared locks and subsequently attempt to elevate their shared locks to exclusive write locks (X locks).

2. UpdateKey (Boolean, default false):

   • When set to true, it informs the NST and SQL Server that the AL loop intends to modify one or more fields that comprise the currently active sorting key (CurrentKey).
   • Why this matters: When a key field value is modified inside a loop, SQL Server may reposition the record within the index B-tree. Without UpdateKey = true, the cursor might encounter the modified record a second time or skip adjacent records entirely (index cursor oscillation). Setting UpdateKey = true causes the runtime to buffer the keys safely to maintain consistent traversal.

Exam Performance Rule:

• Never use FindSet() if you only need a single record; use FindFirst(). FindSet() reads a block of records (default 50 rows) into the NST cache, creating unnecessary database I/O when only one record is inspected.
• Never use if Rec.Count() > 0 then or if Rec.FindSet() then merely to check for record existence. Always use if not Rec.IsEmpty() then, which generates SELECT TOP 1 1 and stops scanning immediately upon finding the first match without hydrating record buffers.

2. Record Modification & Set-Based Bulk Operations

AL provides both row-level manipulation statements (Insert, Modify, Delete, Rename) and high-throughput set-based operations (ModifyAll, DeleteAll).

local procedure DemonstrateDataModifications()
var
    Customer: Record Customer;
    SalesHeader: Record "Sales Header";
    SalesLine: Record "Sales Line";
begin
    // 1. Row-Level Insert with Trigger Execution
    Customer.Init();
    Customer."No." := 'CUST-88001';
    Customer.Name := 'Fabrikam Logistics Corp';
    Customer."Gen. Bus. Posting Group" := 'DOMESTIC';
    Customer."Customer Posting Group" := 'DOMESTIC';
    Customer.Insert(true); // Runs OnInsert table trigger and subscriber events

    // 2. Row-Level Modify without Trigger Execution
    Customer."Credit Limit (LCY)" := 150000;
    Customer.Modify(false); // Bypasses OnModify trigger logic

    // 3. Primary Key Renaming with Cascading Updates
    Customer.Get('CUST-88001');
    Customer.Rename('CUST-88999'); // Automatically updates all foreign key relations

    // 4. Set-Based Bulk Update with ModifyAll
    SalesLine.SetRange("Document Type", SalesLine."Document Type"::Order);
    SalesLine.SetRange("Document No.", 'SO-1001');
    SalesLine.SetRange(Type, SalesLine.Type::Item);
    // Direct T-SQL UPDATE statement executed on SQL Server (RunTrigger = false)
    SalesLine.ModifyAll("Location Code", 'MAIN', false);

    // 5. Set-Based Bulk Delete with DeleteAll
    SalesHeader.SetRange("Document Type", SalesHeader."Document Type"::Quote);
    SalesHeader.SetRange(Status, SalesHeader.Status::Draft);
    // Direct T-SQL DELETE statement executed on SQL Server (RunTrigger = false)
    SalesHeader.DeleteAll(false);
end;

Row-Level Manipulation & The RunTrigger Parameter

All row-level mutation methods (Insert, Modify, Delete) accept an optional RunTrigger: Boolean parameter (defaulting to false if omitted):

• Insert([RunTrigger]): When RunTrigger = true, executes the table's OnInsert trigger, initializes No. Series logic, sets default timestamps, and fires OnBeforeInsert / OnAfterInsert event subscribers.
• Modify([RunTrigger]): When RunTrigger = true, executes the table's OnModify trigger, validating state transitions and firing modification subscribers.
• Delete([RunTrigger]): When RunTrigger = true, executes the table's OnDelete trigger (frequently used to cascade deletions to document lines, comments, or archive tables).
• Rename(NewPK1, [NewPK2...]): Changes the primary key of an existing record. In addition to executing the OnRename trigger, Business Central inspects the entire data dictionary and automatically cascades the new primary key value to all related foreign key fields across all tables where a TableRelation is defined.

Set-Based Bulk Operations: ModifyAll & DeleteAll

ModifyAll(Field, NewValue [, RunTrigger]) and DeleteAll([RunTrigger]) execute mass updates or deletions across all records matching current filters:

ModifyAll / DeleteAll (RunTrigger = false) ──► Single Direct SQL UPDATE/DELETE ──► Minimal I/O
ModifyAll / DeleteAll (RunTrigger = true)  ──► Iterates Each Record in NST  ──► Runs OnModify/OnDelete Triggers

• When RunTrigger = false (Default):
  • The NST constructs a single direct T-SQL UPDATE or DELETE statement with a WHERE clause matching the active filters and sends it directly to SQL Server.
  • Individual records are never fetched into NST memory, companion tables are updated via set-based joins, and execution completes in milliseconds regardless of row count.
• When RunTrigger = true:
  • The NST must fetch every matching record over the network into memory one by one, execute the table's OnModify or OnDelete trigger along with all subscribed event handlers in AL, and issue individual SQL statements for each record.
  • Performance trade-off: Use RunTrigger = true only when business validation or audit logging on each record is strictly required.

3. Filtering Records & Scoping with FilterGroups

Filters define the subset of records returned by database queries. AL provides several methods to construct, inspect, copy, and isolate filters across different execution scopes.

local procedure ApplyAdvancedFilters(var Item: Record Item)
var
    FilterString: Text;
begin
    // 1. Simple Range Filter with SetRange
    Item.SetRange("Unit Price", 50, 250); // Unit Price BETWEEN 50 AND 250
    Item.SetRange(Blocked); // Clearing filter on Blocked by omitting parameters

    // 2. Complex Wildcard Expression with SetFilter
    // Matches items starting with 'CHAIR' or 'DESK', excluding 'CHAIR-09'
    Item.SetFilter("No.", 'CHAIR*|DESK*&<>CHAIR-09');
    // Case-insensitive match containing 'wood'
    Item.SetFilter(Description, '@*wood*');

    // 3. Inspecting and Copying Filters
    FilterString := Item.GetFilter("No.");
    Message('Active Filter on No.: %1', FilterString);
    Message('All Active Filters: %1', Item.GetFilters());

    // 4. Resetting Filters and Key Sorting
    Item.Reset(); // Removes all filters, marks, and resets sorting to Primary Key
end;

Filter Expression Syntax Reference

Scoping Filters with FilterGroup

Every record buffer maintains multiple distinct filter layers called FilterGroups (indexed from -1 to 255). When Business Central builds the final SQL WHERE clause, it applies an AND operation across all active filter groups.

local procedure ApplyProtectedDepartmentFilter(var CustLedgEntry: Record "Cust. Ledger Entry"; DeptCode: Code[20])
begin
    // Switch to developer protected FilterGroup 6
    CustLedgEntry.FilterGroup(6);
    CustLedgEntry.SetRange("Global Dimension 1 Code", DeptCode);
    
    // Switch back to default user FilterGroup 0
    CustLedgEntry.FilterGroup(0);
    CustLedgEntry.SetRange("Posting Date", 20260101D, 20261231D);
    
    // The user can freely change the Posting Date filter in the Web Client,
    // but the Global Dimension 1 Code filter remains strictly enforced and invisible.
end;

4. Calculating FlowFields, SIFT Aggregations & Partial Records

FlowFields are dynamic calculation fields defined on tables (such as Customer."Balance (LCY)" or Item."Inventory"). Because FlowField values are not stored physically in table columns, they are not populated by standard record retrieval methods and must be evaluated on demand.

local procedure DemonstrateFlowFieldCalculations()
var
    Customer: Record Customer;
    CustLedgerEntry: Record "Cust. Ledger Entry";
    TotalCustomerBalance: Decimal;
begin
    // 1. Explicit On-Demand Calculation with CalcFields()
    if Customer.Get('10000') then begin
        Customer.CalcFields("Balance (LCY)", "Net Change (LCY)");
        Message('Customer Balance: %1, Net Change: %2',
            Customer."Balance (LCY)", Customer."Net Change (LCY)");
    end;

    // 2. Declarative Auto-Calculation for Recordsets with SetAutoCalcFields()
    Customer.Reset();
    Customer.SetRange("Customer Posting Group", 'DOMESTIC');
    // Automatically evaluates Balance (LCY) in the main SELECT query for each row
    Customer.SetAutoCalcFields("Balance (LCY)");
    if Customer.FindSet() then
        repeat
            TotalCustomerBalance += Customer."Balance (LCY)";
        until Customer.Next() = 0;

    // 3. SIFT Aggregate Calculation with CalcSums()
    CustLedgerEntry.SetRange("Customer No.", '10000');
    CustLedgerEntry.SetRange(Open, true);
    // Executes a single optimized SQL SUM query against SumIndexFields (SIFT)
    CustLedgerEntry.CalcSums("Remaining Amount", "Amount (LCY)");
    Message('Total Open Amount: %1', CustLedgerEntry."Remaining Amount");
end;

CalcFields() vs. SetAutoCalcFields() vs. CalcSums()

Modern Performance Optimization: Partial Records (SetLoadFields)

By default, AL fetches all fields declared on a table and all its active companion tables. In high-volume routines, loading dozens of unneeded columns causes high memory usage and unneeded SQL joins. Partial Records allow developers to explicitly designate which fields to load:

local procedure ProcessCustomerNamesOnly()
var
    Customer: Record Customer;
begin
    Customer.SetLoadFields("No.", Name, "Customer Posting Group");
    if Customer.FindSet() then
        repeat
            // Only No., Name, and Customer Posting Group are fetched over TDS
            ProcessCustomerName(Customer."No.", Customer.Name);
        until Customer.Next() = 0;
end;

• If AL code subsequently accesses an unloaded field (e.g., Customer."Credit Limit (LCY)"), the NST automatically executes an implicit JIT (Just-In-Time) read to fetch the remaining fields, ensuring backward compatibility at the expense of an extra SQL query.