I am creating Inventory reports using Report Data Provider that can view some fields, one of the fields is the Cost per item which can view in Sell tab > View Trade Agreements, please see below image.
I had hard time to get it in X++, i already get in SQL please see query below.
select DISTINCT a.ITEMID, AMOUNT COST2BRANCH from INVENTTABLE a INNER JOIN PRICEDISCTABLE b ON a.ITEMID = b.ITEMRELATION where Convert(varchar(10),FROMDATE, 121)=(select Convert(varchar(10),MAX(FROMDATE),121) from PRICEDISCTABLE where ITEMRELATION = b.ITEMRELATION ) order by a.ITEMID asc
Actually i tried doing it in AX and i get what i need, but i thought is correct.
upon seeing the image above, the Item 0000000361 has three line amount setup.
i want to get only the latest Amount, see the Query above.
This my code:
select maxOf(FromDate), Amount from priceDiscTable group by FromDate, Amount where priceDiscTable.ItemRelation == inventTable.ItemId; inventOnhandTmp.Amount = priceDiscTable.Amount;
I will appreciate your quick response.
Thank you very much.