Last Price for Part

Good afternoon,

Is there a DB Table/Field that displays the last price of a part that a part was sold for?

Right now, we are using some calculated fields,
- avg( OrderDtl.UnitPrice) * PartWhse.OnHandQty -,
to determine the average unit price in order to examine the approximate value of our On Hand finished goods. Because this BAQ is also pulling only open orders, it has resulted in some blank fields under a large number of on hand finished parts.

We would rather pull the
- last price a unit sold * PartWhse.OnHandQty - (For all orders, open or closed)
in order to more accurately determine the approximate on hand value of these finished goods.

Anyone have any suggestions on how we could accomplish this?

Thanks in advance!

1 Like

You could determine the most recent selling price per part in a sub query (or two), then join the subquery into your top query.

Here’s a post detailing a similar problem/solution:

Thanks Adam!
Would you know which Table would contain the best Display Fields to incorporate into my Main and inner Subqueries to best determine the last selling price of a part?

Looks like you are using OrderDtl today.

Your subquery would use that table.

Which date field on the order are you using as your last sold for date? Or are you just using the order number?

(Just a heads up, I’ve only been using Epicor for less than a month, and the person attempting to show me some things is self-taught, does not have much time to teach me anything, and isn’t the best when he does teach)

I am using the PartNum and also Table criteria of OrderDtl.PartNum and OpenLine = True from the OrderDtl table within the 2nd subquery.

But because 50% of these finished goods do not have an average unit price associated with them, we would rather use different fields and/or Table criteria to determine the last price that all of our On Hand finished parts sold for

Don’t worry we all feel like we are drinking from a firehose.

You are looking for the price you sell the parts? I wonder why that isn’t on the Part table as UnitPrice.

Do you change the price each time you sell an item?

Haha no kidding. It feels better knowing i’m not alone in the chaos.

And yes, there are a couple variables (material cost, outside processing, etc) that could impact the overall price the part is sold for.

Is UnitPrice in the Part table supposed to display the last price the part was sold for?

The unit price is list price for a part. It is the price that pulls onto the order when the part is sold.

It looks like it’s not maintained, because every part displays a UnitPrice of 0.00

Back to using orderdtl then.
Do you have parts that are on multiple lines on an order? I am assuming no.

The rest of this is what the link above is doing for the PO tables.

First subquery

get the latest order by part
select partnum, max(ordernum) as maxOrder
from erp.OrderDtl
group by PartNum

That will give you a list of parts and the latest (last) ordernum it was on.

Then you join that table to the Erp.OrderDtl ( I know it is the same table you just used, but it is needed to get the price).

With the joined table you will then be able to get
OrderNum, PartNum, Last Order Price

Now you can join this subquery table to the PartWhse table to do your calculation.

The baq will have three querys in the end
inner subquery1 (MaxOrderforPart)
inner subquery2 (PriceofPartforMaxOrder using Subquery1)
top query (PartWhse and Subquery2) to do calculations.

Ok i’m trying to follow along, but unsure of a few points.

Subquery 1 - Part Table:
Display Field: PartNum(Group By),
1 Calculated Field = max(OrderNum.OrderDtl)

Subquery 2 - OrderDtl Table & Subquery 1 Table (Table Relations = ?)
OrderDtl.OrderNum, PartNum (From Subquery 1?), (Last Order Price = What display field?)
Calculated Fields = ?

Top Level: PartWhse Table & Subquery 2 Table (Table Relations = ?)
Display & Calculated Fields = ?

Subquery 1 - OrderDtl Table:
Display Field: PartNum(Group By),
1 Calculated Field = max(OrderDtl.OrderNum)

Both of these fields will be the key/relations to the next subquery

Subquery 2 - OrderDtl Table & Subquery 1 Table ( Table Relations = partnum and ordernum )

OrderDtl.OrderNum, OrderDtl.PartNum, OrderDtl.UnitPrice (I don’t know the field of the top of my head I might be different.

Calculated Fields = none

Top Level: PartWhse Table & Subquery 2 Table ( Table Relations = PartWhse.PartNum/ Subquery2.PartNum)

Display & Calculated Fields = The calc field will be the Subquery2.UnitPrice * PartWhse.OnHandQty

Parts that haven’t had an order will not show up if you use inner joins.


@mdiemer welcome to Epicor! As looks to be the case here, learning Epicor’s data structure and understanding what your company’s data “looks like” is almost always the hard part of any BAQ. Syntax, subqueries, calculated fields, etc. may seem tricky at first, but they are easy once you do a few and get the hang of it. But data structure takes a long time to learn. You’ll have to get a feel for how all the common Epicor tables are related to each other, and then get a feel for the character of your company’s data. One good way of learning Epicor structure is opening up all the commonly used menus, going to Field Help -> Technical Details, and read up / poke around to understand the relationship between the UI and the database. The best way to get a feel for your company’s data character is by learning as much as you can about every business process / how things are set up. Once you know what users are doing you’ll know what to expect your data to look like.

For your task there’s obviously no objectively correct way to do this, but here’s some thoughts on how to get to a “reasonable” figure

  1. Base this off the InvcDtl table, not OrderDtl. That will ensure you’re pulling orders that were actually sold/shipped. Typically “Sales Reporting” should be done off the invoice tables, unless you have reason to use the GL tables (which I always try to avoid like the plague). I only base off the GL if Finance absolutely demands it.

  2. Suggest taking some kind of average over the last X invoices for each part, or average of invoices for each part during current date minus X days. You don’t want the figure to be way off because a salesperson priced the last sale of a part way outside the norm.

  3. If you do an average, it would be even better to do a weighted average taking into account the qty of the part on each invoice line. A simple average of Unit Price would skew the figures high (assuming Unit Price goes down the higher the qty).

  4. Watch out for line discounts, make sure to handle them properly however you do this.


Awesome, thanks for your help and patience Ken!
I was able to produce some promising results.
There are some discrepancies that I’ll have to play with and modify but this has been very helpful.

You’re right, understanding the data structure here is what is most challenging, and seems like it will take a good amount of time to really learn and understand. I did find great value in your suggestions, especially poking around in the commonly used menus with the technical details tab open to understand the UI and DB relationship. People here are very busy, so learning about their individual roles and how they use Epicor has been difficult. Especially since I come from a completely different industry that is not relative to manufacturing. Overall, I’ve been taking it day by day and have been making good progress in making my way into the ebb and flow of things. Thank you for taking the time to provide these encouraging and wise words.

1 Like

This is similar but not the same.

Here is a BAQ that will show the most current price in the supplier price list. Parts shown only for supplier where the supplier is the primary.

Look at the calculated fields on the topquery and subquery. It might help you narrow down your results.

_LastPrice.baq (46.2 KB)


@knash Thanks again for this, you’re the man!