You’re offline. This is a read only version of the page.
130
Hello All,
I am having a hard time generating a custom report with the following 3 columns:
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
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.
Share
Report
All responses (
Answers (
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;
}
Thanks to this blog post - here is what I have - how does it look?
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;
}
Hi
you must use flowfield and Flowfilter
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,104
Most Valuable Professional
#3
nmaenpaa
101,156