Dynamics 365 Business Central: How to calculate the total (sum) of a column in a table (Four ways)

Hi, Readers.
Today I would like to talk about a very basic but very important topic, how to calculate the total (Sum) of a column in a table.
As you know, calculating totals is an essential function in every system, for example, the system needs to display total amounts and total quantities. In this post, I will briefly introduce 4 ways to calculate totals in BC, hopefully it will give you some help.

As an example, let’s calculate the total Amount (LCY) or Sales (LCY) of Invoice document type in Customer Ledger Entries for 2024.

Amount (LCY): 111,265.92 (Flowfield)

Sales (LCY): 97,628.7 (Normal)

Using AL repetitive statements (Loop)

AL repetitive statements: A repetitive statement is also known as a loop. The following table shows the looping mechanisms in AL.

This is the most common approach, and although it is not the most efficient, it is applicable to any situation. Here is a simple example using repeat – until:

Source code:

pageextension 50100 CustomerLedgerEntriesExt extends "Customer Ledger Entries"
{
    actions
    {
        addfirst(processing)
        {
            action(Summarize)
            {
                ApplicationArea = All;
                Caption = 'Summarize';
                Promoted = true;
                PromotedCategory = Process;
                Image = CalculateBalanceAccount;
                trigger OnAction()
                var
                    CustLedgerEntry: Record "Cust. Ledger Entry";
                    TotalAmount: Decimal;
                begin
                    TotalAmount := 0;
                    CustLedgerEntry.Reset();
                    CustLedgerEntry.SetRange("Posting Date", 20240101D, 20241231D);
                    CustLedgerEntry.SetRange("Document Type", CustLedgerEntry."Document Type"::Invoice);
                    CustLedgerEntry.SetAutoCalcFields("Amount (LCY)");
                    if CustLedgerEntry.FindSet() then
                        repeat
                            TotalAmount += CustLedgerEntry."Amount (LCY)";
                        until CustLedgerEntry.Next() = 0;
                    Message('Total amount for invoices in 2024: %1', TotalAmount);
                end;
            }
        }
    }
}

Using Record.CalcSums Method

Record.CalcSums(Any [, Any,…]) Method: Calculates the total of a column in a table. You specify which fields to calculate by using parameters.

The CalcSums function is used to calculate a total for a specific field, based on the filters of the dataset.

And this method is most efficient if the SumIndexFields Property is set in the key and the filter conditions are consistent with the fields defined in the key. (Not required)

SumIndexFields Property: Specify which fields should be the “aggregation fields” in a SumIndexField Technology (SIFT) index, if applicable.

For CalcSums, a key that contains the SumIndexFields must be selected as the current key. Similar to CalcFields, CalcSums uses the current filter settings when it performs the calculation.

In the following example, an appropriate key is selected, some filters are set, the calculation is performed and then a message is displayed.

But please note that this method does not support Flowfield.

Argument 1: The argument 1 of field class FlowField is not supported. Allowed field class types are ‘Normal’. AL AL0827

Let’s go back to this example.

Very simple.

Source code:

pageextension 50100 CustomerLedgerEntriesExt extends "Customer Ledger Entries"
{
    actions
    {
        addfirst(processing)
        {
            action(Summarize)
            {
                ApplicationArea = All;
                Caption = 'Summarize';
                Promoted = true;
                PromotedCategory = Process;
                Image = CalculateBalanceAccount;
                trigger OnAction()
                var
                    CustLedgerEntry: Record "Cust. Ledger Entry";
                    TotalAmount: Decimal;
                begin
                    TotalAmount := 0;
                    CustLedgerEntry.Reset();
                    CustLedgerEntry.SetRange("Posting Date", 20240101D, 20241231D);
                    CustLedgerEntry.SetRange("Document Type", CustLedgerEntry."Document Type"::Invoice);
                    CustLedgerEntry.CalcSums("Sales (LCY)");
                    Message('Total Sales (LCY) for 2024: %1', CustLedgerEntry."Sales (LCY)");
                end;
            }
        }
    }
}

Using Query Object

Business Central query objects enable you to retrieve records from one or more tables and then combine the data into rows and columns in a single dataset. Query objects can also perform calculations on data, such finding the sum or average of all values in a column of the dataset. More details: Query object

We can use Method Property to filter dates or sum data.

Let’s look at a simple example

Open directly in BC UI:
PS: Dynamics 365 Business Central: How to make a query searchable (Add Query to Tell me)

Read the value in the Query from the AL code:

Great.

If frequent calculations are required, this method is recommended. The only disadvantage of this method is that it requires the creation of an additional Query Object. More details: Query objects and performance

Source code:

pageextension 50100 CustomerLedgerEntriesExt extends "Customer Ledger Entries"
{
    actions
    {
        addfirst(processing)
        {
            action(Summarize)
            {
                ApplicationArea = All;
                Caption = 'Summarize';
                Promoted = true;
                PromotedCategory = Process;
                Image = CalculateBalanceAccount;
                trigger OnAction()
                var
                    CustLedgerEntriesGroupByYear: Query CustLedgerEntriesGroupByYear;
                    TotalAmount: Decimal;
                    TotalSales: Decimal;
                begin
                    TotalAmount := 0;
                    CustLedgerEntriesGroupByYear.SetFilter(Posting_Date_Year, '2024');
                    if CustLedgerEntriesGroupByYear.Open() then
                        while CustLedgerEntriesGroupByYear.Read() do begin
                            TotalAmount := CustLedgerEntriesGroupByYear.Amount__LCY_;
                            TotalSales := CustLedgerEntriesGroupByYear.Sales__LCY_;
                        end;
                    Message('Total Amount (LCY) for 2024: %1\Total Sales (LCY) for 2024: %2', TotalAmount, TotalSales);
                    CustLedgerEntriesGroupByYear.Close();
                end;
            }
        }
    }
}

query 50102 CustLedgerEntriesGroupByYear
{
    QueryType = Normal;
    Caption = 'Customer Ledger Entries Group By Year';

    elements
    {
        dataitem(Cust__Ledger_Entry; "Cust. Ledger Entry")
        {
            DataItemTableFilter = "Document Type" = const("Document Type"::"Invoice");

            column(Posting_Date_Year; "Posting Date")
            {
                Caption = 'Year';
                Method = Year;
            }
            column(Amount__LCY_; "Amount (LCY)")
            {
                Caption = 'Amount (LCY)';
                Method = Sum;
            }
            column(Sales__LCY_; "Sales (LCY)")
            {
                Caption = 'Sales (LCY)';
                Method = Sum;
            }
        }
    }
}

More details: Dynamics 365 Business Central: Using the Date Method to retrieve only the Day, Month, and Year Data in Queries [Group by Year-month]

Using Flowfields

FlowFields display the result of the calculation described in the CalcFormula Property. For example, the Inventory field in the Item table 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.

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

This is very useful for displaying totals on a page, as there is no need to add additional logic, just the properties.

PS: Business Central 2025 wave 1 (BC26): Calculate values only for visible FlowFields

However, when you want to calculate the total using this method, you need to create a new field in an existing table or in a new table. Let’s look at a simple example. First, we use Flowfield to sum up the scattered data.
PS: Dynamics 365 Business Central: A flow field is part of the query column list, this is not supported (Flowfield based on Flowfield)

And then sum up the minority data. (Maybe not a very good example)

Source code:

pageextension 50100 CustomerLedgerEntriesExt extends "Customer Ledger Entries"
{
    actions
    {
        addfirst(processing)
        {
            action(Summarize)
            {
                ApplicationArea = All;
                Caption = 'Summarize';
                Promoted = true;
                PromotedCategory = Process;
                Image = CalculateBalanceAccount;
                trigger OnAction()
                var
                    TotalSales: Decimal;
                    Cust: Record Customer;
                begin
                    TotalSales := 0;
                    Cust.Reset();
                    Cust.SetAutoCalcFields("Total Sales (LCY) 2024");
                    if Cust.FindSet() then
                        repeat
                            TotalSales += Cust."Total Sales (LCY) 2024";
                        until Cust.Next() = 0;
                    Message('Total Sales (LCY) 2024: %1', TotalSales);
                end;
            }
        }
    }
}

pageextension 50112 CustomerListExt extends "Customer List"
{
    layout
    {
        addafter(Name)
        {
            field("Total Sales (LCY) 2024"; Rec."Total Sales (LCY) 2024")
            {
                ApplicationArea = All;
                Caption = 'Total Sales (LCY) 2024';
                ToolTip = 'Specifies the total sales in local currency for the year 2024.';
            }
        }
    }
}
tableextension 50112 CustomerExt extends Customer
{
    fields
    {
        field(50100; "Total Sales (LCY) 2024"; Decimal)
        {
            Caption = 'Total Sales (LCY) 2024';
            FieldClass = FlowField;
            CalcFormula = sum("Cust. Ledger Entry"."Sales (LCY)" where("Customer No." = field("No."),
                                                                        "Document Type" = filter("Gen. Journal Document Type"::Invoice),
                                                                        "Posting Date" = filter('2024-01-01' .. '2024-12-31')));
            ToolTip = 'Specifies the year of the posting date.';
        }
    }
}

Very simple, give it a try!!!😁

END

Hope this will help.

Thanks for reading.

ZHU