I want to get Price per Item no agreement included and Modifiedatime

Hi guys!

I want to get Price per Item no agreement included just ITEM and PRICE.

Table involved:

  • InventTable
  • PriceDiscTable

My problem is the amount of item is not showing on my report and the modifieddatetime

and this two fields is from PriceDiscTable

TIA.

Tell us how you are getting/fetching these fields in report?

here is my query…

select a.ITEMID, a.NAMEALIAS, h.AMOUNT COST2BRANCH, A.MODIFIEDBY, h.MODIFIEDDATETIME
from INVENTTABLE a
left JOIN PRICEDISCTABLE h ON a.ITEMID = h.ITEMRELATION
where Convert(varchar(10),FROMDATE, 121)=(select Convert(varchar(10),MAX(FROMDATE),121) from PRICEDISCTABLE where ITEMRELATION = h.ITEMRELATION )
order by ITEMID asc

and here is my X++ code:

while select maxOf(FromDate) from PriceDiscTable
where PriceDiscTable.ItemRelation == inventTable.ItemId
{
tmpItemMCost2Branch.ModDateTime = priceDiscTable.modifiedDateTime;
tmpItemMCost2Branch.Amount =priceDiscTable.Amount;
}

Are you using direct SQL statement in AX reports? Or is there any equivalent x++ select statement/query?

x++ sir … check above … thanks

You are only selecting the from date in the select statement, so the select will not return the other fields.

There are several other issues with your code:

  • It doesn’t insert anything to the temporary table, because you don’t call insert().
  • It uses aggregation without grouping, therefore it can’t ever return more than a single record.

i tried to select the two fields as you told, but it the same result no value returned…

sorry i did not copy the whole code … i already called the tmpItemMCost2Branch.insert() method

i will try your your 2nd solution…

thank martin… your second solution is work…
i group the selected field …