web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Summing a value of a f...
Small and medium business | Business Central, N...
Answered

Summing a value of a field

(1) ShareShare
ReportReport
Posted on by Divan 105
Good Day,
 
I am trying to sum a value form a specific table extension into a new field on another table but i keep on getting a value of 0, and not the value which is in the lines. Can you please assist and help me where i am going wrong?
 
The below is the existing table extension with the field that i want to sum: 
 
tableextension 37084585 ProdOrderLineExt extends /Prod. Order Line/
 
field(37084505; /Relative Cost Factor/; Decimal)
        {
            BlankZero = true;
            Caption = 'Relative Cost Factor';
            DecimalPlaces = 0 : 5;
            Description = 'FPW06.01.04-CBP';
            InitValue = 1;
            MinValue = 0;
        }
 
I have tried a few different ways but the last 2 seems to be more in line with what i want to achieve, the first approach was to do a calcFormula like below:
 
tableextension 50029 APTRRC_ProductionOrder extends /Production Order/
{
 
 
    fields
    {
        field(50000; APTRRC_RelativeCostFactor; Decimal)
        {
            Caption = 'Relative Cost Factor';
            FieldClass = FlowField;
            DecimalPlaces = 0 : 5;
            Editable = false;
            // DataClassification = ToBeClassified;
            CalcFormula = sum(/Prod. Order Line/./Relative Cost Factor/ where(/Prod. Order No./ = field(/No./)));
        }
    }
 
Because i was getting a 0 the whole time i tried to add a SetAutoCalcField in the page extension as well but  on the trigger I get the error Argument 1: must be a member when referencing the field APTRRC_RelativeCostFactor, please see below:
 
pageextension 50301 APTRRC_ReleasedProductionOrder extends /Released Production Order/
{
    layout
    {
        addafter(Blocked)
        {
            field(APTRRC_RelativeCostFactor; APTRRC_RelativeCostFactor)
            {
                Caption = 'Relative Cost Factor';
                ApplicationArea = All;
                Visible = false;
            }
        }
    }
 
    trigger OnOpenPage()
    var
        RelativeRec: Record Item;
        TotalQty: Decimal;
    begin
        Clear(TotalQty);
        RelativeRec.Reset();
        RelativeRec.SetAutoCalcFields(APTRRC_RelativeCostFactor);
        if RelativeRec.FindSet() then
            repeat
                TotalQty := TotalQty + RelativeRec.APTRRC_RelativeCostFactor;
            until RelativeRec.Next() = 0;
    end;
}
 
 
The second approach was to write a procedure in the table extension to do the calculation and return the result to my new field, I am not interested in showing the field on the page I only want the value to do some further QA checks:
 
tableextension 50029 APTRRC_ProductionOrder extends /Production Order/
{
 
 
    fields
    {
        field(50000; APTRRC_RelativeCostFactor; Decimal)
        {
            Caption = 'Relative Cost Factor';
            // FieldClass = FlowField;
            DecimalPlaces = 0 : 5;
            Editable = false;
            DataClassification = ToBeClassified;
            // CalcFormula = sum(/Prod. Order Line/./Relative Cost Factor/ where(/Prod. Order No./ = field(/No./)));
        }
    }
 
        procedure CollectRelativeCost()
    var
        RelativeCost: Record /Prod. Order Line/;
        TotalRelative: Decimal;
    begin
        RelativeCost.SetRange(RelativeCost./Prod. Order No./, /No./);
        if RelativeCost.FindSet() then begin
            TotalRelative := 0; // Initialize total before calculating
            repeat
                TotalRelative += RelativeCost./Relative Cost Factor/;
            until RelativeCost.Next() = 0;
 
            APTRRC_RelativeCostFactor := TotalRelative;
            Modify();
        end;
    end;
 
Both these approaches keeps on returning a value of 0 and not the actual sum of values of the lines:
 
 
 
Where am i going wrong?
 
Thanks in advance
 
 
 
Categories:
Dynamics 365 Business Central
Dynamics 365 Business Central
Dynamics 365 Business Central
I have the same question (0)
  • Divan Profile Picture
    Divan 105 on at
    I have changed my approach and written a procedure which seems to be able to do what I want to do, the only issue I have now is that my "ProductionOrder.No" is returrning a blank value:
     
    tableextension 50030 APTRRC_ProductionOrderLine extends "Prod. Order Line"
    {
        fields
        {
            field(50001; APTRRC_TotalRelativeCostFactor; Decimal)
            {
                Caption = 'Total Relative Cost Factor';
                DataClassification = ToBeClassified;
                Editable = false;
            }
        }
        procedure CollectRelativeCost()
        var
            ProductionOrder: Record "Production Order";
            TotalRelative: Decimal;
            ProdOrderNo: Code[20];
        begin
            ProductionOrder.Reset();
            ProductionOrder.SetRange(ProductionOrder."No.", "Prod. Order No.");
     
            MESSAGE('range set');
            MESSAGE('Prod Order No.: ' + FORMAT(ProductionOrder."No."));
            MESSAGE('Prod Order Line: ' + FORMAT("Prod. Order No."));
     
            TotalRelative := 0; // InOnVitialize TotalRelative outside the loop
            MESSAGE('initializzed');
     
            if ProductionOrder.FindSet() then begin
                TotalRelative := TotalRelative + "Relative Cost Factor";
                MESSAGE('While loop');// Accumulate values
            end;
     
            APTRRC_TotalRelativeCostFactor := TotalRelative;
            MESSAGE('Return values'); // Update the field with the calculated total
            Modify();
        end;
    }
  • Suggested answer
    Mohana Yadav Profile Picture
    Mohana Yadav 60,993 Super User 2025 Season 2 on at
    1st approach.
    If the field is added to the page then no need to calculate the flowfield again. It will automatically calculate and show the value.
    Try to remove visible = false condition and try.
    Remove OnOpenPage trigger as well.
    I know you dont want to show it on page but just for your idea.
     
    2nd approach:
    Where are you calling the function CollectRelativeCost?
     
    Did you try to place it on OnAfterGetCurrRecord of Prod Order pageext?
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 95,329 Super User 2025 Season 2 on at
    That is, you want to calculate the total of the Flowfield on lines? And then display it on the Header?
    Let me give a simple example to display the total Qty. to Assign in Sales Lines.
    pageextension 50118 MyExtension extends "Sales Order"
{
    layout
    {
        addafter("Work Description")
        {
            field(Total; Rec.Total)
            {
                ApplicationArea = All;
                Caption = 'Total';
                Editable = false;
            }
        }
    }

    trigger OnOpenPage()
    var
        SalesLine: Record "Sales Line";
        Total: Decimal;
    begin
        SalesLine.Reset();
        Total := 0;
        SalesLine.SetRange("Document Type", Rec."Document Type");
        SalesLine.SetRange("Document No.", Rec."No.");
        SalesLine.SetAutoCalcFields("Qty. to Assign");
        if SalesLine.FindSet() then
            repeat
                Total += SalesLine."Qty. to Assign";
            until SalesLine.Next() = 0;
        if Total <> 0 then begin
            Rec.Total := Total;
            Rec.Modify(true);
        end;

    end;
}
    Hope this helps.
    Thanks
    ZHU
  • Verified answer
    Govinda Kumar Profile Picture
    Govinda Kumar 2,304 Moderator on at
    Hi, the second approach seems correct... but you need to execute the method CollectRelativeCost() within a trigger.. since you can't directly use OnAfterGetCurrentRecord on a table.. so I suggest applying the same code in a page extension instead of a table.. then execute your CollectRelativeCost() procedure within the OnAfterGetCurrRecord() trigger of your page...
     
    pageextension 50301 APTRRC_ReleasedProductionOrder extends /Released Production Order/
    {
        layout
        {
            addafter(Blocked)
            {
                field(APTRRC_RelativeCostFactor; APTRRC_RelativeCostFactor)
                {
                    Caption = 'Relative Cost Factor';
                    ApplicationArea = All;
                    Visible = false;
                }
            }
        }
     
       trigger OnAfterGetCurrRecord()
       begin
       CollectRelativeCost();
       end;
     
    procedure CollectRelativeCost()
        var
            RelativeCost: Record /Prod. Order Line/;
            TotalRelative: Decimal;
        begin
            RelativeCost.SetRange(RelativeCost./Prod. Order No./, /No./);
            if RelativeCost.FindSet() then begin
                TotalRelative := 0; // Initialize total before calculating
                repeat
                    TotalRelative += RelativeCost./Relative Cost Factor/;
                until RelativeCost.Next() = 0;
     
                Rec.APTRRC_RelativeCostFactor := TotalRelative;
                Rec.Modify();
            end;
        end;
    }
     
     

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 3,229

#2
Jainam M. Kothari Profile Picture

Jainam M. Kothari 1,867 Super User 2025 Season 2

#3
YUN ZHU Profile Picture

YUN ZHU 1,153 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans