Item Vendor Table 99

voyager99

Hi there, I need some help
In the table 99 I have a lot of entries each item with starting date and differential vendors.
I just want to get the best "Direct Unit Cost" and display it in my Item Card.

Example:
Vendor Unit Startingdate EK-Preis
10000 KG 01.01.2002 4,602
10000 KG 01.04.2004 5
10000 KG 01.07.2004 5
10000 KG 01.10.2004 5
20000 KG 01.01.2002 4,193
20000 KG 01.04.2004 3,7
20000 KG 01.07.2004 3,75
20000 KG 01.10.2004 4,25
20000 KG 01.01.2005 2,5
20000 KG 05.01.2005 1,8
30000 KG 01.01.2002 4,039
30000 KG 01.04.2004 3,95
30000 KG 01.07.2004 4,6
30000 KG 01.10.2004 4,6
30000 KG 24.12.2004 1,95

I am filtering on the Startingdate 0D, WORKDATE
But now i have to get the latest cost each vendor, (FIND('+') :?: )
And of all the latest cost each vendor, the cheapest ! (BLUE entries)
The cheapest with workdate 17.12.04 is the RED one.
Is there anyone, who have a solution :?:

thanks

GoMaD

Have you tried to use 'SETCURRENTKEY' on this table variable
and then selecting a key that starts with the "Starting Date" field?

Regards

voyager99

Hi GoMaD

yes, I´m using the SETCURRENTKEY.
Trouble makes the differential vendor and Starting Date !

Greetings

RobertMo

Have you tried creating your own key ? Like "Item No.,Unit Price".

What about using function MIN as part of FlowField calculation. Adding a flowfiled to Item table with following formula shows minimum direct unit cost for an item regardless of Vendor or Starting Date:

Min("Purchase Price"."Direct Unit Cost" WHERE (Item No.=FIELD(No.)))

Now just play with filters to limit by date/vendor - you can add your own flow filters for Vendor and/or Price Date and include them in formula...