distinct itemno from itemledger entry in a report

monir_asian

i have a report and it's outlook will be
Negative/positive adj.(qty)---Barcode---warehouse-cost
2

---

11333327--cachecabat-55.04
3

---

5627070100024--CACHECABAT---35.80

---

i want to display total quantity of positive and negative adjugment entry type from iterm ledger entry by each barcode and location.

i can take unique barcode from item table .but it is slow and it is looping all value from item table.
so, i want to find out distinct item no from itemledger entry table.

how can i find out this?

Sog

There is no "distinct" in NAV. You'd have to work with temporary tables to reach a similar function.
Best to keep looping the item table.

kine

Or just group the report by the value you want and use the GroupFooter to display the data...

monir_asian

i have grouptotal field on item no and location code.



but it is not appeared correctly.

suppose:

barcode

---

LOCATION

---

quantity

100101-1

---

OUTLET

---

1

100101-1

---

CACHECABAT

---

1

---

My report show only:

100101-1

---

CACHECABAT

---

1



Report output will be following because it's group based on barcode and location code:

barcode

---

LOCATION

---

quantity

100101-1

---

OUTLET

---

1

100101-1

---

CACHECABAT

---

1







help me.







OBJECT Report 50017 Adjustments
{
OBJECT-PROPERTIES
{
Date=18.01.12;
Time=14:48:15;
Modified=Yes;
Version List=Monir;
}
PROPERTIES
{
OnPreReport=BEGIN
//IF PrintToExcel THEN
// MakeExcelInfo;
END;

OnPostReport=BEGIN
//IF PrintToExcel THEN
// CreateExcelbook;
END;

}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table32;
DataItemTableView=SORTING(Item No.)
ORDER(Ascending)
WHERE(Item No.=FILTER(1..));
OnPreDataItem=BEGIN
IF( Barcode<>'') THEN BEGIN
// Item.SETRANGE(Item."No.", Barcode) ;
"Item Ledger Entry".SETRANGE("Item Ledger Entry"."Item No.", Barcode);

END;
IF( DocumentNo<>'') THEN BEGIN
"Item Ledger Entry".SETRANGE("Item Ledger Entry"."Document No.",DocumentNo) ;
END;
IF( Warehouse<>'') THEN BEGIN
"Item Ledger Entry".SETFILTER("Item Ledger Entry"."Location Code", '=%1', Warehouse) ;
// "Item Ledger Entry".SETRANGE("Item Ledger Entry"."Location Code", Warehouse) ;
END;

IF(( PositiveValue=TRUE) AND ( NegativeValue=FALSE)) THEN BEGIN
"Item Ledger Entry".SETFILTER("Entry Type", '=%1',"Item Ledger Entry"."Entry Type"::"Positive Adjmt.");

END;

IF (( PositiveValue=FALSE)AND ( NegativeValue=TRUE)) THEN BEGIN
"Item Ledger Entry".SETFILTER("Entry Type", '=%1',"Item Ledger Entry"."Entry Type"::"Negative Adjmt.");

END;

IF (( PositiveValue=TRUE)AND ( NegativeValue=TRUE)) THEN BEGIN

"Item Ledger Entry".SETFILTER("Entry Type", '=%1|=%2',"Item Ledger Entry"."Entry Type"::"Positive Adjmt.",
"Item Ledger Entry"."Entry Type"::"Negative Adjmt.");
END;

IF (( PositiveValue=FALSE)AND ( NegativeValue=FALSE)) THEN BEGIN

"Item Ledger Entry".SETFILTER("Entry Type", '=%1|=%2',"Item Ledger Entry"."Entry Type"::"Positive Adjmt.",
"Item Ledger Entry"."Entry Type"::"Negative Adjmt.");
END;

// IF ("Negative Adjmt." =TRUE) THEN BEGIN

// END;

"Item Ledger Entry".SETCURRENTKEY("Item Ledger Entry"."Item No.");

"Item Ledger Entry".ASCENDING(TRUE) ;

//window.OPEN('@\',intProgress);
// timProgress := TIME;

Number := "Item Ledger Entry".COUNT;
END;

OnAfterGetRecord=BEGIN
// ItemBarcode:="Item Ledger Entry"."Item No.";


CLEAR( CurrentBarcode);
CLEAR( CurrentLocationCode);


CurrentBarcode:= "Item Ledger Entry"."Item No." ;
CurrentLocationCode:="Item Ledger Entry"."Location Code";
END;

OnPostDataItem=BEGIN
// window.CLOSE;
END;

GroupTotalFields=Item No.,Location Code;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
SectionWidth=23250;
SectionHeight=846;
}
CONTROLS
{
{ 1000000010;Label ;2100 ;0 ;2550 ;423 ;HorzAlign=Left;
CaptionML=ENU=Negative/Positive }
{ 1000000011;Label ;6300 ;0 ;2100 ;423 ;HorzAlign=Left;
CaptionML=ENU=Barcode }
{ 1000000013;Label ;11400;0 ;1950 ;423 ;HorzAlign=Left;
CaptionML=ENU=Warehouse }
{ 1000000014;Label ;15150;0 ;1800 ;423 ;HorzAlign=Left;
CaptionML=ENU=Cost }
}
}
{ PROPERTIES
{
SectionType=GroupFooter;
SectionWidth=23250;
SectionHeight=423;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT:=(PositiveValue=TRUE);
END;

}
CONTROLS
{
{ 1000000000;Label ;0 ;0 ;1500 ;423 ;HorzAlign=Left;
CaptionML=ENU=Pos. }
{ 1000000001;TextBox;1800 ;0 ;3150 ;423 ;HorzAlign=Left;
SourceExpr=+TotalPositiveQty }
{ 1000000002;TextBox;5850 ;0 ;3900 ;423 ;HorzAlign=Left;
SourceExpr="Item Ledger Entry"."Item No." }
{ 1000000003;TextBox;11100;0 ;2700 ;423 ;HorzAlign=Left;
SourceExpr="Item Ledger Entry"."Location Code" }
{ 1000000005;TextBox;14550;0 ;3750 ;423 ;HorzAlign=Left;
SourceExpr=Unitcost }
}
}
{ PROPERTIES
{
SectionType=GroupFooter;
SectionWidth=23250;
SectionHeight=846;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT:=(NegativeValue=TRUE);
END;

}
CONTROLS
{
{ 1000000007;Label ;0 ;0 ;1500 ;423 ;HorzAlign=Left;
CaptionML=ENU=Neg. }
{ 1000000008;TextBox;1800 ;0 ;3150 ;423 ;HorzAlign=Left;
SourceExpr=+TotalNegativeQty }
{ 1000000016;TextBox;5700 ;0 ;4050 ;423 ;HorzAlign=Left;
SourceExpr="Item Ledger Entry"."Item No." }
{ 1000000017;TextBox;11100;0 ;2700 ;423 ;HorzAlign=Left;
SourceExpr="Item Ledger Entry"."Location Code" }
{ 1000000018;TextBox;14550;0 ;3750 ;423 ;HorzAlign=Left;
SourceExpr=Unitcost }
}
}
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table2000000026;
DataItemTableView=SORTING(Number)
ORDER(Ascending)
WHERE(Number=CONST(1));
DataItemVarName=<IntegerPositive>;
OnPreDataItem=BEGIN
IF (PositiveValue =TRUE) THEN BEGIN
// Item.SETRANGE(Item."No.", Barcode) ;

PositiveItemLedger.RESET;
PositiveItemLedger.SETRANGE( PositiveItemLedger."Item No.", "Item Ledger Entry"."Item No.");


IF( DocumentNo<>'') THEN BEGIN
PositiveItemLedger.SETRANGE( PositiveItemLedger."Document No.",DocumentNo) ;
END;
IF( Warehouse<>'') THEN BEGIN
PositiveItemLedger.SETFILTER( PositiveItemLedger."Location Code", '=%1', Warehouse) ;
// "Item Ledger Entry".SETRANGE("Item Ledger Entry"."Location Code", Warehouse) ;
END ELSE BEGIN
PositiveItemLedger.SETFILTER( PositiveItemLedger."Location Code", '=%1', "Item Ledger Entry"."Location Code") ;
END;

IF( PositiveValue=TRUE) THEN BEGIN
PositiveItemLedger.SETFILTER("Entry Type", '=%1', PositiveItemLedger."Entry Type"::"Positive Adjmt.");

END;



PositiveItemLedger.SETCURRENTKEY( PositiveItemLedger."Item No.");

PositiveItemLedger.ASCENDING(TRUE) ;




"Value Entry".SETRANGE("Value Entry"."Item No.","Item Ledger Entry"."Item No.");

IF( Warehouse<>'') THEN BEGIN
"Value Entry".SETFILTER( "Value Entry"."Location Code", '=%1', Warehouse) ;

END ELSE BEGIN
"Value Entry".SETFILTER("Value Entry"."Location Code", '=%1', "Item Ledger Entry"."Location Code") ;
END;



END ELSE BEGIN
CurrReport.BREAK;
END;
END;

OnAfterGetRecord=BEGIN

// IF (Positive =TRUE) THEN BEGIN

CLEAR(TotalPositiveQty);
CLEAR(ToWarehouse);
CLEAR(Unitcost);




WITH PositiveItemLedger DO BEGIN
IF( PositiveItemLedger.FIND('-') )THEN BEGIN
REPEAT

TotalPositiveQty:= TotalPositiveQty+ PositiveItemLedger.Quantity;
//ToWarehouse:=PositiveItemLedger."Location Code";


UNTIL PositiveItemLedger.NEXT=0;
END;
END;




//"Value Entry".SETRANGE("Value Entry"."Item No.", CurrentBarcode);
// "Value Entry".SETRANGE("Value Entry"."Item Ledger Entry No.", "Item Ledger Entry"."Entry No.");

WITH "Value Entry" DO BEGIN
IF( "Value Entry".FIND('-') )THEN BEGIN
REPEAT

Unitcost:= Unitcost+ "Value Entry"."Cost Amount (Actual)";
UNTIL "Value Entry".NEXT=0;
END;
END;


// END;

//IF PrintToExcel THEN
// MakeExcelDataBody;
END;

}
SECTIONS
{
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table2000000026;
DataItemTableView=SORTING(Number)
ORDER(Ascending)
WHERE(Number=CONST(1));
DataItemVarName=<IntegerNegative>;
OnPreDataItem=BEGIN
IF ( NegativeValue=TRUE ) THEN BEGIN

NegativeItemLedger.RESET;
// Item.SETRANGE(Item."No.", Barcode) ;
NegativeItemLedger.SETRANGE( NegativeItemLedger."Item No.","Item Ledger Entry"."Item No.");


IF( DocumentNo<>'') THEN BEGIN
NegativeItemLedger.SETRANGE( NegativeItemLedger."Document No.",DocumentNo) ;
END;
IF( Warehouse<>'') THEN BEGIN
NegativeItemLedger.SETFILTER( NegativeItemLedger."Location Code", '=%1', Warehouse) ;
// "Item Ledger Entry".SETRANGE("Item Ledger Entry"."Location Code", Warehouse) ;
END ELSE BEGIN

NegativeItemLedger.SETFILTER( NegativeItemLedger."Location Code", '=%1',"Item Ledger Entry"."Location Code") ;

END;

IF(NegativeValue=TRUE) THEN BEGIN
NegativeItemLedger.SETFILTER("Entry Type", '=%1', NegativeItemLedger."Entry Type"::"Negative Adjmt.");

END;


// IF ("Negative Adjmt." =TRUE) THEN BEGIN

// END;

NegativeItemLedger.SETCURRENTKEY( NegativeItemLedger."Item No.");

NegativeItemLedger.ASCENDING(TRUE) ;

"Value Entry".SETRANGE("Value Entry"."Item No.","Item Ledger Entry"."Item No.");

IF( Warehouse<>'') THEN BEGIN
"Value Entry".SETFILTER( "Value Entry"."Location Code", '=%1', Warehouse) ;

END ELSE BEGIN
"Value Entry".SETFILTER("Value Entry"."Location Code", '=%1', "Item Ledger Entry"."Location Code") ;
END;



END ELSE BEGIN
CurrReport.BREAK;
END;
END;

OnAfterGetRecord=BEGIN




// IF ( Negative=TRUE ) THEN BEGIN

CLEAR( TotalNegativeQty );
CLEAR(ToWarehouse);
CLEAR(Unitcost);



WITH NegativeItemLedger DO BEGIN
IF( NegativeItemLedger.FIND('-') )THEN BEGIN
REPEAT

TotalNegativeQty:= TotalNegativeQty+ NegativeItemLedger.Quantity;
// ToWarehouse:=NegativeItemLedger."Location Code";


UNTIL NegativeItemLedger.NEXT=0;
END;
END;




// "Value Entry".SETRANGE("Value Entry"."Item No.",CurrentBarcode);
// "Value Entry".SETRANGE("Value Entry"."Item Ledger Entry No.", "Item Ledger Entry"."Entry No.");


WITH "Value Entry" DO BEGIN
IF( "Value Entry".FIND('-') )THEN BEGIN
REPEAT

Unitcost:= Unitcost+ "Value Entry"."Cost Amount (Actual)";
UNTIL "Value Entry".NEXT=0;
END;
END;

//END ELSE BEGIN
//CurrReport.BREAK;

//END;
//IF PrintToExcel THEN
// MakeExcelDataBody;
END;

}
SECTIONS
{
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=11770;
Height=7700;
OnOpenForm=BEGIN
PrintToExcel := FALSE;
PositiveValue:=FALSE;
NegativeValue:=FALSE;
END;

}
CONTROLS
{
{ 1000000000;Label ;880 ;2420 ;3190 ;440 ;InPage=-1;
HorzAlign=Left;
CaptionML=ENU=Warehouse }
{ 1000000001;TextBox;4510 ;2530 ;3850 ;440 ;HorzAlign=Left;
SourceExpr=Warehouse;
TableRelation=Location.Code }
{ 1000000004;Label ;2090 ;5830 ;2090 ;440 ;InPage=-1;
CaptionML=ENU=Print to Excel }
{ 1000000005;CheckBox;4400;5830 ;660 ;440 ;ShowCaption=No;
SourceExpr=PrintToExcel }
{ 1000000002;Label ;880 ;3080 ;3190 ;440 ;InPage=-1;
HorzAlign=Left;
CaptionML=ENU=Document No }
{ 1000000003;TextBox;4510 ;3190 ;3740 ;440 ;SourceExpr=DocumentNo }
{ 1000000006;Label ;770 ;3960 ;3300 ;440 ;InPage=-1;
HorzAlign=Left;
CaptionML=ENU=Entry Type }
{ 1000000007;CheckBox;4510;3960 ;3190 ;440 ;Name=Positive Adjmt.;
ShowCaption=Yes;
HorzAlign=Left;
CaptionML=ENU=Positive Adjmt.;
SourceExpr=PositiveValue }
{ 1000000008;CheckBox;4510;4620 ;3190 ;440 ;Name=Negative Adjmt.;
InPage=-1;
ShowCaption=Yes;
HorzAlign=Left;
CaptionML=ENU=Negative Adjmt.;
SourceExpr=NegativeValue }
{ 1000000009;Label ;880 ;1650 ;3190 ;660 ;HorzAlign=Left;
CaptionML=ENU=Barcode }
{ 1000000010;TextBox;4400 ;1760 ;3850 ;550 ;InPage=-1;
HorzAlign=Left;
SourceExpr=Barcode }
}
}
REQUESTPAGE
{
PROPERTIES
{
}
CONTROLS
{
}
}
CODE
{
VAR
"Sales Shipment Header"@1000000001 : Record 110;
"Purch. Rcpt. Header"@1000000002 : Record 120;
"Transfer Shipment Header"@1000000003 : Record 5744;
Warehouse@1000000000 : Text[30];
ToWarehouse@1000000004 : Text[30];
PrintToExcel@1000000005 : Boolean;
Location@1000000006 : Record 14;
TotalPositiveQty@1000000007 : Decimal;
TotalNegativeQty@1000000008 : Decimal;
totalQty@1000000009 : Decimal;
DocumentNo@1000000010 : Code[20];
NegativeValue@1000000011 : Boolean;
PositiveValue@1000000012 : Boolean;
Unitcost@1000000014 : Decimal;
"Value Entry"@1000000015 : Record 5802;
Barcode@1000000013 : Code[20];
ItemLedger@1000000016 : Record 32;
ItemBarcode@1000000017 : Code[20];
window@1000000018 : Dialog;
timProgress@1000000019 : Time;
intProgress@1000000020 : Integer;
LineCount@1000000021 : BigInteger;
NoOfRecords@1000000022 : BigInteger;
ExcelBuf@1000000023 : TEMPORARY Record 370;
Text001@1000000038 : TextConst 'ENU=Negative/Positive';
Text002@1000000037 : TextConst 'ENU=Barcode';
Text003@1000000036 : TextConst 'ENU=Warehouse';
Text004@1000000035 : TextConst 'ENU=Cost';
Text005@1000000034 : TextConst 'ENU=Available';
Text006@1000000033 : TextConst 'ENU=Import';
Text007@1000000032 : TextConst 'ENU="Date "';
Text008@1000000031 : TextConst 'ENU=Order No';
Text009@1000000030 : TextConst 'ENU="Total "';
Text010@1000000029 : TextConst 'ENU=Company Name';
Text011@1000000028 : TextConst 'ENU=User ID';
Text012@1000000027 : TextConst 'ENU=Report No.';
Text013@1000000026 : TextConst 'ENU=Date';
Text014@1000000025 : TextConst 'ENU=Available Items';
Text015@1000000024 : TextConst 'ENU=Report Name';
Text016@1000000039 : TextConst 'ENU=Pos.';
Text017@1000000040 : TextConst 'ENU=Neg.';
NextBarcode@1000000041 : Code[20];
CurrentBarcode@1000000042 : Code[20];
NextLocationCode@1000000043 : Code[20];
CurrentLocationCode@1000000044 : Code[20];
PositiveItemLedger@1000000045 : Record 32;
NegativeItemLedger@1000000046 : Record 32;
Number@1000000047 : Integer;
PreviousBarcode@1000000048 : Code[20];
PreviousLocationCode@1000000049 : Code[20];
FirstBarcode@1000000050 : Code[20];
FirstLocationCode@1000000051 : Code[20];

PROCEDURE MakeExcelInfo@1000000000();
BEGIN
ExcelBuf.SetUseInfoSheed;
ExcelBuf.AddInfoColumn(FORMAT(Text010),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(COMPANYNAME,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text015),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(FORMAT(Text014),FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text012),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(REPORT::Adjustments,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text011),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(USERID,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text013),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(TODAY,FALSE,'',FALSE,FALSE,FALSE,'');
//ExcelBuf.NewRow;
//ExcelBuf.AddInfoColumn(FORMAT(Text014),FALSE,'',TRUE,FALSE,FALSE,'');
//ExcelBuf.AddInfoColumn(Item.GETFILTERS,FALSE,'',FALSE,FALSE,FALSE,'');

ExcelBuf.ClearNewRow;
MakeExcelDataHeader;
END;

PROCEDURE MakeExcelDataHeader@1000000001();
BEGIN
ExcelBuf.NewRow;
ExcelBuf.AddColumn('',FALSE,'',TRUE,FALSE,TRUE,'@');

ExcelBuf.AddColumn(FORMAT( Text001),FALSE,'',TRUE,FALSE,TRUE,'@');
ExcelBuf.AddColumn(FORMAT(Text002),FALSE,'',TRUE,FALSE,TRUE,'@');
ExcelBuf.AddColumn(FORMAT(Text003),FALSE,'',TRUE,FALSE,TRUE,'@');
ExcelBuf.AddColumn(FORMAT(Text004),FALSE,'',TRUE,FALSE,TRUE,'@');
END;

PROCEDURE MakeExcelDataBody@1000000002();
BEGIN

ExcelBuf.NewRow;
ExcelBuf.AddColumn(Text016,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn(TotalPositiveQty,FALSE,'',FALSE,FALSE,FALSE,'');

ExcelBuf.AddColumn("Item Ledger Entry"."Item No.",FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn(ToWarehouse,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn(Unitcost,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddColumn(Text017,FALSE,'',FALSE,FALSE,FALSE,'');

ExcelBuf.AddColumn(TotalNegativeQty,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn("Item Ledger Entry"."Item No.",FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn(ToWarehouse,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn(Unitcost,FALSE,'',FALSE,FALSE,FALSE,'');

//ExcelBuf.AddColumn(FinalQty,FALSE,'',FALSE,FALSE,FALSE,'');
//ExcelBuf.AddColumn(FinalReserved,FALSE,'',FALSE,FALSE,FALSE,'');
//ExcelBuf.AddColumn(FinalAvailable,FALSE,'',FALSE,FALSE,FALSE,'');
END;

PROCEDURE CreateExcelbook@1000000003();
BEGIN
ExcelBuf.CreateBook;
ExcelBuf.CreateSheet(Text010,Text011,COMPANYNAME,USERID);
ExcelBuf.GiveUserControl;
ERROR('');
END;

BEGIN
END.
}
RDLDATA
{
}
}

kine

Sorting order <> GroupTotalFields...

monir_asian

i am not clear about this reply :

Sorting order <> GroupTotalFields...

mohana_cse06

DataItemTableView=SORTING(Item No.)

GroupTotalFields=Item No.,Location Code;

The key should contain the fields of GroupTotalFields...

ssingla

Standard help says

When using the GroupTotalFields property, be aware of the relationship to the currently active key in the underlying table. Because the GroupTotalFields functionality is closely connected with the active key, some precautions might be required. For example, the No. and City fields are listed as GroupTotalFields while the active key consists of the No., Name, and City fields. If the contents of the Name field change as the records are processed, any grouping based on the City field will fail. Therefore, it may be necessary to prevent the user from choosing a key from the request form of the report, because the grouping only works with the one key that it was designed for. You can remove the request form by using the UseReqForm Property, or remove the Sort button by using the DataItemTableView Property property to set an explicit sorting order (key) for the data item.

monir_asian

I already set group total field=itemno,location code.

but this same problem.
100101-1

---

CACHECABAT

---

1



correct output will be following
100101-1

---

OUTLET

---

1

100101-1

---

CACHECABAT

---

1

mohana_cse06

what is in DataItemTableView?

monir_asian

for Item Ledger Entry:

dataitemtable view=SORTING(Item No.)
grouptotalfield=itemno,locationcode

---

mohana_cse06

](*,) ](*,)

Read the help how Grouping works in reports...

monir_asian

i made new key itemno, location code in itemledger entry table.

and now group is working fine.

but group section is calling two times and data is showing two times.

how can i solve this issue?

dans

take a look at TOTALSCAUSEDBY function.

monir_asian

thanks . it is working fine now.