Questions Return Fields from Most Recent Record

Here's a little bit more color:

I have the POLine table and I want to get the most recent unit cost for each inventory item. For example, in the filtered result below, I would like only show the circled results.

When I group by InventoryID and apply either a aggregate Max on the order date field or a sort by date, I get the most recent date but I don't get the correct unit cost associated with that PO date. Instead, the cost is a sum of all aggregated records.

I can’t use any of the aggregate tools on the unit cost (max, min, avg, etc.) because cost can go up and down - I want the most recent cost. How can I just get the unit cost associated with the most recent record date? Is there any way to do this within a GI? Any help would be greatly appreciate!

Pretty sure what you want to do above can't be done in GI for the reasons you encountered.

Why don't you just take the price from the Item directly?

This assumes you set AP Preferences Supplier Price Update to On PO Entry.

And the price is tracked per supplier, which may not be what you want. 

Hi Royce,

Thanks for the reply. We have the system setup like that already. However, getting price from the item is a manual process and you have to look at each item individually. I'm trying to create a view that contains the latest costs in one screen.

Additionally, I was planning on using this GI as a data provider on an import scenario to update the standard costs for non-stock items. As such, having it be as automated as possible would be preferred.

Hi Royce, 

thanks again for the suggestions! The same issue remains even with that table. When items have multiple vendors, this table will return multiple rows for each item. This means you have to select the row with the most recent last modified date and then get the cost related to that row. It’s the same issue as using POLines.

Hi Amit,

I've used the solution of joining a table to itself to get the latest record as shown in this Acumatica community post - https://community.acumatica.com/reports-and-generic-inquires-115/how-to-get-the-last-sales-price-and-qty-using-gi-4856

I didn't fully digest the details you posted so it might be a different scenario, but hopefully that solution can provide some help.

Dianne

Dianne - Thank you so much!!! I had been testing out various ways to do self-join but using the null condition never occurred to me. GOLD STAR TO YOU!