Views and computed columns are sometimes really useful to get data from Dynamics with performance and efficiency.
For the developer, this is also something harder to work with views rather than X++ code because SQL works with a relational and non dynamic execution.
Let's say we have a view on customer table, implemented with a good performance and you have a change request asking to add a new field with a "1-0,N" cardinality relation between the root data source (CustTable) and the data to retrieve (so, for one customer I can retrieve 1 or more addresses from this type, but maybe I can't find one, so this join is an Outer Join query).
An X++ implementation of this requirement will be easy to do, using a virtual field. But for performance, it could be helpful to use SQL through the view using a computed column.
Basics of computed columns in D365 F&O for memory :
Computed columns are SQL executed by Microsoft SQL Server. The related method of a computed column is not running the calculation but it writes the SQL Code during the synchronization to create the SQL Statement use of this field in the query. That's why a computed column method is returning a primitive type (int, str etc...) and is a static method (no context available for this execution during synchronization).
Imagine in our example, you can't implement an inner join in your existing query because you don't know if you could have a value for your new field and also you can't implement an outer join to avoid duplicating lines on the root datasource. If you can't define a unique key to retrieve the data, a "Select TOP 1" subquery should be a means to reach your goal.
You can see a sample of the syntax below to retrieve for instance a particular address of a customer, selected on a specific criteria (here this is the name of the address). If no address is found, the value will be simply empty without any Outer Join inconvenience.
/// <summary>
/// Get the Country of one NAMED Address of fetched Sales order
/// </summary>
public static server str getFirstMyNameAddressRefRecid()
{
//Target : select TOP 1 logisticsPostalAddress.RecId
// from logisticsPostalAddress
// inner join DirPartyLocation on DirPartyLocation.LOCATION = logisticsPostalAddress.Location
// inner join CustTable on CustTable.Party = DirPartyLocation.PARTY
// inner join DIRPARTYLOCATIONROLE on DIRPARTYLOCATIONROLE.PARTYLOCATION = DirPartyLocation.Recid
// inner join LogisticsLocationRole on LogisticsLocationRole.Recid = DIRPARTYLOCATIONROLE.LOCATIONROLE AND LogisticsLocationRole.NAME = 'MyName'
// where salesTable.CUSTACCOUNT = view.CustAccount and salesTable.DATAAREAID = view.dataAreaId
SysDictTable logisticsPostalAddress = new SysDictTable(tableNum(logisticsPostalAddress));
SysDictTable DirPartyLocation = new SysDictTable(tableNum(DirPartyLocation));
SysDictTable custTable = new SysDictTable(tableNum(CustTable));
SysDictTable DirPartyLocationRole = new SysDictTable(tableNum(DirPartyLocationRole));
SysDictTable LogisticsLocationRole = new SysDictTable(tableNum(LogisticsLocationRole));
DictView MyDirPartyMyNameAddress = new DictView(tableNum(MYDirPartyMyNameAddress));
str s = strFmt('SELECT TOP 1 %2.%1 FROM %2 '+ //LOGISTICSLOCATION
'INNER JOIN %5 ON %2.%3 = %5.%4 '+ //DirPartyLocation
'INNER JOIN %8 ON %8.%7 = %5.%6 '+ // CustTable
'INNER JOIN %12 ON %12.%13 = %5.%18 '+ //DIRPARTYLOCATIONROLE
'INNER JOIN %15 ON %15.%16 = %12.%19 AND %15.%17 = \'MyName\' '+ //LogisticsLocationRole
'WHERE %8.%9 = %10 AND %8.%20 = %11', //Criteria
logisticsPostalAddress.fieldName(fieldNum(logisticsPostalAddress, RecId), DbBackend::Sql), // 1
logisticsPostalAddress.name(DbBackend::Sql), // 2
logisticsPostalAddress.fieldName(fieldNum(logisticsPostalAddress, Location), DbBackend::Sql), //3
DirPartyLocation.fieldName(fieldNum(DirPartyLocation, Location), DbBackend::Sql), // 4
DirPartyLocation.name(DbBackend::Sql), // 5
DirPartyLocation.fieldName(fieldNum(DirPartyLocation, Party), DbBackend::Sql), //6
custTable.fieldName(fieldNum(custTable, Party), DbBackend::Sql), // 7
custTable.name(DbBackend::Sql), // 8
custTable.fieldName(fieldNum(custTable, AccountNum), DbBackend::Sql), //9
MyDirPartyMyNameAddress.computedColumnString(tableStr(SalesTable), fieldStr(SalesTable, CustAccount), FieldNameGenerationMode::WhereClause), //10
MyDirPartyMyNameAddress.computedColumnString(tableStr(SalesTable), fieldStr(SalesTable, DataAreaId), FieldNameGenerationMode::WhereClause), //11
DirPartyLocationRole.name(DbBackend::Sql), // 12
DirPartyLocationRole.fieldName(fieldNum(DirPartyLocationRole, PartyLocation), DbBackend::Sql), //13
DirPartyLocationRole.fieldName(fieldNum(DirPartyLocationRole, RecId), DbBackend::Sql), //14
LogisticsLocationRole.name(DbBackend::Sql), // 15
LogisticsLocationRole.fieldName(fieldNum(LogisticsLocationRole, Recid), DbBackend::Sql), //16
LogisticsLocationRole.fieldName(fieldNum(LogisticsLocationRole, Name), DbBackend::Sql), //17
DirPartyLocation.fieldName(fieldNum(DirPartyLocation, RecId), DbBackend::Sql), // 18
DirPartyLocationRole.fieldName(fieldNum(DirPartyLocationRole, LocationRole), DbBackend::Sql), //19
custTable.fieldName(fieldNum(custTable, DataAreaId), DbBackend::Sql)); //20
return strFmt('ISNULL((%1), \'\')', s);
}
Advice for implementation and maintenance :Write your SQL Statement in comment and on every lines of your code (number of your variables).
Don't forget also the dataareaid managed normally by the system won't be here added automatically. So you need to manage it.
