select distinct itemno from itemledger entry table

i want distinct item no from item ledger entry table. i can get distinct item no from item table. but it loop all item and make slow. so, i want this from itemledger entry table.

how can i find this?

NAV does not have a “SELECT DISTINCT” concept. Your best bet is to use the item table. If you want to know whether it has a ledger entry or not you can add a FlowField to the Item table, or just do it with the simple calculation in code.

I have done this in the following way. this report will cacluate positive and negative adjustment value.

it show correct value. but it does not show first record. this is the problem.

help me.

OBJECT Report 50020 AdjustmentsTest

{

OBJECT-PROPERTIES

{

Date=17.01.12;

Time=14:31:00;

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(( Positive=TRUE) AND ( Negative=FALSE)) THEN BEGIN

“Item Ledger Entry”.SETFILTER(“Entry Type”, ‘=%1’,“Item Ledger Entry”.“Entry Type”::“Positive Adjmt.”);

END;

IF (( Positive=FALSE)AND ( Negative=TRUE)) THEN BEGIN

“Item Ledger Entry”.SETFILTER(“Entry Type”, ‘=%1’,“Item Ledger Entry”.“Entry Type”::“Negative Adjmt.”);

END;

IF (( Positive=TRUE)AND ( Negative=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 (( Positive=FALSE)AND ( Negative=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(’@1@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@’,intProgress);

// timProgress := TIME;

Number := “Item Ledger Entry”.COUNT;

END;

OnAfterGetRecord=BEGIN

// ItemBarcode:=“Item Ledger Entry”.“Item No.”;

CLEAR( FirstBarcode);

CLEAR( FirstLocationCode);

CLEAR( PreviousBarcode);

CLEAR( PreviousLocationCode);

CLEAR( CurrentBarcode);

CLEAR( CurrentLocationCode);

CLEAR(NextBarcode);

CLEAR( NextLocationCode);

IF “Item Ledger Entry”.FIND(’<’) THEN BEGIN

PreviousBarcode:= “Item Ledger Entry”.“Item No.” ;

PreviousLocationCode:=“Item Ledger Entry”.“Location Code”;

END;

IF “Item Ledger Entry”.FIND(’=’) THEN BEGIN

CurrentBarcode:= “Item Ledger Entry”.“Item No.” ;

CurrentLocationCode:=“Item Ledger Entry”.“Location Code”;

END;

IF “Item Ledger Entry”.FIND(’>’) THEN BEGIN

NextBarcode:= “Item Ledger Entry”.“Item No.” ;

NextLocationCode:=“Item Ledger Entry”.“Location Code”;

END;

IF( (NextBarcode= CurrentBarcode) AND ( NextLocationCode=CurrentLocationCode)) THEN BEGIN

CurrReport.SKIP;

END;

// LineCount:=LineCount+1;

// NoOfRecords:= “Item Ledger Entry”.COUNT;

// IF timProgress < TIME - 1000 THEN BEGIN // every second

// timProgress := TIME;

// intProgress := ROUND( LineCount/ NoOfRecords * 10000,1);

// window.UPDATE;

// END;

END;

OnPostDataItem=BEGIN

// window.CLOSE;

END;

}

SECTIONS

{

{ PROPERTIES

{

SectionType=Header;

SectionWidth=22650;

SectionHeight=846;

}

CONTROLS

{

{ 1000000010;Label ;1800 ;0 ;3150 ;423 ;HorzAlign=Left;

CaptionML=ENU=Negative/Positive }

{ 1000000011;Label ;5850 ;0 ;3900 ;423 ;HorzAlign=Left;

CaptionML=ENU=Barcode }

{ 1000000013;Label ;11100;0 ;2700 ;423 ;HorzAlign=Left;

CaptionML=ENU=Warehouse }

{ 1000000014;Label ;14550;0 ;3750 ;423 ;HorzAlign=Left;

CaptionML=ENU=Cost }

}

}

}

}

{ PROPERTIES

{

DataItemIndent=1;

DataItemTable=Table2000000026;

DataItemTableView=SORTING(Number)

ORDER(Ascending)

WHERE(Number=CONST(1));

DataItemVarName=;

OnPreDataItem=BEGIN

IF (Positive =TRUE) THEN BEGIN

// Item.SETRANGE(Item.“No.”, Barcode) ;

PositiveItemLedger.RESET;

PositiveItemLedger.SETRANGE( PositiveItemLedger.“Item No.”, CurrentBarcode);

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;

IF( Positive=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.”, CurrentBarcode);

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

{

SectionType=Body;

SectionWidth=22650;

SectionHeight=846;

OnPreSection=BEGIN

IF ( Positive=FALSE ) THEN BEGIN

CurrReport.SHOWOUTPUT(FALSE);

END ELSE BEGIN

CurrReport.SHOWOUTPUT(TotalPositiveQty>0);

END;

END;

}

CONTROLS

{

{ 1000000000;TextBox;1650 ;0 ;3300 ;423 ;HorzAlign=Left;

SourceExpr=TotalPositiveQty }

{ 1000000001;TextBox;5700 ;0 ;4050 ;423 ;HorzAlign=Left;

SourceExpr=“Item Ledger Entry”.“Item No.” }

{ 1000000002;TextBox;11100;0 ;2700 ;423 ;HorzAlign=Left;

SourceExpr=ToWarehouse }

{ 1000000003;TextBox;14550;0 ;3750 ;423 ;HorzAlign=Left;

SourceExpr=Unitcost }

{ 1000000005;Label ;150 ;0 ;1500 ;423 ;HorzAlign=Left;

CaptionML=ENU=Pos. }

}

}

}

}

{ PROPERTIES

{

DataItemIndent=1;

DataItemTable=Table2000000026;

DataItemTableView=SORTING(Number)

ORDER(Ascending)

WHERE(Number=CONST(1));

DataItemVarName=;

OnPreDataItem=BEGIN

IF ( Negative=TRUE ) THEN BEGIN

NegativeItemLedger.RESET;

// Item.SETRANGE(Item.“No.”, Barcode) ;

NegativeItemLedger.SETRANGE( NegativeItemLedger.“Item No.”, CurrentBarcode);

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;

IF(Negative=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.”,CurrentBarcode);

END ELSE BEGIN

CurrReport.BREAK;

END;

END;

OnAfterGetRecord=BEGIN

IF ( Negative=TRUE ) THEN BEGIN

CLEAR( TotalNegativeQty );

CLEAR(ToWarehouse);

&nbs

This piece of code will do the trick, although not really a select distinct

code based on “Item Ledger Entry”

setcurrentkey( “Item No.”);

if findset then

repeat

setrange( “Item No.”, “Item No.”);

… do what you got to do …

findlast;

setrange( “Item No.”);

unttil next = 0;

i am not clear about this code.

setcurrentkey( “Item No.”);

with “Item Ledger Entry” do begin

if findset then begin

repeat

setrange( “Item No.”, “Item No.”);

until “Item Ledger Entry” .next=0;

end;

end;

http://dynamicsuser.net/forums/p/46482/234938.aspx

i think grouping is the best idea here.