Dynamics 365 Business Central: FlowFields (Sum, Average, Exist, Count, Min, Max, Lookup)

Dynamics 365 Business Central

Hi, Readers.
Recently, I found that team members who are new to Business Central do not know FlowFields very well.
As you know, the FlowField fields are used extensively in standard codes. This is a very powerful feature in Business Central and NAV, but it cannot be abused. So this time I want to briefly discuss FlowField in this post.

FlowFields in Business Central

FlowFields display the result of the calculation described in the CalcFormula Property. For example, the Inventory field in the Itemtable shows the current inventory of the item and is calculated as the sum of the Quantity fields for all Item Ledger Entries in this item.

Source Code in Item table:

For some types of FlowField, you can click its value on the page.

The records will be opened.

You can view the filter of the result.

FlowField types

There are seven types of FlowFields. Each is described in the following table.

FlowField typeField typeDescription
SumDecimal, Integer, BigInteger, or DurationThe sum of a specified set in a column in a table.
AverageDecimal, Integer, BigInteger, or DurationThe average value of a specified set in a column in a table.
ExistBooleanIndicates whether any records exist in a specified set in a table.
CountIntegerThe number of records in a specified set in a table.
MinAnyThe minimum value in a column in a specified set in a table.
MaxAnyThe maximum value in a column in a specified set in a table.
LookupAnyLooks up a value in a column in another table.

Examples: Some fields need to be displayed in Design mode.
Sum: Inventory in Item Table

Average: Avg. Estimated Value (LCY) in Salesperson/Purchaser Table

Exist: Comment in Item Table

Count: Bill-To No. of Orders in CustomerTable

Min: Completely Shipped in Sales Header Table

Max: No. of Archived Versions in Sales Header Table

Lookup: Posting Date in Sales LineTable

Image:
Consider the Customer table in the following illustration. This table contains two FlowFields. The field named Any Entries is a FlowField of the Exist type, and the Balance field is a FlowField of the Sum type.

Table that has two FlowFields
https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-flowfields

Code:

field(50100; Balance; Decimal)
{
    CalcFormula = Sum("Cust. Ledger Entry".Amount WHERE("Customer No." = FIELD("No.")));
    Caption = 'Balance';
    Editable = false;
    FieldClass = FlowField;
}
field(50101; "Any Entries"; Boolean)
{
    CalcFormula = Exist("Cust. Ledger Entry" WHERE(Customer No.=FIELD("No.")));
    Caption = 'Any Entries';
    Editable = false;
    FieldClass = FlowField;
}

How to get value from a FlowField field

FlowFields are not physical fields that are stored in the database. They are a description of a calculation and a location for the result to be displayed. Because the information in FlowFields exists only at run time, values in FlowFields are automatically initialized to 0 (zero). To update a FlowField, use the CalcFields Method (Record). If a FlowField is the direct source expression of a control on a page, then the FlowField is automatically calculated when the page is displayed.

For Example: If you forget to use CalcFields to calculate a flowfield (Inventory), the value will be 0.

So you first have to use CALCFIELDS to calculate a flowfield.

Then you can get the value.

You can improve performance by using the SETAUTOCALCFIELDS method before looping through records with FlowFields instead of calling the CALCFIELDS method on each record in the loop.
For example:

Other tips

1. The calculation of FlowFields will take a lot of time in Microsoft Dynamics 365 Business Central, so I recommend creating Flowfields only when really needed. Otherwise, please create physical fields.

2. Please do not display many FlowField fields on the same page, which will also take a lot of time to open. Such as General Ledger Entries, Detailed Customer Ledger Entries, Value Entries and so on.

3. CodeCop Rule AA0232: The FlowField of a table should be indexed.
You can potentially increase performance if fields that are used in FlowFields are added to SumIndexedFields of the corresponding key.
Good code example:

But so far we have not been able to add standard fields to secondary keys by Table Extension. So there are limits to this approach.

Find out more about FlowFields from Microsoft Docs.

End

Hope this will help.

Thanks for your reading.

ZHU

コメント

タイトルとURLをコピーしました