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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Create report comparin...
Small and medium business | Business Central, N...
Suggested answer

Create report comparing todays inventory on hand vs yesterdays inventory on and (by each item)

(0) ShareShare
ReportReport
Posted on by SammySevens 130

Hello All,

I am having a hard time generating a custom report with the following 3 columns:

  • Item No. (every item no. in our items table)
  • Quantity On Hand As of Today
  • Quantity On Hand As of Yesterday

I am experimenting with calcformulas, flow fields, even analysis template.....and thanks to Zhu for a great tutorial - but I am still stuck. 

Any help would be appreciated

I have the same question (0)
  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,037 Moderator on at
    RE: Create report comparing todays inventory on hand vs yesterdays inventory on and (by each item)

    Just a little tips.

    Instead of creating a new flow field you can use the existing field Net change that is already on the Item table.

     field(70; "Net Change"; Decimal)
        {
            CalcFormula = Sum("Item Ledger Entry".Quantity WHERE("Item No." = FIELD("No."),
                                                                  "Global Dimension 1 Code" = FIELD("Global Dimension 1 Filter"),
                                                                  "Global Dimension 2 Code" = FIELD("Global Dimension 2 Filter"),
                                                                  "Location Code" = FIELD("Location Filter"),
                                                                  "Drop Shipment" = FIELD("Drop Shipment Filter"),
                                                                  "Posting Date" = FIELD("Date Filter"),
                                                                  "Variant Code" = FIELD("Variant Filter"),
                                                                  "Lot No." = FIELD("Lot No. Filter"),
                                                                  "Serial No." = FIELD("Serial No. Filter"),
                                                                  "Unit of Measure Code" = FIELD("Unit of Measure Filter"),
                                                                  "Package No." = FIELD("Package No. Filter")));
            Caption = 'Net Change';
            DecimalPlaces = 0 : 5;
            Editable = false;
            FieldClass = FlowField;
        }

  • SammySevens Profile Picture
    SammySevens 130 on at
    RE: Create report comparing todays inventory on hand vs yesterdays inventory on and (by each item)

    Thanks to this blog post - here is what I have - how does it look?

    • Extended item table 
    • Created 4 fields: YDayDate, YDayQuantity, TDayDate,TDayQuantity
    • Created new page with sourcetable = Item
    • OnOpenPage - set 2 filters for YDayDate, and TDayDate

    tableextension 60002 pExt60002ItemMfrName extends Item
{
    fields
    {
        field(60000; "Manufacturer Name"; Text[100])
        {
            TableRelation = Manufacturer;
            Caption = 'lookupManufacturer';
            FieldClass = FlowField;
            CalcFormula = lookup(Manufacturer.Name where(Code = field("Manufacturer Code")));
        }
        field(60001; "Vendor Name"; Text[100])
        {
            TableRelation = Vendor;
            Caption = 'lookupVendorName';
            FieldClass = FlowField;
            CalcFormula = lookup(Vendor.Name where("No." = field("Vendor No.")));
        }
        field(60002; "Today Quantity"; Decimal)
        {
            Caption = 'Field';
        }
        field(60003; "yDayDate"; Date)
        {
            Editable = false;
            FieldClass = FlowFilter;
        }
        field(60004; tDayDate; Date)
        {
            Editable = false;
            FieldClass = FlowFilter;
        }
        field(60005; yDayQuantity; Decimal)
        {
            Editable = false;
            FieldClass = FlowField;
            CalcFormula = sum("Item Ledger Entry".Quantity where("Item No." = field("No."), "Posting Date" = field(yDayDate)));

        }
        field(60006; tDayQuantity; Decimal)
        {
            Editable = false;
            FieldClass = FlowField;
            CalcFormula = sum("Item Ledger Entry".Quantity where("Item No." = field("No."), "Posting Date" = field(tDayDate)));
        }
    }
}

    page 60003 p60003DailyInvDelta
{
    ApplicationArea = All;
    Caption = 'Daily Inventory Delta';
    PageType = List;
    SourceTable = Item;
    UsageCategory = Lists;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field("No."; Rec."No.")
                {
                    ToolTip = 'Specifies the number of the item.';
                }
                field(yDayQuantity; Rec.yDayQuantity)
                {
                    ToolTip = 'Specifies the value of the yDayQuantity field.';
                }
                field(tDayQuantity; Rec.tDayQuantity)
                {
                    ToolTip = 'Specifies the value of the tDayQuantity field.';
                }
            }
        }
    }
    trigger OnOpenPage()
    begin
        Rec.Reset();
        If not Rec.Get() then begin
            Rec.Init();
            Rec.Insert();
        end;
        Rec.SetFilter(yDayDate, '< %1', WorkDate());
        Rec.SetFilter(tDayDate, '<= %1', WorkDate());

    end;
}

  • Suggested answer
    DAnny3211 Profile Picture
    DAnny3211 11,374 on at
    RE: Create report comparing todays inventory on hand vs yesterdays inventory on and (by each item)

    Hi

    you must use flowfield and Flowfilter

    pastedimage1676106412738v2.png

    e.g. the "Purchases (Qty.)" field of the Item table can be filtered to AL code with a simple setfilter or setrange on the Posting date field which is a flowfilter acting on the calculation of the "Purchases (Qty.)" field

    check my answer if it helped you, thanks


    DAniele

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 experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

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

#1
Nimsara Jayathilaka. Profile Picture

Nimsara Jayathilaka. 4,252

#2
Rishabh Kanaskar Profile Picture

Rishabh Kanaskar 4,117

#3
Sumit Singh Profile Picture

Sumit Singh 2,956

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans