Add a Computed Column to a View [AX 2012]

SysComputedColumn  is the class which has all the implementation . we can put cross reference on the required method to define our own custom computed column to store calculated values.


create a "String computed column " and set View method and String size property.








 
IF conditions sample:


create a new method in view and assign this method to string computed column view method.




private static server str displayActualDeliveryDate()
{
return SysComputedColumn::if(
SysComputedColumn::equalExpression(
SysComputedColumn::comparisonField(
tableStr(afmPOConfirmView),identifierStr(afmEDIPurchLine_1),fieldStr(afmEDIPurchLine,ActualDeliveryDate)),
SysComputedColumn::comparisonLiteral(dateNull())),
SysComputedColumn::returnLiteral(''),
SysComputedColumn::cast(SysComputedColumn::returnField(tableStr(afmPOConfirmView),identifierStr(afmEDIPurchLine_1),fieldStr(afmEDIPurchLine,ActualDeliveryDate)), 'NVARCHAR'));
//return SysComputedColumn::cast(SysComputedColumn::returnField(tableStr(afmPOConfirmView),identifierStr(afmEDIPurchLine_1),fieldStr(afmEDIPurchLine,ActualDeliveryDate)),'NVARCHAR');
}



 From Sql Sub-Query:


Select case is performed in below query to perform IF conditions. Cast and convert are used to convert from one data type to another data type.


private static server str displayActualDeliveryDate()
{
str headerstaging;
str tmpQuery;
;
headerstaging = SysComputedColumn::returnField(tableStr(afmPOASNView),identifierStr(afmEDIPurchASNHeaderStaging_1),fieldStr(afmEDIPurchASNHeaderStaging,RecId));



tmpQuery = 'select case when count(distinct(ACTUALDELIVERYDATE)) > 1 then ' + "'" + "*" + "'" + 'else convert(VARCHAR(10),max(ACTUALDELIVERYDATE),101) END as ActualDeliverydate from afmEDIPurchASNLineStaging where afmEDIPurchASNLineStaging.AFMEDIPURCHASNHEADERSTAGING = ' + headerstaging;



//tmpQuery = 'select case when count(distinct(ACTUALDELIVERYDATE)) > 1 then ' + "'" + "*" + "'" + 'else CAST(max(ACTUALDELIVERYDATE) AS nvarchar) END as ActualDeliverydate from afmEDIPurchASNLineStaging where afmEDIPurchASNLineStaging.AFMEDIPURCHASNHEADERSTAGING = ' + headerstaging;
return tmpQuery;
}


Standard Link from MSDN:


https://msdn.microsoft.com/EN-US/library/gg845841.aspx

__


code example:


https://msdn.microsoft.com/EN-US/library/gg846293.aspx