Dynamics Community Forum Thread Details

View with a conditional computed column

Is it possible to create a computed column in a view where, based on a condition, I can select the return value from different tables.

Specifically, I'm looking at the AssetDisposal view and I am trying to get the reason the asset was disposed. Depending on the BookType field in the AssetDisposal view, the disposal reason is stored in either the AssetDepBookTrans table or the AssetTrans table.

I've created a display method on the view which looks like this and works great for displaying the value I'm looking for:

However, I need to add a range to a query on this field so I need to make it a computed column instead of a display method. Since the computed column method needs to be static, I won't be able to use "this.BookType" to drive my condition. I know I can do something with SysComputedColumn::IF(...) to check the value of the BookType but I'm unsure is it possible to select a record from AssetDepBookTrans or AssetTrans within the SysComputedColumn::IF? I didn't see a way to do that. Any suggestion would be greatly appreciated.

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:

CREATE VIEW "DBO".ASSETDISPOSAL AS

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!

Quick Links

Leaderboard > Microsoft Dynamics AX (Archived)