Computed column and virtual field in D365

 

Computed column and virtual field:

Requirement: Display methods needed in entity/view
Solution: There are two different approaches for adding additional unmapped fields beyond those that are directly mapped to fields of the data sources.
1.       Computed column (SQL query executed by Microsoft SQL Server)
2.       Virtual Field (Custom X++ code)
 
·      Computed Column:
The computed column method returns a T-SQL query. It hits during database synchronization and is written to the database. In runtime no x++ code is executed, instead, your T-SQL query is executed inside the database. It is mostly used for reads/exports. Performance wise it is better than virtual methods.

Equivalent X++ query:

select sum (amount) from consignmentProductReceiptJournalLine
          where consignmentProductReceiptJournalLine.ReplenishmentOrderLine == this. RecId.

Add any unmapped field and set its properties accordingly.

 

 

example with join:

Equivalent X++ query:

select sum(qty) from inventJournalTrans
            join InventJournalTable
            where InventJournalTable.JournalId == InventJournalTrans.JournalId 

&& InventJournalTable.posted == NoYes: Yes

&& inventJournalTrans.envReplenishmentOrderNumber == this. ReplenishmentOrderNumber 

&& inventJournalTrans.JournalType == InventJournalType: OwnershipChange 

&& inventJournalTrans.ItemId == this. ItemId.

                 

·      Virtual field:

Data entities are saved as a view on the back end in SQL, but we cannot see virtual fields in SQL as virtual fields are not persistent. We can write and debug custom X++ code. It can be used for both read and write operations. Virtual field cannot be used in views, but it can be used on data entities.

Add an unmapped field and set its properties

 

Override the post-load method and write your X++ code logic.

 

Now once you rebuild your code and try to export the data entity you will see this field will be populated in the data entity.

Don’t forget to re-generate target mapping.

For more examples DirPartyBaseEntity