4.3: FlowFields, FlowFilters & Table Relations

4.3 FlowFields, FlowFilters & Table Relations

Business Central features a powerful declarative data calculation engine centered around FlowFields, FlowFilters, and Table Relations. FlowFields eliminate the need to store redundant aggregate values (such as account balances, inventory on hand, or total open orders) in database tables, ensuring real-time calculation and preventing data synchronization anomalies.

1. FlowFields Architecture & Calculation Formulas

A FlowField is a virtual, calculated field defined on a table object. It has FieldClass = FlowField and is always non-editable (Editable = false). FlowFields do not occupy physical storage column space in SQL Server tables; instead, their values are calculated dynamically using a CalcFormula.

The Seven FlowField Calculation Methods

Table Declaration with FlowFields

table 50110 "Customer Reward Summary"
{
    DataClassification = CustomerContent;

    fields
    {
        field(1; "Customer No."; Code[20])
        {
            Caption = 'Customer No.';
            TableRelation = Customer."No.";
        }
        field(10; "Total Sales (LCY)"; Decimal)
        {
            Caption = 'Total Sales (LCY)';
            FieldClass = FlowField;
            CalcFormula = Sum("Cust. Ledger Entry"."Sales (LCY)" WHERE(
                "Customer No." = FIELD("Customer No."),
                "Posting Date" = FIELD("Date Filter"),
                "Global Dimension 1 Code" = FIELD("Global Dimension 1 Filter")));
            Editable = false;
        }
        field(11; "Has Open Orders"; Boolean)
        {
            Caption = 'Has Open Orders';
            FieldClass = FlowField;
            CalcFormula = Exist("Sales Header" WHERE(
                "Document Type" = CONST(Order),
                "Sell-to Customer No." = FIELD("Customer No."),
                "Status" = CONST(Released)));
            Editable = false;
        }
        // FlowFilter definitions
        field(20; "Date Filter"; Date)
        {
            Caption = 'Date Filter';
            FieldClass = FlowFilter;
        }
        field(21; "Global Dimension 1 Filter"; Code[20])
        {
            Caption = 'Global Dimension 1 Filter';
            FieldClass = FlowFilter;
            TableRelation = "Dimension Value"."Code" WHERE("Global Dimension No." = CONST(1));
        }
    }
}

2. SIFT (SumIndexFields Technology) & Performance

Calculating sums and averages across ledger tables with millions of rows would cause severe performance bottlenecks if SQL Server had to scan individual table rows on every request. Business Central solves this using SumIndexFields Technology (SIFT).

How SIFT Works in SQL Server

1. On the source table (e.g., Cust. Ledger Entry), a secondary key is defined with numeric fields listed in the SumIndexFields property:

   key(CustomerSales; "Customer No.", "Posting Date")
   {
       SumIndexFields = "Sales (LCY)", "Amount (LCY)";
   }
   

2. SQL Server creates an Indexed View (materialized aggregated view) maintained automatically by the database engine on every insert, modify, and delete.
3. When a FlowField executes a Sum formula matching the key structure, SQL Server queries the pre-computed indexed view directly, returning the aggregated result in $O(1)$ constant time.

Exam Watch: SIFT indexed views speed up reads but incur a minor write overhead on inserts and updates. Always align FlowField WHERE filters with the exact order of fields in the source table's SIFT key.

3. FlowFilters: Dynamic Calculation Filtering

A FlowFilter is a special field defined with FieldClass = FlowFilter. FlowFilters are never stored in the database; they exist purely as in-memory filter placeholders within the record buffer.

Passing Filters to FlowFields

When a FlowField CalcFormula references a FlowFilter using FIELD("Date Filter"), the FlowField dynamically incorporates whatever filter is currently applied to that FlowFilter on the record instance.

codeunit 50120 "Reward Calculator"
{
    procedure GetYearToDateSales(CustomerNo: Code[20]): Decimal
    var
        Cust: Record Customer;
    begin
        Cust.Get(CustomerNo);
        // Apply date range to the FlowFilter
        Cust.SetFilter("Date Filter", '%1..%2', CalcDate('<-CY>', WorkDate()), WorkDate());
        // Apply global dimension filter
        Cust.SetRange("Global Dimension 1 Filter", 'RETAIL');
        // Calculate the FlowField
        Cust.CalcFields("Sales (LCY)");
        exit(Cust."Sales (LCY)");
    end;
}

FlowFields in AL: CalcFields vs SetAutoCalcFields

By default, when you retrieve a record via Get(), FindFirst(), or FindSet(), all FlowField values are blank (0, false, or '').

1. Rec.CalcFields(Field1, Field2, ...): Calculates the specified FlowFields for the current single record instance.
2. Rec.SetAutoCalcFields(Field1, Field2): Configures the record variable to automatically calculate the specified FlowFields in the initial SQL query during FindSet(). This combines record retrieval and FlowField calculation into a single round-trip, eliminating the N+1 query problem during dataset iterations.

4. Advanced Table Relations

The TableRelation property establishes foreign key relationships, enables lookup drill-downs, and validates referential integrity.

Types of Table Relations

1. Simple Table Relation:

   field(5; "Customer No."; Code[20])
   {
       TableRelation = Customer."No.";
   }
   

2. Filtered Table Relation:

   field(6; "Active Project Code"; Code[20])
   {
       TableRelation = Job."No." WHERE("Status" = CONST(Open), "Blocked" = CONST(" "));
   }
   

3. Conditional Table Relation: Links to different target tables depending on the value of a discriminator field in the same table:

   field(10; "Source Type"; Enum "Payment Source Type") { }
   field(11; "Source No."; Code[20])
   {
       TableRelation = if ("Source Type" = const(Customer)) Customer."No."
                       else if ("Source Type" = const(Vendor)) Vendor."No."
                       else if ("Source Type" = const("Bank Account")) "Bank Account"."No."
                       else if ("Source Type" = const("Fixed Asset")) "Fixed Asset"."No.";
   }
   

4. Composite / Multi-Segment Table Relation: Matches multiple fields across composite primary keys:

   field(20; "Sales Line No."; Integer)
   {
       TableRelation = "Sales Line"."Line No." WHERE(
           "Document Type" = FIELD("Document Type"),
           "Document No." = FIELD("Document No."));
   }