# CalcFormula in Business Central: FlowFields With Sum, Count, Exist and Lookup

> Source: https://netray.co/erp-help/calcformula-in-business-central-flowfield-sum-lookup-count-exist
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

CalcFormula defines how a FlowField is calculated from other tables. A FlowField is not stored. It is computed on demand using Sum, Average, Min, Max, Count, Exist or Lookup over a source table filtered by conditions. Read it with CalcFields or SetAutoCalcFields, and keep it fast with a key that supports the filter and sum.

## Kind

how-to

## System

dynamics

## Erp

Microsoft Dynamics 365 Business Central

## Module

AL development / FlowFields

## Query

calcformula in business central

## Alternate Queries

- CalcFormula AL syntax
- business central flowfield sum
- business central flowfield not calculating
- CalcFields vs SetAutoCalcFields

## Applies To

Business Central online and on-premises, AL table and table extension fields.

## Steps

### Add a FlowField with CalcFormula

**Items:**

- Add a field to a table or table extension and set FieldClass = FlowField.
- Write CalcFormula with the method and source table, such as sum("Cust. Ledger Entry".Amount where("Customer No." = field("No."))).
- Add filter conditions with where(). Use field() to refer to the current record, const() for fixed values and filter() for patterns.
- Optionally add a FlowFilter field (FieldClass = FlowFilter) so users or code can restrict the calculation, for example by date.
- Make sure the source table has a key starting with the filter fields and, for sums, includes the summed field in SumIndexFields.
- In code, call Rec.CalcFields(FieldName) before reading, or SetAutoCalcFields on the record variable for loops. Pages calculate FlowFields they display automatically.
- Remember the field is read-only and has no stored value, so it cannot be set, sorted or filtered cheaply like a normal field.

## Sections

### The seven methods

**Body:**

Sum adds a decimal field, Average averages it, Min and Max return the lowest and highest value of a field, Count returns the number of matching records, Exist returns a Boolean whether any record matches, and Lookup returns a single field value from the first matching record. Sum, Average, Min, Max operate on numeric fields. Lookup can return text, code or any other single field.

Each formula refers to exactly one source table. You cannot join two tables or reference another FlowField in the where clause. If you need that, calculate it in code or build a query object.

**Code:**

field(50100; "Posted Balance"; Decimal)
{
    FieldClass = FlowField;
    CalcFormula = sum("Cust. Ledger Entry"."Remaining Amt. (LCY)" where("Customer No." = field("No.")));
    Editable = false;
}

field(50101; "Has Open Orders"; Boolean)
{
    FieldClass = FlowField;
    CalcFormula = exist("Sales Header" where("Sell-to Customer No." = field("No."),
                                             "Document Type" = const(Order)));
}

field(50102; "Order Count"; Integer)
{
    FieldClass = FlowField;
    CalcFormula = count("Sales Header" where("Sell-to Customer No." = field("No.")));
}

field(50103; "Salesperson Name"; Text[50])
{
    FieldClass = FlowField;
    CalcFormula = lookup("Salesperson/Purchaser".Name where(Code = field("Salesperson Code")));
}

### Reading FlowFields in code

**Body:**

A FlowField has no value until it is calculated. In code call CalcFields on the record before you read it, passing the field names. Looping over many records with CalcFields in the body issues a calculation per record, while SetAutoCalcFields on the variable makes each fetched record come with the field calculated, which is the usual pattern for loops.

CalcFields respects FlowFilters that are set on the record variable. That is how a customer list shows a balance for a date range: set the date FlowFilter, then calculate. A calculated value is only in memory, so Modify does not save it, and filtering or sorting the list by a FlowField can be slow on big tables.

**Code:**

Customer.SetAutoCalcFields("Posted Balance");
if Customer.FindSet() then
    repeat
        Total += Customer."Posted Balance";
    until Customer.Next() = 0;

Customer.SetRange("Date Filter", 0D, WorkDate());
Customer.CalcFields("Posted Balance");

### Performance: keys and SIFT

**Body:**

A sum over a large ledger is only fast if the platform can answer it from a pre-aggregated index. Define a key on the source table that starts with the fields in your where clause and lists the summed field in SumIndexFields. The system maintains these SIFT structures (as indexed views on SQL Server), so reads are cheap but every insert pays a maintenance cost.

Do not add SumIndexFields keys everywhere. Every extra one slows posting on high-volume tables such as ledger entries. Add them where the FlowField is used in lists or reports that matter, and check that they are enabled. For one-off reports on big data, a query object or a separate summary table may be cheaper than a FlowField on a hot table.

**Code:**

- key(Key50100; "Customer No.", "Posting Date")
{
    SumIndexFields = "Remaining Amt. (LCY)";
}

## Pitfalls

- Reading a FlowField without CalcFields, getting zero or blank because it was never calculated.
- Calling CalcFields inside a loop over thousands of records instead of using SetAutoCalcFields.
- Adding a FlowField over a large ledger table without a supporting key, causing slow lists.
- Expecting Modify to store a FlowField value. FlowFields are never persisted.
- Filtering or sorting a page by a FlowField on a big table. It forces a calculation for every row.
- Trying to reference one FlowField from another's where clause. It is not supported.

## Ai Angle

An assistant that reads your tables and keys can review a FlowField definition against the available keys, point out where a SumIndexFields key is missing, and draft the AL for the field. Run in a sandbox, it can also compare list load times before and after. A developer stays responsible for the posting-performance cost of any new key.

## Faq

### What is a FlowField in Business Central?

**Answer:** A FlowField is a table field whose value is computed from other tables using CalcFormula when it is displayed or when CalcFields is called. It is never stored in the table, so it is always current but costs a calculation each time it is read.

### What is the difference between CalcFields and SetAutoCalcFields?

**Answer:** CalcFields calculates the listed fields on the current record when you call it. SetAutoCalcFields tells a record variable to calculate the listed FlowFields automatically for each record it fetches, which is better in loops because you do not call CalcFields per record.

### Why is my FlowField zero?

**Answer:** Usually it was not calculated in code, a FlowFilter such as the date filter excludes the data, the where clause does not match any records, or the field refers to the wrong source field. Check the formula, the filters on the record and whether you called CalcFields.

### Can CalcFormula use more than one table?

**Answer:** No. A formula has one source table. For joined data use code, a Query object or a summary table that you maintain, and keep the FlowField for single table aggregates.

## Search terms

calcformula in business central, business central flowfield, CalcFormula sum AL, CalcFields vs SetAutoCalcFields, SumIndexFields SIFT, FlowFilter business central, business central lookup calcformula, business central count exist calcformula

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json