Getting the Item Amount in Sell View Trade Agreements

Hi Guys,

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.

Regards,

Edmar

select firstonly priceDiscTable order by recid desc where priceDiscTable.ItemId == InventTable.ItemId;

inventOnhandTmp.Amount = priceDiscTable.Amount;

Hi Amol,

I already try this, but not exactly what you give.
i never use recid to order by…

i try this … tnx.

Hey JEMT what is the problem with recid. It’s a system generated record and using this field is more convenient to find exactly what you want.

Amol you don’t get me, what i mean is i haven’t try yet the recid.

Anway, I try what you say and here is the result

Some items were correct, but other items have same the cost.

Where is your code currently. Please explain in details about your requirement.

i already explain above, with screen shots and codes

anyone? please help…

Can u send the screen shot of your form design. You are customizing on standard form or created new one?

And above my reply i asked that where is your code situated currently (On datasource method or dataTable method)?