How to get last unit price for an item?

I am using sales price table and item table.

How do I get the last record for the item?

Barathy

Last PRICE is somewhat rather undefinable - as it depends on possible discounts you might apply (and if you mentioned Sales Prices table, I suppose you don’t have a fixed price for all customers).

If you rather thought last COST - this is updated in ItemCard after posting of every PO/PI.

Hi Modris

Thank you for your reply.

I need latest selling price.

When I connect Item and Sales Price table, it brings all the prices. How do I eliminate that and find only last record.

I am presuming that you have defined Sales Prices for your items via : Item Card || Sales || Prices; . The Header of the sales Prices window has an number of filters you can apply, I think the useful filter in your case will probably be the Starting date Filter as the latest selling prices will be determine by Starting Date. So therefore, to use this filter effectively, you must define Starting date for each Sales Price line. Ending date for can also be defined if you wish to effect a date limit on the Sales Price.

You can place a date filter for the latest Sales Prices that you have defined for the item, the result will be derived from the starting/Ending date you have defined. Example, in the Starting date filter, place the following date filters:

17/04/10 (latest) : This shows Item sales prices that has this starting Date

10/04/10…17/04/10: This shows Item sales Prices that have been defined in this date range

10/04/10… : This shows Items Prices that have been defined from this Starting date to date, this will also pick up sales prices where ending date is not in the range of 10/04/10 till date.

I hope this helps.

I found the solution.

IF NOT Salesprice.GET(Item. “No.”)

THEN BEGIN

Salesprice.INIT:

END:

Salesprice.SETCURRENTKEY(“Item No.”)

Salesprice.SETRANGE(“Item No.”, Item.“No.”)

IF NOT Salesprice.FIND(+1) THEN

EXIT:

As mentioned by Modris, the last selling price unit price is not stored, and hence you will have to calculate it. to calculate it, you can filter on the Item Ledger Entries for that Item with Entry Type Sale where “Invoiced Quantity” <> 0, get the last record (if you want posting date wise, the key should be set accordingly), then CALCFIELDS on field “Sales Amount (Actual)”, then divide the value with the “Invoiced Quantity”.

The same value can be stored on the Item card, for that the sales post needs to be modified and a field created on the item table like “Last Direct Cost” which stores the last cost price.

cheers !

HI Barathy,

I have same problem few week from now. Then i solved.My client wonna show Last sales price on Inventory Valuation report.

  1. I add Sales Price Dataitem like that

0333.4-15-2010 10-36-15 AM.png

Value Entry - OnAfterGetRecord()
i:=0;
j:=0;
SalesCode:=’’;
QtyOnHand := 0;
RcdIncreases := 0;
ShipDecreases := 0;
ValueOfQtyOnHand := 0;
ValueOfInvoicedQty := 0;

Sales Price - OnAfterGetRecord()

CostOfShipDecreases := 0;
CostOfInvDecreases := 0;
InvDecreases := 0;

IsPositive := GetSign;

then i write code on Sales Price’s body section

Sales Price, Body (1) - OnPreSection()
CurrReport.SHOWOUTPUT(((“Sales Code”<>SalesCode)) OR ((“Sales Type”=“Sales Type”::“All Customers”)
AND (i=0)));

Sales Price, Body (1) - OnPostSection()
IF “Sales Code”=’‘THEN BEGIN
i+=1;
END
ELSE IF “Sales Code”=’’ THEN BEGIN
j+=1;
END;
SalesCode:=“Sales Code”;
Date:=“Starting Date”;
SalesPriceAmount:=“Unit Price”;
TAmount1:=“Unit Price”*a;
TAmount2:=“Unit Price”*b;
TAmount3:=“Unit Price”*c;
TAmount4:=“Unit Price”*d;

Now i can c last sales price . Maybe its poor way.

Regards PNK

Hello

I did the same thing in different way.

I did not connect Sales Price table to Item table this way.

0333.4-15-2010 10-36-15 AM.png

  1. I added Sales Price table like this.

I connected as a temporary table. First I created Salesprice as RECORD - Sales Price in the Globals (it is a temporary table).

I also created a Unitprice as variable Decimal. Then I write the code under

Item - OnAfterGetRecord()

IF NOT Salesprice.GET(Item. “No.”)THEN BEGIN // Here I am connecting Salesprice temp table to Item table

Salesprice.INIT: // Here I am populating Salesprice table with “Sales Price” data

END:

Salesprice.SETCURRENTKEY(“Item No.”) //Here I am assigning Item No as primary key

Salesprice.SETRANGE(“Item No.”, Item.“No.”) // Here I am filtering. getting data from “Sales price”. "Item No. "= Item.“No.”

IF NOT Salesprice.FIND(+1) THEN // Here I am getting last record of Salesprice for the particular Item

EXIT:

// Then in the sections I create a textbox . Properties of the text box add

DataSetFieldName - Unitprice and SourceExpr - “Salesprice”.“Unit Price”

That is all.

You can see the last sales price in the textbox.

Thanks

Barathy

Hi,

yeah its just show the last unit price. If you have @ and more customer Price Group and all Customers has another unit price, how to show each unit price of customer price group.

Regards