Dataport Last Unit Cost - Performance

depro

Hi Experts,

I`am using NAV Native Database 4.0 sp3.

I need to find last "Cost per Unit" from Value Entry table for each Item in database.
I`ve wrote dataport with Item Table as DataItem.

I`ve put this code in trigger: Item - OnBeforeExportRecord()

CLEAR(rValueEntry);
rValueEntry.SETRANGE("Item No.", "No.");
rValueEntry.SETRANGE("Location Code", MyLocationCode);
rValueEntry.SETRANGE("Item Ledger Entry Type", rValueEntry."Item Ledger Entry Type"::Purchase);
rValueEntry.SETFILTER("Invoiced Quantity", '>%1', Zero);
rValueEntry.SETFILTER("Cost per Unit", '<>%1', Zero);
IF rValueEntry.FINDLAST THEN
  LastCost := rValueEntry."Cost per Unit"
ELSE
  LastCost := 0;

My dataport exports exactly what i need.

But the problem is performance. My dataport run time is about 15-20 minuts.

I`ve add new key to Value Entry Table:
Item No.,Location Code,Entry Type,Invoiced Quantity

Number of records for table I`ve used:
Item: 13 320 records;
Value Entry: 495 000 records;

Is there any way to make this operation faster?

regards;
Depro

matteo_montanari

depro wrote:

Hi Experts,

I`am using NAV Native Database 4.0 sp3.

I need to find last "Cost per Unit" from Value Entry table for each Item in database.
I`ve wrote dataport with Item Table as DataItem.

I`ve put this code in trigger: Item - OnBeforeExportRecord()

CLEAR(rValueEntry);
rValueEntry.SETRANGE("Item No.", "No.");
rValueEntry.SETRANGE("Location Code", MyLocationCode);
rValueEntry.SETRANGE("Item Ledger Entry Type", rValueEntry."Item Ledger Entry Type"::Purchase);
rValueEntry.SETFILTER("Invoiced Quantity", '>%1', Zero);
rValueEntry.SETFILTER("Cost per Unit", '<>%1', Zero);
IF rValueEntry.FINDLAST THEN
  LastCost := rValueEntry."Cost per Unit"
ELSE
  LastCost := 0;

My dataport exports exactly what i need.

But the problem is performance. My dataport run time is about 15-20 minuts.

I`ve add new key to Value Entry Table:
Item No.,Location Code,Entry Type,Invoiced Quantity

Number of records for table I`ve used:
Item: 13 320 records;
Value Entry: 495 000 records;

Is there any way to make this operation faster?

regards;
Depro

Hi

Add
rValueEntry.SETCURRENTKEY("Item No");
between "clear" and the first "setrange".

Bye

Matteo

depro

Thank You ETO !