Item Vendor Table 99

voyager99
voyager99 Member Posts: 15
edited 2004-12-19 in Navision Financials
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

Comments

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

    Regards
    Now, let's see what we can see.
    ...
    Everybody on-line.
    ...
    Looking good!
  • voyager99
    voyager99 Member Posts: 15
    Hi GoMaD

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

    Greetings
  • RobertMo
    RobertMo Member Posts: 484
    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...
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

Categories