Latest Price for Material base on Invoice

[Ziqqie_ringstorm]

Good Morning Epicor Community, how can I get latest price for material base on invoice? I tried using MAX() but I get multiple lines with different date, you guys can refer my dashboard image below.

[gpayne (Greg Payne)]

Does PartCost not have the info you need in LastmaterialCost?

If not a top 1 subquery sorted in descending order grouped by part should work.

here is a thread.

Struggling - BAQ Last Purchased Job Material

Adam, What do you mean check the Group By in the sub query but not the calculated field itself? I also need PODate from POHeader. Would I do this by pulling in another POHeader table or in the subquery itself? 2 subqueries right now total right? PODetail and POHeader? Do I pull them both into the Top query or POHeader into PODetail then PODetail into the Top query with JobMtl?