write query (condition) in Navision Select Distinct("Item No.","Lot No.","Expiration Date") from "Warehouse Entry"

Business Central/NAV Developers Forum
1

Dear friends

how can i write this query (condition) in Navision Select Distinct(“Item No.”,“Lot No.”,“Expiration Date”) from "Warehouse Entry"

2

Hi Akhilesh,

that’s a challenge in NAV, because we have no select distinct. What you need is:

  • a proper key on the warehouse entries, consisting of “Item No.”,“Lot No.”,“Expiration Date” - these must be the first thre members of the key.

  • a special loop in C/AL, like in report 94:

with whseentry do begin

// set the key

if find(’-’) then begin

repeat

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

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

setrange(“Expiration Date”,“Expiration Date”);

//here you can copy to another table, or do calculations on those entries

find(’+’);

setrange(“Item No.”);

setrange(“Lot No.”);

setrange(“Expiration Date”);

until whseentry.next = 0

end;

This loop touches one record for the selected fields, but only (!) if the key is right.

with best regards

Jens

3

You can always create a dedicated view on SQL to achive your result.

4

This is the logic

I forgot to use this option

Thanks Jens.

I have already written one code for that but its taking around 6 min for 15 lacks warehouse entries …
Your code its reduced to 1.5 min … nice logic Keep it

regards

Akhilesh

5

Thank U

6

i tried it for G/L entry.pos order no. but it is not working.

it just take only last value.

7

If you’re on NAV 2013 or later, it’s simpler to do this. Use a basic query object.

There’s a NAV design pattern for this: SELECT DISTINCT with Queries.