GI for Inventory item, Last Cost and Sales Prices no longer working.

Hello, @CodyK ,

The million dollar question: What changed since this query last worked?

I found a previous post with a similar error that may help you, answered by Benjamin Crisman of Acumatica.  Please review the prior post linked here:

Here is another:

Right Join on ARSales Price stands out to me, unusual and different from your other joins. It might be suspect, but I can’t be certain. Try removing one join at a time, to find out which one is causing the errors.

Do you have a copy of this GI that you could restore, perhaps from a test copy of your tenant?

Laura

Hi @CodyK 

Have you upgraded recently? If so, what version did you come from and what did you upgrade to?

Sometimes fields change and that could be causing the issues. 

I agree with you. I think it might have been in an upgrade we did months ago but havent used this GI since. We were on 2022 R2 and are now on 2023 R2

Double check the fields. Do you know how to inspect the fields?

I deactivated the top table relation for INVENTORYITEM->RIGHT->INITEMCOST. It let the report show. Now my LAST COST is incorrect. 

No, I do not know how to inspect the fields. The join, I’m still figuring out lol

Dipak Nilkanth wrote:

Hi @CodyK,

You must join InventoryItem  to INItemCost DAC to fetch the Last Cost as INItemCost DAC contains Last Cost field. Join those table by InventoryID field as below.
 

To inspect fields in Acumatica to need to CTRL + ALT + Click on that field Area. Check below screenshot for Last Cost field.

Hope, it helps!

Thank you! I Did a full join on INVENTORYITEM---INITEMCOST and that seemed to fix it! Thank you to everyone for their help!