Dynamics 365 Business Central: How to calculate and display the total amount or quantity of selected lines on the list page

Hi, Readers.
Last week, someone asked me a question, is it possible to calculate and display the total amount or quantity of selected lines on the list page like in Excel.
For example, Display Average, Count, Sum of the selected lines.

This’s an interesting question. So in this post, I will share a solution for reference only, hope this will help.

As you might know, since there is no selected trigger in Business Central, we can’t do exactly the same as in Excel. We have to add a new action.

Then the next question is where the data is displayed.
There are two cases in the standard function of BC, one is the footer below the Worksheet type page, which can display the total balance. The second is to use the Factbox on the page.

If you need to add this feature to an existing list page, I recommend the second one.

Let’s try to do it.

For example:
Users’ requirements: Need to be able to calculate and display the count, average of the total amount, total amount, and total quantity of selected lines on the Sales Lines page.

1. Create a new table for Factbox.

table 50104 TotalSalesLines
{
    DataClassification = CustomerContent;
    Caption = 'Total Sales Lines';

    fields
    {
        field(1; Number; Integer)
        {
            Caption = 'Number';
            DataClassification = CustomerContent;
        }
        field(2; LinesConut; Integer)
        {
            Caption = 'Line Count';
            DataClassification = CustomerContent;
        }
        field(3; LinesAverage; Decimal)
        {
            Caption = 'Average';
            DecimalPlaces = 0 : 5;
        }
        field(15; Quantity; Decimal)
        {
            Caption = 'Quantity';
            DecimalPlaces = 0 : 5;
        }
        field(29; Amount; Decimal)
        {
            Caption = 'Amount';
            Editable = false;
        }
    }

    keys
    {
        key(PK; Number)
        {
            Clustered = true;
        }
    }
}

2. Create a new CardPart page to display the data on the list page.

page 50103 "Selected Sales Lines Total"
{
    PageType = CardPart;
    Editable = false;
    SourceTable = TotalSalesLines;

    layout
    {
        area(Content)
        {
            field(LinesConut; Rec.LinesConut)
            {
                Caption = 'Count';
                ApplicationArea = All;
            }
            field(LinesAverage; Rec.LinesAverage)
            {
                Caption = 'Average';
                ApplicationArea = All;
            }
            field(Amount; Rec.Amount)
            {
                Caption = 'Total Amount';
                ApplicationArea = All;
            }

            field(Quantity; Rec.Quantity)
            {
                Caption = 'Total Quantity';
                ApplicationArea = All;
            }
        }
    }

    procedure SetTotals(var NewCount: Integer; NewAverage: Decimal; NewTotalAmount: Decimal; NewTotalQuantity: Decimal)
    begin
        Rec.Reset();
        Rec.DeleteAll();
        Rec.Init();
        Rec.Number := 1;
        Rec.LinesConut := NewCount;
        Rec.LinesAverage := NewAverage;
        Rec.Amount := NewTotalAmount;
        Rec.Quantity := NewTotalQuantity;
        Rec.Insert();
    end;

}

3. Create a pageextension to add the new Factbox and actions.

pageextension 50104 SalesLinesPageExt extends "Sales Lines"
{
    layout
    {
        addfirst(FactBoxes)
        {
            part(SelectedSalesLinesTotal; "Selected Sales Lines Total")
            {
                Caption = 'Selected Sales Lines Total';
                ApplicationArea = All;

                SubPageLink = Number = const(1);
            }
        }
    }

    actions
    {
        addbefore("Show Document")
        {
            action("Calculate Total")
            {
                Caption = 'Calculate Total';
                ApplicationArea = All;
                Promoted = true;
                PromotedIsBig = true;
                PromotedCategory = Process;
                Image = Calculate;

                trigger OnAction()
                var
                    SelectedSalesLines: Record "Sales Line";
                    TotalAmount: Decimal;
                    TotalQuantity: Decimal;
                    LinesCount: Integer;
                    LinesAverage: Decimal;
                begin
                    SelectedSalesLines.Reset();
                    LinesCount := 0;
                    LinesAverage := 0;
                    TotalAmount := 0;
                    TotalQuantity := 0;
                    CurrPage.SetSelectionFilter(SelectedSalesLines);
                    if SelectedSalesLines.FindSet() then
                        repeat
                            LinesCount += 1;
                            TotalAmount += SelectedSalesLines.Amount;
                            TotalQuantity += SelectedSalesLines.Quantity;
                        until SelectedSalesLines.Next() = 0;
                    if LinesCount > 0 then begin
                        LinesAverage := TotalAmount / LinesCount;
                        CurrPage.SelectedSalesLinesTotal.Page.SetTotals(LinesCount, LinesAverage, TotalAmount, TotalQuantity);
                        CurrPage.SelectedSalesLinesTotal.Page.Update();
                    end;
                end;
            }
            action("Clear Total")
            {
                Caption = 'Clear Total';
                ApplicationArea = All;
                Promoted = true;
                PromotedIsBig = true;
                PromotedCategory = Process;
                Image = ClearLog;

                trigger OnAction()
                begin
                    ClearTotal();
                end;
            }
        }
    }

    trigger OnOpenPage()
    begin
        ClearTotal();
    end;

    local procedure ClearTotal()
    var
        TotalSalesLines: Record TotalSalesLines;
    begin
        TotalSalesLines.Reset();
        TotalSalesLines.DeleteAll();
    end;
}

Publish and test.

Test Video:

END

Hope this will help.

Thanks for reading.

ZHU