Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Adding average field f...
Small and medium business | Business Central, N...
Answered

Adding average field from Item Ledger Entry table breaks the Item Invoicing FactBox

(1) ShareShare
ReportReport
Posted on by Hagru 225
Hello!
 
I want to add a field to the /Item Invoicing FactBox/ in Business Central. This is the box that appears to the right when clicking on an item in the Items page.
 
 
I want this field to show the average amount that item is being sold for. I figured I could add a field to the Item table that gets the average of the /Sales Amount (Actual)/ from the /Item Ledger Entry/ table.
 
So I tried adding this to my Item table:
 
field(50103; /Average Sales Amount/; Decimal)
        {
            CalcFormula = average(/Item Ledger Entry/./Sales Amount (Actual)/ WHERE(/Item No./ = field(/No./)));
            Caption = 'Average Sales Amount';
            Editable = false;
            FieldClass = FlowField;
        }
 
Then I tried adding that field to the field to the /Item Invoicing FactBox/ page like this:
 
pageextension 50123 ItemInvFactBoxExt extends /Item Invoicing FactBox/
{
    layout
    {
        addlast(content)
        {
            field(/Average Sales Amount/; Rec./Average Sales Amount/)
            {
                ApplicationArea = All;
            }
        }
    }
}
 
But that gives me an error in the FactBox saying:
Error: An unexpected error occurred after a database command was cancelled..
 
 
I tried adding another couple of fields from the Item Ledger Entry table, such as /Qty. On Sales Order/ which is quite similar in a way, since it is a CalcFormula sum field from the same table. I am able to add that field, so it seems to me like there is a problem with the /average/ field in this particular case.
 
How can I get the field I want to show in the FactBox without breaking it?
Categories:
Business Central
  • Verified answer
    Mohamed Amine Mahmoudi Profile Picture
    Mohamed Amine Mahmoudi 9,536 Super User 2024 Season 2 on at
    Adding average field from Item Ledger Entry table breaks the Item Invoicing FactBox
    Hi @ChristianPIT,
    it seems to me that the problem is due to the "Sales Amount (Current)" field, since it is FlowField
    whatever please try this code in your page extension :
    pageextension 50123 ItemInvFactBoxExt extends "Item Invoicing FactBox"
{
    layout
    {
        addlast(content)
        {
            field(AvgSalesAmount; AvgSalesAmount)
            {
                ApplicationArea = All;
                Caption = 'Average Sales Amount';
            }
        }
    }

    trigger OnAfterGetCurrRecord()
    var
        ItemLedgerEntry: Record "Item Ledger Entry";
        counter: Integer;
        sum: Decimal;
    begin
        ItemLedgerEntry.SetRange("Item No.", Rec."No.");
        if ItemLedgerEntry.FindSet() then
            repeat
                ItemLedgerEntry.CalcFields("Sales Amount (Actual)");
                sum := sum + ItemLedgerEntry."Sales Amount (Actual)";
                counter := counter + 1;
            until ItemLedgerEntry.Next() = 0;

        if counter = 0 then
            AvgSalesAmount := 0
        else begin
            AvgSalesAmount := sum / counter;
        end;
    end;

    var
        AvgSalesAmount: Decimal;
}
    Best regards,
    Mohamed Amine MAHMOUDI
  • Suggested answer
    KasparsSemjonovs Profile Picture
    KasparsSemjonovs 3,926 Super User 2024 Season 2 on at
    Adding average field from Item Ledger Entry table breaks the Item Invoicing FactBox
    I see no exception in calculation if there are no Sales done... and it would result in division by zero which could be the cause of the error.

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

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans