Skip to main content

Notifications

Announcements

No record found.

Small and medium business | Business Central, N...
Suggested answer

How to make selected sale lines to calculate

(0) ShareShare
ReportReport
Posted on by 15

Hello,

Find super good extension - https://yzhums.com/11138/

think hmm it would be super nice if i cound do the same but in every sale order and calculate weight so, my journey has started... 

tried tried and something i did that debugger goes without error, but... when selected sale order lines in sales order and press calculate getting error "The table IDs do not match."

used this code

[code]

table 50105 TotalSalesOrderLines
{
DataClassification = CustomerContent;
Caption = 'Total Sales Order Lines';
fields
{
field(1; Number; Integer)
{
Caption = 'Number';
DataClassification = CustomerContent;
}
field(2; LineCount; 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;
}
field(30; TotalWeight; Decimal)
{
Caption = 'Total Weight';
Editable = false;
}
}
keys
{
key(PK; Number)
{
Clustered = true;
}
}
}

page 50106 "Sales Order Lines Total"
{
PageType = CardPart;
Editable = false;
SourceTable = TotalSalesOrderLines;
layout
{
area(Content)
{
field(LineCount; Rec.LineCount)
{
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;
}
field(TotalWeight; Rec.TotalWeight)
{
Caption = 'Total Weight';
ApplicationArea = All;
}
}
}
procedure SetTotals(var NewLineCount: Integer; NewAverage: Decimal; NewTotalAmount: Decimal; NewTotalQuantity: Decimal; NewTotalWeight: Decimal)
begin
Rec.Reset();
Rec.DeleteAll();
Rec.Init();
Rec.Number := 1;
Rec.LineCount := NewLineCount;
Rec.LinesAverage := NewAverage;
Rec.Amount := NewTotalAmount;
Rec.Quantity := NewTotalQuantity;
Rec.TotalWeight := NewTotalWeight;
Rec.Insert();
end;
}

pageextension 50103 SalesOrderPageExt extends "Sales Order"
{
layout
{
addfirst(FactBoxes)
{
part(SelectedSalesOrderLinesTotal; "Sales Order Lines Total")
{
Caption = 'Sales Order Lines Total';
ApplicationArea = All;
SubPageLink = Number = const(2);
}
}
}
actions
{
addbefore("F&unctions")
{
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;
TotalWeight: Decimal;
LinesCount: Integer;
LinesAverage: Decimal;
LineWeight: Decimal;
begin
SelectedSalesLines.Reset();
LinesCount := 0;
LinesAverage := 0;
TotalAmount := 0;
TotalQuantity := 0;
TotalWeight := 0;
CurrPage.SetSelectionFilter(SelectedSalesLines);
if SelectedSalesLines.FindSet() then begin
repeat
LinesCount += 1;
TotalAmount += SelectedSalesLines.Amount;
TotalQuantity += SelectedSalesLines.Quantity;
LineWeight := SelectedSalesLines."Net Weight" * SelectedSalesLines.Quantity;
TotalWeight += LineWeight;
until SelectedSalesLines.Next() = 0;
end;
if LinesCount > 0 then begin
LinesAverage := TotalAmount / LinesCount;
CurrPage.SelectedSalesOrderLinesTotal.Page.SetTotals(LinesCount, LinesAverage, TotalAmount, TotalQuantity, TotalWeight);
CurrPage.SelectedSalesOrderLinesTotal.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
TotalSalesOrderLines: Record TotalSalesOrderLines;
begin
TotalSalesOrderLines.Reset();
TotalSalesOrderLines.DeleteAll();
end;
}

[/code]

Tried something else but nowstill not working

[code]

table 50105 TotalSalesOrderLines
{
    DataClassification = CustomerContent;
    Caption = 'Total Sales Order Lines';
    fields
    {
        field(1; Number; Integer)
        {
            Caption = 'Number';
            DataClassification = CustomerContent;
        }
        field(2; LineCount; 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;
        }
        field(30; TotalWeight; Decimal)
        {
            Caption = 'Total Weight';
            Editable = false;
        }
    }
    keys
    {
        key(PK; Number)
        {
            Clustered = true;
        }
    }
}

page 50106 "Sales Order Lines Total"
{
    PageType = CardPart;
    Editable = false;
    SourceTable = TotalSalesOrderLines;
    layout
    {
        area(Content)
        {
            field(LineCount; Rec.LineCount)
            {
                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;
            }
            field(TotalWeight; Rec.TotalWeight)
            {
                Caption = 'Total Weight';
                ApplicationArea = All;
            }
        }
    }
    trigger OnAfterGetCurrRecord()
    begin
        if Rec.IsEmpty() then begin
            Rec.Init();
            Rec.Insert();
        end;
    end;

    procedure SetTotals(var NewLineCount: Integer; NewAverage: Decimal; NewTotalAmount: Decimal; NewTotalQuantity: Decimal; NewTotalWeight: Decimal)
    begin
        Rec.Reset();
        Rec.DeleteAll();
        Rec.Init();
        Rec.Number := 1;
        Rec.LineCount := NewLineCount;
        Rec.LinesAverage := NewAverage;
        Rec.Amount := NewTotalAmount;
        Rec.Quantity := NewTotalQuantity;
        Rec.TotalWeight := NewTotalWeight;
        Rec.Insert();
    end;
}

pageextension 50103 SalesOrderPageExt extends "Sales Order"
{
    layout
    {
        addfirst(FactBoxes)
        {
            part(SelectedSalesOrderLinesTotal; "Sales Order Lines Total")
            {
                Caption = 'Sales Order Lines Total';
                ApplicationArea = All;
                SubPageLink = Number = const(2);
            }
        }
    }
    actions
    {
        addbefore("F&unctions")
        {
            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;
                    TotalWeight: Decimal;
                    LinesCount: Integer;
                    LinesAverage: Decimal;
                    LineWeight: Decimal;
                begin
                    SelectedSalesLines.Reset();
                    LinesCount := 0;
                    LinesAverage := 0;
                    TotalAmount := 0;
                    TotalQuantity := 0;
                    TotalWeight := 0;
                    CurrPage.SetSelectionFilter(SelectedSalesLines);
                    if SelectedSalesLines.FindSet() then begin
                        repeat
                            LinesCount += 1;
                            TotalAmount += SelectedSalesLines.Amount;
                            TotalQuantity += SelectedSalesLines.Quantity;
                            LineWeight := SelectedSalesLines."Net Weight" * SelectedSalesLines.Quantity;
                            TotalWeight += LineWeight;
                        until SelectedSalesLines.Next() = 0;
                    end;
                    if LinesCount > 0 then begin
                        LinesAverage := TotalAmount / LinesCount;
                        CurrPage.SelectedSalesOrderLinesTotal.Page.SetTotals(LinesCount, LinesAverage, TotalAmount, TotalQuantity, TotalWeight);
                        CurrPage.SelectedSalesOrderLinesTotal.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
        TotalSalesOrderLines: Record TotalSalesOrderLines;
    begin
        TotalSalesOrderLines.Reset();
        TotalSalesOrderLines.DeleteAll();
    end;
}[/code]
  • RimasPL Profile Picture
    RimasPL 15 on at
    RE: How to make selected sale lines to calculate

    now trying Provider = "Sales Order Subform"; but got obvously error : "AL0186: Reference 'Sales Order Subform' in application object 'Sales Order' does not exist

    "

    Maybe someone know script where calculate quantity of only selected sales order lines ?

    now trying this code dont know how to modifie it, because calculating all order lines, not only selected... :(

    table 50105 TotalSalesOrderLines

    {

       DataClassification = CustomerContent;

       Caption = 'Total Sales Order Lines';

       fields

       {

           field(1; Number; Integer)

           {

               Caption = 'Number';

               DataClassification = CustomerContent;

           }

           field(2; LineCount; 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;

           }

           field(30; TotalWeight; Decimal)

           {

               Caption = 'Total Weight';

               Editable = false;

           }

       }

       keys

       {

           key(PK; Number)

           {

               Clustered = true;

           }

       }

    }

    page 50106 "Sales Order Lines Total"

    {

       PageType = CardPart;

       Editable = false;

       SourceTable = TotalSalesOrderLines;

       layout

       {

           area(Content)

           {

               field(LineCount; Rec.LineCount)

               {

                   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;

               }

               field(TotalWeight; Rec.TotalWeight)

               {

                   Caption = 'Total Weight';

                   ApplicationArea = All;

               }

           }

       }

       procedure SetTotals(var NewLineCount: Integer; NewAverage: Decimal; NewTotalAmount: Decimal; NewTotalQuantity: Decimal; NewTotalWeight: Decimal)

       begin

           Rec.Reset();

           Rec.DeleteAll();

           Rec.Init();

           Rec.Number := 1;

           Rec.LineCount := NewLineCount;

           Rec.LinesAverage := NewAverage;

           Rec.Amount := NewTotalAmount;

           Rec.Quantity := NewTotalQuantity;

           Rec.TotalWeight := NewTotalWeight;

           Rec.Insert();

       end;

    }

    pageextension 50103 SalesOrderPageExt extends "Sales Order"

    {

       layout

       {

           addfirst(FactBoxes)

           {

               part(SelectedSalesOrderLinesTotal; "Sales Order Lines Total")

               {

                   Caption = 'Sales Order Lines Total';

                   ApplicationArea = All;

                   Provider = saleslines;

                   SubPageLink = Number = const(1);

               }

           }

       }

       actions

       {

           addbefore("F&unctions")

           {

               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;

                       TotalWeight: Decimal;

                       LinesCount: Integer;

                       LinesAverage: Decimal;

                       LineWeight: Decimal;

                   begin

                       SelectedSalesLines.Reset();

                       LinesCount := 0;

                       LinesAverage := 0;

                       TotalAmount := 0;

                       TotalQuantity := 0;

                       TotalWeight := 0;

                       SelectedSalesLines.setRange("Document Type", rec."Document Type");

                       SelectedSalesLines.setRange("Document No.", rec."No.");

                       if SelectedSalesLines.FindSet() then begin

                           repeat

                               LinesCount += 1;

                               TotalAmount += SelectedSalesLines.Amount;

                               TotalQuantity += SelectedSalesLines.Quantity;

                               LineWeight := SelectedSalesLines."Net Weight" * SelectedSalesLines.Quantity;

                               TotalWeight += LineWeight;

                           until SelectedSalesLines.Next() = 0;

                       end;

                       if LinesCount > 0 then begin

                           LinesAverage := TotalAmount / LinesCount;

                           CurrPage.SelectedSalesOrderLinesTotal.Page.SetTotals(LinesCount, LinesAverage, TotalAmount, TotalQuantity, TotalWeight);

                           CurrPage.SelectedSalesOrderLinesTotal.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

           TotalSalesOrderLines: Record TotalSalesOrderLines;

       begin

           TotalSalesOrderLines.Reset();

           TotalSalesOrderLines.DeleteAll();

       end;

    }

    Also tryied  this code : 

    table 50105 TotalSalesOrderLines
    {
        DataClassification = CustomerContent;
        Caption = 'Total Sales Order Lines';
        fields
        {
            field(1; Number; Integer)
            {
                Caption = 'Number';
                DataClassification = CustomerContent;
            }
            field(2; LineCount; 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;
            }
            field(30; TotalWeight; Decimal)
            {
                Caption = 'Total Weight';
                Editable = false;
            }
        }
        keys
        {
            key(PK; Number)
            {
                Clustered = true;
            }
        }
    }

    page 50106 "Sales Order Lines Total"
    {
        PageType = CardPart;
        Editable = false;
        SourceTable = TotalSalesOrderLines;
        layout
        {
            area(Content)
            {
                field(LineCount; Rec.LineCount)
                {
                    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;
                }
                field(TotalWeight; Rec.TotalWeight)
                {
                    Caption = 'Total Weight';
                    ApplicationArea = All;
                }
            }
        }
        procedure SetTotals(var NewLineCount: Integer; NewAverage: Decimal; NewTotalAmount: Decimal; NewTotalQuantity: Decimal; NewTotalWeight: Decimal)
        begin
            Rec.Reset();
            Rec.DeleteAll();
            Rec.Init();
            Rec.Number := 1;
            Rec.LineCount := NewLineCount;
            Rec.LinesAverage := NewAverage;
            Rec.Amount := NewTotalAmount;
            Rec.Quantity := NewTotalQuantity;
            Rec.TotalWeight := NewTotalWeight;
            Rec.Insert();
        end;
    }

    pageextension 50103 SalesOrderPageExt extends "Sales Order"
    {
        layout
        {
            addfirst(FactBoxes)
            {
                part(SelectedSalesOrderLinesTotal; "Sales Order Lines Total")
                {
                    Caption = 'Sales Order Lines Total';
                    ApplicationArea = All;
                    SubPageLink = Number = const(1);
                }
                part(MySalesLines; "Sales Order Subform")
                {
                    ApplicationArea = All;
                }
            }
        }
        actions
        {
            addbefore("F&unctions")
            {
                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;
                        TotalWeight: Decimal;
                        LinesCount: Integer;
                        LinesAverage: Decimal;
                        LineWeight: Decimal;
                    begin
                        SelectedSalesLines.Reset();
                        LinesCount := 0;
                        LinesAverage := 0;
                        TotalAmount := 0;
                        TotalQuantity := 0;
                        TotalWeight := 0;
                        SelectedSalesLines.setRange("Document Type", rec."Document Type");
                        SelectedSalesLines.setRange("Document No.", rec."No.");
                        MySalesLines.SetSelectionFilter(SelectedSalesLines);
                        if SelectedSalesLines.FindSet() then begin
                            repeat
                                LinesCount += 1;
                                TotalAmount += SelectedSalesLines.Amount;
                                TotalQuantity += SelectedSalesLines.Quantity;
                                LineWeight := SelectedSalesLines."Net Weight" * SelectedSalesLines.Quantity;
                                TotalWeight += LineWeight;
                            until SelectedSalesLines.Next() = 0;
                        end;
                        if LinesCount > 0 then begin
                            LinesAverage := TotalAmount / LinesCount;
                            CurrPage.SelectedSalesOrderLinesTotal.Page.SetTotals(LinesCount, LinesAverage, TotalAmount, TotalQuantity, TotalWeight);
                            CurrPage.SelectedSalesOrderLinesTotal.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
            TotalSalesOrderLines: Record TotalSalesOrderLines;
        begin
            TotalSalesOrderLines.Reset();
            TotalSalesOrderLines.DeleteAll();
        end;
    }
    added another part and selectionfilter, but still not working :( 
  • RimasPL Profile Picture
    RimasPL 15 on at
    RE: How to make selected sale lines to calculate

    Now i am thinking that i need pageextension to "Sales Order Subform", but because its not working.. now even dont know how to do this ... :(

    mind blowing.. .:D

  • RimasPL Profile Picture
    RimasPL 15 on at
    RE: How to make selected sale lines to calculate

    Thanks for help, but now got error :( :

    Your license does not grant you the following permissions on Page Selected Sales Lines Total: Execute.To view details about your permissions, see the Effective Permissions page. To report a problem, refer to the following server session ID: '94'.

    and after adding permisions and ect. but its didnt counting and summing what i sellected...  counting and suming everything in the sales order.

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 74,115 Super User 2024 Season 2 on at
    RE: How to make selected sale lines to calculate

    Emmmm....The reason is simple. My example is to calculate the total of selected sales Lines on the Sales Order Lines page. But you added it to the Sales Order to calculate the total on the all sales Lines. There is no need for CurrPage.SetSelectionFilter(SelectedSalesLines);, you only need to add a filter.

    pastedimage1685059630263v2.png

    pastedimage1685059869394v3.png

    For example,

    I didn't check the logic of your calculations, hope this gives you some hints.

    Thanks.

    ZHU

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans