Invoice item field for cost price rather than last cost price (stock field)

[gazmoz17]

Hi,

Is there such a field. E.g. cost price equivalent of these sales price fields.

INVOICE_ITEM.FOREIGN_UNIT_PRICE
INVOICE_ITEM.FOREIGN_NET_AMOUNT

So that I can run historical Inv profit reports and profit is correct,as its referencing the inv cost price at the time of inv (as does for fields above rather than STOCK.SALES_PRICE). Rather than current Last Cost price for stock items.

Reference what we have spoke about before (we dont have stock switched on/your recommendation of notional spare stock field for cost price)....

...I know that would work in relation to below topic we spoke about but dont think it would for historical inv item report?...as notional field still in STOCK table and not Inv Item table?...stumped tbh.

viewtopic.php?t=4236

Notional cost price.PNG

I wish to run a excel Query for Inv profit reports but Im guessing it would recalc cost price and therefore all inv profit (upon refresh) based on current Last Cost price from stock field? Undoing historical invs which (should be loosely accurate as im currently exporting this as sage report into excel on a weekly basis). Everything else on this manual report I know I could do as a query.

Query.PNG

Not got a clue how to get around this

Many Thanks
Gareth

[brucedenney]

If you wish to calculate invoice profitability using the stock transactions, then you need to use the cost price off the stock transaction for that invoice / sales order rather than the average/last cost price.

If the stock transaction history has been cleared you will not be able to do this as the individual transactions will not be there.

So you need to find the stock transaction record where the inv ref matches the Invoice / Sales order number and the StockCode matched the stock code on the invoice/sop Item.

You may need to have a contingency for older items to use the average where the transactions no longer exist.

I would make a Hash field in the Stock Transaction data sheet which is a combination of STOCK_CODE and INV_REF.

Then for each invoice item I would make a hash field of the STOCK_CODE and INVOICENUBMBER (or SOP) you can now lookup this field in the other table and get the cost price off the stock transaction and so have an accurate valuation.

[gazmoz17]

Hi Bruce,

Thanks for the reply, makes complete sense what you've said and would be a good way to do it.

However, we dont use stock at all, we dont record it at all in sage or any system for that matter....I know dont ask out of my hands.

Stock.PNG

I didnt know if there was a work around, not for historical data but going fwd after any potential work around was established. Work around....was thinking was an ivoice item expression that I could call in an invoice report, but Im guessing the expression would have to be based on Last stock price anyway so would be wrong & always linked to current cost price. Something like store last cost price (date stamped at time of invoice *inv item qty), then if report could store this and return it.

Ranting there I know that doesnt really make sense & prob not possible Im guessing there's no time stamp facility.

Manual excel (from exported sage report) ref "Query Excel Sample"in my earlier post...

The excel is loosley correct for the highlighted "Prod Costs" as I run it on a weekly basis, however if colleague has done a cost price change that week then it will be incorrect as will profit etc.

If I set that up as a Query, when query refreshes will it refresh historical Invoices (that are already on my manual excel going back months) with current incorrect Last Cost Price (Standard), incorrect as in not correct at time of inv. Can I specify the Query to only bring in new Data e.g. the next invoice number onwards e.g. Inv 13948?

As I'm doing now for my Sage report I just set the Criteria to next Invoice number from.

Excel manual report.PNG

Thanks

[brucedenney]

Sounds like you are trying to do something without all the information you need, ie the historical cost prices.

You could always use the historical prices off purchase orders as a guide.

Build a query that gets you the pop date, stockcode and price

Then you can lookup the stock code and the date of the sales invoice to get the approximate cost price at that time.