jemt22
March 18, 2016, 7:10am
1
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?
jemt22
March 21, 2016, 7:28am
3
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
jemt22
March 21, 2016, 7:42am
4
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?
jemt22
March 21, 2016, 7:46am
6
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.
jemt22
March 21, 2016, 8:25am
9
i tried to select the two fields as you told, but it the same result no value returned…
jemt22
March 21, 2016, 8:27am
10
sorry i did not copy the whole code … i already called the tmpItemMCost2Branch.insert() method
i will try your your 2nd solution…
jemt22
March 21, 2016, 8:53am
11
thank martin… your second solution is work…
i group the selected field …