Getting the Item Amount in Sell View Trade Agreements

Dynamics 365 for Finance and Operations/AX Developer Forum
#1

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.

Capture.JPG
Capture.JPG1354×696 148 KB

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;

3731.Capture.JPG
3731.Capture.JPG1186×61 16.5 KB

I will appreciate your quick response.

Thank you very much.

Regards,

Edmar

#2

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

inventOnhandTmp.Amount = priceDiscTable.Amount;

#3

Hi Amol,

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

i try this … tnx.

#4

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.

#5

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

1680.Capture.JPG
1680.Capture.JPG1189×481 106 KB
Some items were correct, but other items have same the cost.

#6

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

#7

i already explain above, with screen shots and codes

#8

anyone? please help…

#9

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)?