0

Linked table=>you only get 1 record=>sort records RESOLVED

Hey

I just start to use Jet.

I want a report with information of 2 tables of NAV: the item-table and the the purchace price-table.

item-table:
*no
*description
*unit price

purchace price:
* direct unit cost
I just want to get the last direct unit cost of the item.


what I do:
=NL("Link";"Purchase Price";"";"Item No.";"=No.";)
But this gives me the oldest direct unit cost (the first record).

Since a new record in the Purchase Price-table is automaticaly created each time our accountant books a purchase invoice,
only the starting date-field contains a value (the date the purchase invoice has been booked), but the ending date-field is always empty.
So there is no way to put an extra filter on the purchase price-table to only get the record with ending date=''.


Is there a way to sort the linked table?
If I could sort the starting-date field in descending instead of ascending order, then the first record would be ok.
I've already tried this =NL("Link";"Purchase Price";"";"Item No.";"=No.";"-Starting Date";"*"),
but this doesn't work.


(I know that the field Last Direct Cost exists in the Item-table, but I don't want to use that field
since a the purchase departement can also register a price in the Purchase Price-table that has not already been used in a sales order)

Hopfully my question is not to complicated.


Best regards

Tom

4 comments

Please sign in to leave a comment.