You’re offline. This is a read only version of the page.
270
My goal is to add the value of the method CustTrans.custPaymManAmountMST in the report.
Here is the method:
Share
Reportstatic server AmountMST openAmountMST(CustAccount _custAccount, CurrencyCode _currencyCode = '*')
{
CustTrans custTrans;
CustPaymManTrans custPaymManTrans;
;
select sum(AmountMST) from custTrans
where custTrans.AccountNum == _custAccount &&
custTrans.CurrencyCode like _currencyCode
exists join custPaymManTrans
where custPaymManTrans.RefRecId == custTrans.RecId;
return -custTrans.AmountMST;
}
Upon populating the temp table custAgingReportTmp, I noticed that there's an AccountNum field. So there's no problem of just joining the table CustTrans to the statement.
insert_recordset custAgingReportTmp
(fields here)
select
(fields here)
from tmpAccountSum
join SortOrder
from custVendTransAging
where tmpAccountSum.AccountNum == custVendTransAging.AccountNum
join AmountMST
from custTrans
where tmpAccountSum.AccountNum == custTrans.AccountNum
I added the custTrans and I successfully displayed it in the report. However, the AmountMST I need to get, should check if it exists in this table another table (as seen on the custPaymManAmountMST method) which is:
exists join custPaymManTrans where custPaymManTrans.RefRecId == custTrans.RecId;
but when I add this exists join syntax above, I get 0 returns.
Is there anyway I can apply the exists join to that I can get the correct AmountMST? or my approach is incorrect?
*This post is locked for comments
Thanks for your input Martin! Will try creating a view.
Let me try to formulate your problem, if I can.
It seems you want records from tmpAccountSum and custVendTransAging regardless of whether there is a corresponding record in custPaymManTrans table, but that's not what you get when you join these tables. You must remove the join with custPaymManTrans.
You've added the join to get the summarized amounts, but that's a different query! In T-SQL, you would use a sub-query to calculate the sum.
In AX, you can create a view returning account numbers and summarized amounts and join this view, for instance.
I can display values of the corresponding AmountMST for every account in the report when I added custTrans and in report it is placed as [Sum(AmountMST)].
However I just need to check if the AmountMST record I'm getting exist in custPaymManTrans..
I'm quite having a hard time trying to explain.. but I just really want to know if there's any workaround/way where I can check if the custtrans records I'll be joining exists in the table custPaymManTrans
I updated the tag, it's AX 2012.
If I simplify your query, we're talking about this:
All responses (
Answers (
select tmpAccountSum
join custVendTransAging
where custVendTransAging.AccountNum == tmpAccountSum.AccountNum
join custTrans
where custTrans.AccountNum == tmpAccountSum.AccountNum
It's trying to join all CustTrans for the given account number, which is very different thing than the method above, which summarize AmountMST from multiple transactions. Don't you see it as a problem?
By the way, you have another thread and said this is a continuation, but you've selected a different version tag for this thread. Can you please confirm your version of AX?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156