ExamFinch.com My Study

Study guide

4.3: FlowFields, FlowFilters & Table Relations

Practice this exam

Microsoft Dynamics 365 Business Central Developer MB-820

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Business Central Architecture & Platform Foundation

1.1 Business Central Architecture & Extensions Model 1.2 Customization Lifecycle: Cloud (SaaS) vs. On-Premises 1.3 Base Application, System Application & AppSource

2 Chapter 2: Development Environment & Configuration

2.1: Development Environment Setup: VS Code, AL: Go! Projects & Sandboxes 2.2: AL Configuration Files: app.json & launch.json Parameters 2.3: Multi-Root Workspaces & Project Dependencies

3 Chapter 3: Extension Lifecycle: Debugging, Deployment, Multilanguage & Maintenance

3.1 Debugging & Snapshot Debugging in Visual Studio Code 3.2 Extension Deployment & Schema Synchronization Modes 3.3 Multilanguage Development with XLIFF & Translation Files 3.4 Extension Maintenance, Deprecation & Breaking Changes

4 Chapter 4: Tables, Table Extensions & Data Modeling

4.1: Building Tables, Fields, Keys & Data Types 4.2: Table Extensions & Table Modifications 4.3: FlowFields, FlowFilters & Table Relations 4.4: Enums, Enum Extensions & Extensible Types

5 Chapter 5: Pages, Page Extensions & Role Centers

5.1: Page Types, Anatomy & Control Structure 5.2: Building Card, List & Document Pages 5.3: Page Extensions & Page Customizations 5.4: Role Centers, Headlines & Cue Data Modeling

6 Chapter 6: Reports & Document Generation

6.1 Report Architecture, DataItems & Dataset Design 6.2 Report Layouts: RDLC, Word, Excel & Multilanguage 6.3 Document Reports & Request Page Programming 6.4 Processing-Only Reports & Report Substitutions

7 Chapter 7: XMLports & Data Exchange

7.1 XMLport Architecture, Formats & Properties 7.2 XMLport Node Hierarchy, Triggers & Stream Manipulation

8 Chapter 8: Codeunits, Events, Triggers & Interfaces

8.1 Codeunit Types, Execution & SingleInstance Codeunits 8.2 Events, Publishers, Subscribers & Event Isolation 8.3 Interfaces, Polymorphism & Dynamic Codeunit Invocation 8.4 Installation & Upgrade Codeunits, Data Transfer & Archive

9 Chapter 9: Entitlements, Permissions & Queries

9.1 Permission Sets & Permission Set Extensions 9.2 Inherent Permissions, Inherent Entitlements & Security Scoping 9.3 Query Objects: Joins, Aggregation & Query as Report Data Source

10 Chapter 10: UI Customization, User Assistance & Onboarding

10.1 Profile Objects & Custom Page Views in AL 10.2 User Assistance: ToolTips, Teaching Tips & In-App Tours 10.3 Assisted Setup, Guided Experience & Onboarding Checklists

11 Chapter 11: Business Central Design Patterns & Data Process Model

11.1 Functional Table Types: Master Data, Supplemental & Setup Patterns 11.2 Document Standards: Header, Line, History & Journal Architectures 11.3 Data Process Model & Posting Routine Architecture

12 Chapter 12: AL Language Fundamentals, Data Types & Variables

12.1 AL Syntax, Variable Scope, Primitive & Complex Data Types 12.2 Expressions, Control Flow Statements & Built-in Functions 12.3 Access Modifiers (Public, Internal, Local) & Code Scoping

13 Chapter 13: Data Manipulation, Streams & Error Handling

13.1 Record Operations: Get, FindSet, ModifyAll & Filter Functions 13.2 Streams, File Management & TempBlob 13.3 Error Handling: ErrorInfo, TryFunctions & Collectible Errors

14 Chapter 14: Testing, Test Toolkit & Automation

14.1 Testing Architecture & the Test Toolkit 14.2 Authoring Test Codeunits & UI Handler Functions

15 Chapter 15: Telemetry, Performance Optimization & Analyzers

15.1 Telemetry Configuration & Azure Application Insights 15.2 Custom Telemetry Signals with Session.LogMessage & KQL 15.3 Performance Profiler, SetLoadFields & Query Optimization 15.4 AL Analyzers: CodeCop, AppSourceCop, UICop & PTECop

16 Chapter 16: REST Web Services & JSON in AL

16.1 HttpClient, HttpRequestMessage & REST API Consumption 16.2 JSON Processing: JsonObject, JsonArray, JsonValue & JsonToken

17 Chapter 17: API Pages, OData, Read Scale-Out & OAuth

17.1 API Pages (PageType=API), OData Web Services & REST Endpoints 17.2 Bound and Unbound OData Actions with [ServiceEnabled] 17.3 Read Scale-Out Architecture & DataAccessIntent Configuration 17.4 Microsoft Entra ID App Registrations & OAuth 2.0 Integration

4.3: FlowFields, FlowFilters & Table Relations

Key Takeaways Explain This Topic AI

FlowFields are virtual, dynamically calculated fields (e.g., Sum, Count, Exist) that do not store persistent data in SQL tables.

SIFT (SumIndexFields Technology) powers Sum and Average FlowFields by maintaining SQL Server Indexed Views for rapid O(1) aggregation.

FlowFilters are in-memory filter variables (FieldClass = FlowFilter) used to pass dynamic runtime filter parameters into FlowField calculation formulas.

In AL code, FlowFields evaluate to zero or empty until explicitly populated using Rec.CalcFields() or SetAutoCalcFields() on the record variable.

Conditional Table Relations allow a single field to dynamically reference different target tables based on the value of a discriminator field.

Last updated: August 2026

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

Method Description Example CalcFormula

Sum Calculates the mathematical sum of a numeric field in a target table. Backed by SIFT. Sum("Cust. Ledger Entry"."Amount (LCY)" WHERE("Customer No." = FIELD("No."), "Open" = CONST(true)))

Average Calculates the arithmetic mean of a numeric field in a target table. Backed by SIFT. Average("Item Ledger Entry"."Unit Cost" WHERE("Item No." = FIELD("No.")))

Count Counts the total number of matching records in the target table. Count("Sales Header" WHERE("Document Type" = CONST(Order), "Sell-to Customer No." = FIELD("No.")))

Exist Returns a Boolean true if at least one matching record exists; otherwise false . Highly optimized in SQL. Exist("Sales Line" WHERE("Document Type" = FIELD("Document Type"), "Document No." = FIELD("Document No."), "Type" = CONST(Item)))

Min Finds the minimum value of a specified field among matching records. Min("Sales Line"."Planned Delivery Date" WHERE("Document No." = FIELD("No.")))

Max Finds the maximum value of a specified field among matching records. Max("Cust. Ledger Entry"."Posting Date" WHERE("Customer No." = FIELD("No.")))

Lookup Retrieves the value of a single field from another related table record. Lookup(Contact.Name WHERE("No." = FIELD("Primary Contact No.")))

  • 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));
  • }
  • }
  • }

Loading diagram... FlowField Calculation and SIFT Index Resolution

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

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)";
  • }

SQL Server creates an Indexed View (materialized aggregated view) maintained automatically by the database engine on every insert, modify, and delete.

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 ) O(1) 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 '' ).

Rec.CalcFields(Field1, Field2, ...) : Calculates the specified FlowFields for the current single record instance.

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

  • Simple Table Relation: field(5; "Customer No."; Code[20])
  • {
  • TableRelation = Customer."No.";
  • }
  • Filtered Table Relation: field(6; "Active Project Code"; Code[20])
  • {
  • TableRelation = Job."No." WHERE("Status" = CONST(Open), "Blocked" = CONST(" "));
  • }

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.";
  • }

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."));
  • }