To update on what I have tried, I have successfully created two computed fields in my view, one which gives the disposal reason for records in AssetDisposal view with BookType == 0 and another computed column which gives the disposal reason for records in AssetDisposal view where BookType == 1. Those methods look like this:
private static server str getAssetTransDisposalReason()
{
str bookId = SysComputedColumn::returnField(tableStr(AssetDisposal), identifierStr(AssetBookMerge), identifierStr(BookId));
str sqlQuery = 'select Reason from ReasonTable join AssetTrans on AssetTrans.REASONREFRECID = ReasonTable.RecId where AssetTrans.BOOKID = %1 and (AssetTrans.TRANSTYPE = 8 or AssetTrans.TransType = 9)';
return(strFmt(sqlQuery, bookId));
}
private static server str getAssetDepBookTransDisposalReason()
{
str bookId = SysComputedColumn::returnField(tableStr(AssetDisposal), identifierStr(AssetBookMerge), identifierStr(BookId));
str sqlQuery = 'select Reason from ReasonTable join AssetDepBookTrans on AssetDepBookTrans.REASONREFRECID = ReasonTable.RecId where AssetDepBookTrans.DEPRECIATIONBOOKID = %1 and (AssetDepBookTrans.TRANSTYPE = 8 or AssetDepBookTrans.TransType = 9)';
return(strFmt(sqlQuery, bookId));
}
I'd like to create a single computed column which uses a conditional statement to compute these values into a single column. This way I can range/filter the view data on a single fields. I've tried the following approach which will compile but SQL throws an exception to AX when I try to save/synchronize the view:
private static server str getAssetDisposalReason()
{
str bookId = SysComputedColumn::returnField(tableStr(AssetDisposal), identifierStr(AssetBookMerge), identifierStr(BookId));
str sqlQueryAssetDepBook = 'select Reason from ReasonTable join AssetDepBookTrans on AssetDepBookTrans.REASONREFRECID = ReasonTable.RecId where AssetDepBookTrans.DEPRECIATIONBOOKID = %1 and (AssetDepBookTrans.TRANSTYPE = 8 or AssetDepBookTrans.TransType = 9)';
str sqlQueryAssetTrans = 'select Reason from ReasonTable join AssetTrans on AssetTrans.REASONREFRECID = ReasonTable.RecId where AssetTrans.BOOKID = %1 and (AssetTrans.TRANSTYPE = 8 or AssetTrans.TransType = 9)';
return SysComputedColumn::if(
SysComputedColumn::equalExpression( SysComputedColumn::comparisonField(identifierStr(AssetDisposal), identifierStr(AssetBookMerge), identifierStr(BookType)),
SysComputedColumn::comparisonLiteral(0)),
strFmt(sqlQueryAssetTrans, bookId),
strFmt(sqlQueryAssetDepBook, bookId));
}
The SQL Statement for the view is:
SELECT T1.NAME AS NAME,
T1.DATAAREAID AS DATAAREAID,
T1.PARTITION AS PARTITION,
T1.RECID AS RECID,
T2.DATAAREAID AS DATAAREAID#2,
T2.PARTITION AS PARTITION#2,
T2.ASSETGROUP AS ASSETGROUP,
T2.ASSETID AS ASSETID,
T2.BOOKID AS BOOKID,
T2.BOOKTYPE AS BOOKTYPE,
T2.DISPOSALDATE AS DISPOSALDATE,
T2.STATUS AS STATUS,
(CAST ((CASE WHEN T2.BOOKTYPE = 0 THEN
select Reason from ReasonTable join AssetTrans on AssetTrans.REASONREFRECID = ReasonTable.RecId where AssetTrans.BOOKID = T2.BOOKID and (AssetTrans.TRANSTYPE = 8 or AssetTrans.TransType = 9)
ELSE
select Reason from ReasonTable join AssetDepBookTrans on AssetDepBookTrans.REASONREFRECID = ReasonTable.RecId where AssetDepBookTrans.DEPRECIATIONBOOKID = T2.BOOKID and (AssetDepBookTrans.TRANSTYPE = 8 or AssetDepBookTrans.TransType = 9)
END) AS NVARCHAR(10))) AS VIEWFIELD1
FROM ASSETTABLE T1
CROSS JOIN ASSETBOOKMERGE T2
WHERE (T1.ASSETID=T2.ASSETID AND (T1.DATAAREAID = T2.DATAAREAID) AND (T1.PARTITION = T2.PARTITION))
Does anyone see a workaround for this or where I'm going wrong?
From what I can tell, this is failing as T2.BOOKID is not in scope inside the CASE statement generated by AX. I'm not sure how to resolve this as that statement is generated by the SysComputedColumn::IF().
Thanks again, any input is greatly appreciated!
