available item reports by location code and barcode and description field

Business Central/NAV Developers Forum
1

i want a report which have two filtering field barcode=itemno

when report will be filter by both then report out look will be as like

Total itemqty--------------remainining qty---------available- Import(date and order no)

  1. 4 2

i want a report which have two filtering field barcode=itemno and warehouse code=itemledgerentry.locationcode.

location code------ total qty------------------remaining qty-------------available----------import(date and order no)

WHSFEIQREB----- 6-----------------------4------------------------------2------------------
ALCOTT-BAT---------4--------------------------4-----------------------------0-

i add dataitem as a item and data indentation as a itemledger entry table.

IF( Item.GETFILTER(Item.“No.”)<>’’) THEN BEGIN
“Item Ledger Entry”.SETRANGE(“Item Ledger Entry”.“Item No.”, Item.GETFILTER(Item.“No.”));
END;

IF (warehouse<>’’) THEN BEGIN
“Item Ledger Entry”.SETRANGE(“Item Ledger Entry”.“Location Code”, warehouse);
END;

this is in onaftergetrecord method

REPEAT
totalqty:=totalqty+“Item Ledger Entry”.Quantity;
reserved:=reserved+“Item Ledger Entry”.“Remaining Quantity”;
UNTIL “Item Ledger Entry”.NEXT = 0;

but it is calculation all when i do not want filter by location code. it must shown total quatntiy according to location code.

how can i do this?

2

Hi,

I am not sure if i am really able to catch your problem here,

But for reporting what you call remaining qty. can’t you just use the inventory field on the Item table?

If you use the iventory field you can use the flowfilter to calculate the remainging qty for each location.

Or is the object of your report to show the remaining qty for every Item ledger entry?

Inge

3

i am doing this. but it does not show total quantity by location code.

OBJECT Report 50016 Available Items
{
OBJECT-PROPERTIES
{
Date=06.01.12;
Time=13:59:27;
Modified=Yes;
Version List=Monir;
}
PROPERTIES
{
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table27;
ReqFilterFields=No.,Description;
}
SECTIONS
{
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table32;
DataItemTableView=SORTING(Item No.);
OnPreDataItem=BEGIN
IF( Item.GETFILTER(Item.“No.”)<>’’) THEN BEGIN
“Item Ledger Entry”.SETRANGE(“Item Ledger Entry”.“Item No.”, Item.GETFILTER(Item.“No.”));
END;

IF (warehouse<>’’) THEN BEGIN
“Item Ledger Entry”.SETRANGE(“Item Ledger Entry”.“Location Code”, warehouse);
END;

CurrReport.CREATETOTALS(totalbylocationcode,“Item Ledger Entry”.Quantity);
END;

OnAfterGetRecord=BEGIN

//IF “Item Ledger Entry”.FIND([Which])
REPEAT
totalqty:=totalqty+“Item Ledger Entry”.Quantity;
reserved:=reserved+“Item Ledger Entry”.“Remaining Quantity”;
UNTIL “Item Ledger Entry”.NEXT = 0;
END;

}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
SectionWidth=23550;
SectionHeight=846;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT(warehouse<>’’) ;
END;

}
CONTROLS
{
{ 1000000011;Label ;2850 ;0 ;2250 ;423 ;HorzAlign=Left;
CaptionML=ENU=Total Qty }
{ 1000000012;Label ;5550 ;0 ;2700 ;423 ;HorzAlign=Left;
CaptionML=ENU=Reserved }
{ 1000000013;Label ;8700 ;0 ;2850 ;423 ;HorzAlign=Left;
CaptionML=ENU=Available }
{ 1000000014;Label ;12150;0 ;9300 ;423 ;HorzAlign=Left;
CaptionML=ENU=Import }
{ 1000000015;Label ;12150;423 ;3300 ;423 ;HorzAlign=Left;
CaptionML=ENU=Date }
}
}
{ PROPERTIES
{
SectionType=Header;
SectionWidth=23550;
SectionHeight=1269;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT(warehouse=’’) ;
END;

}
CONTROLS
{
{ 1000000000;Label ;600 ;423 ;2250 ;423 ;HorzAlign=Left;
CaptionML=ENU=Warehouse }
{ 1000000002;Label ;3450 ;423 ;2250 ;423 ;HorzAlign=Left;
CaptionML=ENU=Total Qty }
{ 1000000003;Label ;6150 ;423 ;2700 ;423 ;HorzAlign=Left;
CaptionML=ENU=Reserved }
{ 1000000004;Label ;9300 ;423 ;2850 ;423 ;HorzAlign=Left;
CaptionML=ENU=Available }
{ 1000000005;Label ;12750;423 ;9300 ;423 ;HorzAlign=Left;
CaptionML=ENU=Import }
{ 1000000006;Label ;12750;846 ;3300 ;423 ;HorzAlign=Left;
CaptionML=ENU=Date }
{ 1000000007;Label ;16050;846 ;6000 ;423 ;HorzAlign=Left;
CaptionML=ENU=Order No }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=23550;
SectionHeight=846;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT(warehouse=’’) ;
END;

}
CONTROLS
{
{ 1000000016;TextBox;5700 ;0 ;2700 ;423 ;HorzAlign=Left;
SourceExpr=reserved }
{ 1000000017;TextBox;2850 ;0 ;2550 ;423 ;HorzAlign=Left;
SourceExpr=totalqty }
{ 1000000018;TextBox;0 ;0 ;2550 ;423 ;HorzAlign=Left;
SourceExpr=“Item Ledger Entry”.“Location Code” }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=23550;
SectionHeight=1692;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT(warehouse<>’’) ;
END;

}
CONTROLS
{
{ 1000000008;TextBox;3300 ;0 ;2550 ;423 ;HorzAlign=Left;
SourceExpr=totalqty }
{ 1000000009;TextBox;6150 ;0 ;2700 ;423 ;HorzAlign=Left;
SourceExpr=reserved }
}
}
{ PROPERTIES
{
SectionType=GroupFooter;
SectionWidth=23550;
SectionHeight=846;
}
CONTROLS
{
{ 1000000001;TextBox;3000 ;0 ;3600 ;423 ;SourceExpr=totalbylocationcode }
{ 1000000010;TextBox;0 ;0 ;2550 ;423 ;HorzAlign=Left;
SourceExpr=“Item Ledger Entry”.“Location Code” }
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=9020;
Height=3410;
}
CONTROLS
{
{ 1000000000;Label ;330 ;770 ;3080 ;550 ;InPage=-1;
CaptionML=ENU=Warehouse }
{ 1000000001;TextBox;3740 ;770 ;3850 ;550 ;SourceExpr=warehouse;
TableRelation=Location.Code }
}
}
REQUESTPAGE
{
PROPERTIES
{
}
CONTROLS
{
}
}
CODE
{
VAR
warehouse@1000000000 : Text[30];
totalqty@1000000001 : Decimal;
reserved@1000000002 : Decimal;
totalbylocationcode@1000000003 : Decimal;

BEGIN
END.
}
RDLDATA
{
}
}

4

I want to calculate total qauanity by location code.