Computed columns and virtual fields in data entities

A data entity can have additional unmapped fields beyond those that are directly mapped to fields of the data sources. There are mechanisms for generating values for unmapped fields:

• Custom X++ code
• SQL executed by Microsoft SQL Server

The two types of unmapped fields are computed and virtual. Unmapped fields always support read actions, but the feature specification might not require any development effort to support write actions.

Computed field

• Value is generated by an SQL view computed column.
• During read, data is computed by SQL and is fetched directly from the view.
• For writes, custom X++ code must parse the input value and then write the parsed values to the regular fields of the data entity. The values are stored in the regular fields of the data sources of the entity.
• Computed fields are used mostly for reads.
• If possible, it's a good idea to use computed columns instead of virtual fields, because they are computed at the SQL Server level, whereas, virtual fields are computed row by row in X++.

Virtual field

• Is a non-persisted field.
• Is controlled by custom X++ code.
• Read and write happens through custom X++ code.
• Virtual fields are typically used for intake values that are calculated by using X++ code and can't be replaced by computed columns.

Properties of unmapped fields

Category	Name	Type	Default value	Behavior
Data	IsComputedField	NoYes	Yes	Yes – The field is synchronized as a SQL view computed column. Requires an X++ method to compute the SQL definition string for the column. The virtual column definition is static and is used when the entity is synchronized. After that, the X++ method is not called at run time. No – The field is a true virtual field, where inbound and outbound values are fully controlled through custom code.
Data	ComputedFieldMethod	String		A static DataEntity method in X++ to build the SQL expression that will generate the field definition. This property is disabled and irrelevant if the property IsComputedField is set to No. The method is required if the property IsComputedField is set to Yes.

Example: Create a computed field

In this example, you add a computed field to the FMCustomerEntity entity. For reads, the field combines the name and address of the customer into a nice format. For writes, your X++ code parses the combined value into its separate name and address values, and then the code updates the regular name and address fields.

1. In Microsoft Visual Studio, right-click your project, and add the existing FMCustomerEntity.
2. In Solution Explorer, right-click the FMCustomerEntity node, and then click Open.
3. In the designer for FMCustomerEntity, right-click the FMCustomerEntity node, and then click New > String Unmapped Field.
   
4. Rename the new field NameAndAddress.
5. Update properties of the NameAndAddress unmapped field, as shown in the following screenshot.
   
6. Go to FMCustomerEntity > Methods. Right-click the Methods node, and then click New. Ensure that the method name matches the DataEntityView Method property value of the unmapped computed field.
7. Paste the following X++ code into the method. The method returns the combined and formatted NameAndAddress value.
    Note

   The server keyword is required.

   private static server str formatNameAndAddress()   // X++
   {
       DataEntityName      dataEntityName= tablestr(FMCustomerEntity);
       List                fieldList = new List(types::String);
       ////Format name and address to look like following
       ////John Smith, 123 Main St, Redmond, WA 98052
       fieldList.addEnd(SysComputedColumn::returnField(DataEntityName, identifierstr(FMCustomer), fieldstr(FMCustomer, FirstName)));
       fieldList.addEnd(SysComputedColumn::returnLiteral(" "));
       fieldList.addEnd(SysComputedColumn::returnField(DataEntityName, identifierstr(FMCustomer), fieldstr(FMCustomer, LastName)));
       fieldList.addEnd(SysComputedColumn::returnLiteral("; "));
       fieldList.addEnd(SysComputedColumn::returnField(DataEntityName, identifierstr(BillingAddress), fieldstr(FMAddressTable, AddressLine1)));
       fieldList.addEnd(SysComputedColumn::returnLiteral(", "));
       fieldList.addEnd(SysComputedColumn::returnField(DataEntityName, identifierstr(BillingAddress), fieldstr(FMAddressTable, City)));
       fieldList.addEnd(SysComputedColumn::returnLiteral(", "));
       fieldList.addEnd(SysComputedColumn::returnField(DataEntityName, identifierstr(BillingAddress), fieldstr(FMAddressTable, State)));
       fieldList.addEnd(SysComputedColumn::returnLiteral(", "));
       fieldList.addEnd(SysComputedColumn::cast(
           SysComputedColumn::returnField(DataEntityName, identifierstr(BillingAddress), fieldstr(FMAddressTable, ZipCode)), "NVARCHAR"));
       return SysComputedColumn::addList(fieldList);
   }
   
   T-SQL for the computed column.
   
   ( Cast (( ( T1.firstname ) + ( N' ' ) + ( T1.lastname ) + ( N'; ' ) +
       ( T5.addressline1 )
       + ( N', ' ) + ( T5.city ) + ( N', ' ) + ( T5.state ) + (
       N', '
       ) +
       ( Cast(T5.zipcode AS NVARCHAR) ) ) AS NVARCHAR(100))
   )
   AS
   NAMEANDADDRESS
   

    Tip

   If you receive error in data entity synchronization because of computed columns, it's easier to come up with the SQL definition in Microsoft SQL Server Management Studio (SSMS) before using it in X++.
8. Rebuild the project.
9. Synchronize the database. Don't forget this step. You can do this by going to Dynamics 365 > Synchronize database > Synchronize.

Example: Create a virtual field

In this example, you add a virtual field to the FMCustomerEntity entity. This field displays the full name as a combination of the last name and first name. X++ code generates the combined value.

1. In the designer for the FMCustomerEntity entity, right-click the Fields node, and then click New > String Unmapped Field.
2. In the properties pane for the unmapped field, set the Name property to FullName.
3. Set the Is Computed Field property to No. Notice that you leave the DataEntityView Methodempty.
   
4. In the FMCustomerEntity designer, right-click the Methods node, and then click Override > postLoad. Your X++ code in this method will generate the values for the virtual field.
5. Paste the following X++ code in for the postLoad override. Notice that the postLoad method returns void.

   public void postLoad()
   {
       super();
       //Populate virtual field once entity has been loaded from database
       //Format full name - "Doe, John"
       this.FullName = this.LastName + ", " + this.FirstName;
   }
   

6. Compile your project.