How to find the last (recent) purchase unit price ofany item

mkpjsr
mkpjsr Member Posts: 587
edited 2010-06-19 in NAV/Navision Classic Client
Hi all,

I want to find out the latest purchase unit price of any item from "Value Entry" table on a report having "Item" and "Value Entry" data item.

Can anybody guide me.

Answers

  • matttrax
    matttrax Member Posts: 2,309
    You don't even need the Value Entry table, although it is indirectly used. Just find the last Item Ledger Entry with Entry Type = Purchase. Do a CALCFIELDS on Cost Amount (Actual) and divide it by the quantity.
    Matt Traxinger - ArcherPoint
  • mkpjsr
    mkpjsr Member Posts: 587
    matttrax wrote:
    Just find the last Item Ledger Entry with Entry Type = Purchase.

    that is what i wanted to know, how to find the last entry...
  • SLF25
    SLF25 Member Posts: 37
    that is what i wanted to know, how to find the last entry...

    http://www.mibuso.com/howtoinfo.asp?FileID=22

    Search for FINDLAST
  • ufuk
    ufuk Member Posts: 514
    mkpjsr wrote:
    that is what i wanted to know, how to find the last entry...

    It depends on your case. If latest entry no is enough then you can directly find last record with the primary key. If latest posting date is your criteria then you have to set related key (ex: posting date) to find the last record. Be aware that a record having last entry no do not have to be the record having latest posting date.
    Ufuk Asci
    Pargesoft
  • mkpjsr
    mkpjsr Member Posts: 587
    ufuk wrote:
    mkpjsr wrote:
    that is what i wanted to know, how to find the last entry...

    It depends on your case. If latest entry no is enough then you can directly find last record with the primary key. If latest posting date is your criteria then you have to set related key (ex: posting date) to find the last record. Be aware that a record having last entry no do not have to be the record having latest posting date.

    In my case i have to find the latest posting date where Item Ledger Entry Type is Purchase, then what i should do, i have written the following code but getting Rate as 0 
    Rate-> decimal

ValueEntry.RESET;
ValueEntry.SETFILTER(ValueEntry."Item Ledger Entry Type",'%1',ValueEntry."Item Ledger Entry Type"::Purchase);
ValueEntry.SETRANGE(ValueEntry."Item No.",ValueEntry."Item No.");
IF ValueEntry.FINDLAST THEN
  ValueEntry.CALCFIELDS(ValueEntry."Cost Amount (Actual)");
  if ValueEntry."Invoiced Quantity">0 then
  Rate:=ValueEntry."Cost Amount (Actual)"/ValueEntry."Invoiced Quantity";
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    matttrax wrote:
    You don't even need the Value Entry table, although it is indirectly used. Just find the last Item Ledger Entry with Entry Type = Purchase. Do a CALCFIELDS on Cost Amount (Actual) and divide it by the quantity.

    The item ledger cost amount (actual) includes any landed cost (item charges). So it does not reflect the actual cost the vendor invoiced you at.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • ufuk
    ufuk Member Posts: 514
    edited 2010-06-16
    Costing method affects the way value entry records created. For ex: if you use standard method then the variance of cost is inserted as an other record so you have to be careful when getting cost from value entry. You can use both tables by filtering the appropriate fields but basically you can use Item Ledger Entry Cost Amount (Actual) field which is already referencing Value Entry Cost Amount (Actual) field.

    You can modify code like: 
    ItemLedgEntry.RESET;
ItemLedgEntry.SETCURRENKEY("Item No.","Entry Type")
ItemLedgEntry.SETRANGE("Item No.",pItemNo); // pass as parameter
ItemLedgEntry.SETRANGE("Entry Type",ItemLedgEntry."Entry Type"::Purchase);
ItemLedgEntry.SETFILTER(Invoiced Quantity,'>0'); //exclude returns
IF ItemLedgEntry.FINDLAST THEN BEGIN
  ItemLedgEntry.CALCFIELDS("Cost Amount (Actual)");
  Rate := ItemLedgEntry."Cost Amount (Actual)"/ItemLedgEntry."Invoiced Quantity";
END;
    Ufuk Asci
    Pargesoft
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    Again, ItemLedgEntry."Cost Amount (Actual)" is NOT a good place to get the last purchase price! Item charge gets considered into this.

    The easist way is to get the last purchase invoice line record.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • ufuk
    ufuk Member Posts: 514
    Alex Chow wrote:
    Again, ItemLedgEntry."Cost Amount (Actual)" is NOT a good place to get the last purchase price! Item charge gets considered into this.

    The easist way is to get the last purchase invoice line record.

    You're right. If you want to get the price on invoice, invoice line is the best place. But he said he wanted to get it from Value Entry meaning like want to get all acquisition cost. As you said this cost includes charges and also variances of standard cost.
    Ufuk Asci
    Pargesoft
  • mkpjsr
    mkpjsr Member Posts: 587
    edited 2010-06-17
    Alex Chow wrote:
    Again, ItemLedgEntry."Cost Amount (Actual)" is NOT a good place to get the last purchase price! Item charge gets considered into this.

    The easist way is to get the last purchase invoice line record.

    yes, i wrote the following code and my problem is resolved::

    PurchInvLine.RESET;
    PurchInvLine.SETRANGE("No.","Value Entry"."Item No."); // pass as parameter
    PurchInvLine.SETFILTER(Quantity,'>0'); //exclude returns
    IF PurchInvLine.FINDLAST THEN
    BEGIN
    IF PurchInvLine."Tax Group Code"='CST%' THEN
    Rate := (PurchInvLine."Amount To Vendor"+PurchInvLine."Tax Amount"+PurchInvLine."Charges To Vendor")/ PurchInvLine.Quantity
    ELSE
    Rate := (PurchInvLine."Amount To Vendor"+PurchInvLine."Charges To Vendor")/ PurchInvLine.Quantity;
    END;
  • matttrax
    matttrax Member Posts: 2,309
    Alex Chow wrote:
    Again, ItemLedgEntry."Cost Amount (Actual)" is NOT a good place to get the last purchase price! Item charge gets considered into this.

    This is true. It is the sum of all value entries related to the item ledger entry, so item charges, revaluation journals, etc.

    If you want to get the last price you paid for something, as the title suggests, then you should look at the purchase invoice line or value entry (I prefer always getting monetary amounts from ledger tables like value entry myself). If you just want the cost of the item do the CALCFIELDS on Item Ledger Entry.
    Matt Traxinger - ArcherPoint
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    matttrax wrote:
    If you just want the cost of the item tier do the CALCFIELDS on Item Ledger Entry for that costing tier.

    Corrected. :D
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • Andwian
    Andwian Member Posts: 627
    What about just using the Last Direct Cost field on Item data Item?
    Regards,
    Andwian
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    Andwian wrote:
    What about just using the Last Direct Cost field on Item data Item?

    This can be changed by positive adjustments.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • Andwian
    Andwian Member Posts: 627
    Alex Chow wrote:
    Andwian wrote:
    What about just using the Last Direct Cost field on Item data Item?

    This can be changed by positive adjustments.

    Ah yes, you were right, Alex!
    Regards,
    Andwian

Categories