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

Add a FlowField with CalcFormula

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

Reading FlowFields in 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");

Common 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.