Επεξεργασία

Add custom fields to Microsoft-managed tables

Note

Community interest groups have now moved from Yammer to Microsoft Viva Engage. To join a Viva Engage community and take part in the latest discussions, fill out the Request access to Finance and Operations Viva Engage Community form and choose the community you want to join.

This article describes how to extend existing Microsoft archive scenarios, such as General ledger, Sales order, and Inventory journal, by adding custom fields to live tables and ensuring proper synchronization with history tables and finance and operations data entities.

Overview

Use this scenario when you need to add custom fields to standard Microsoft tables that are already part of an archive scenario. For example, you might add a custom status field to SalesTable or a custom reference field to GeneralJournalAccountEntry.

This scenario involves the following components:

  • Table extensions for live tables
  • Table extensions for history tables
  • Entity extensions for finance and operations data entities
  • Dataverse virtual entities refresh

You don't need to make any code changes. You don't need to modify job contract creator classes. The framework automatically includes your custom fields in archive operations.

Prerequisites

  • Access to Visual Studio with Dynamics 365 finance and operations development tools
  • Development environment with Archive framework deployed
  • Understanding of X++ extensibility patterns
  • System administrator role in Dynamics 365 finance and operations

Add the custom field to live table

Add your custom field to the source table by using a table extension.

Create table extension

To create a table extension, follow these steps:

  1. In Visual Studio, right-click your project. Select Add > New item.
  2. Select Dynamics 365 items > Data model > Table extension.
  3. Name the extension using the pattern: [TableName]_[YourCompany]_Extension
    • Example: SalesTable_Contoso_Extension
    • Example: GeneralJournalAccountEntry_Fabrikam_Extension

Add your custom field

To add your custom field, follow these steps:

  1. Right-click the table extension, select New > Field.
  2. Choose the appropriate field type. For example, String, Integer, Date, Enum.
  3. Configure field properties:
    • In the Label field, enter a descriptive name.
    • In the Help text field, explain the custom field's purpose.
    • Configure Extended Data Type or base type.
    • Set the Mandatory property if necessary.

Example fields:

  • CustomStatus (Enum) - Custom status tracking.
  • CustomDate (Date) - Another date field.
  • CustomReference (String) - External system reference.
  • CustomAmount (Real) - Another calculated amount.

Configure table properties

Important

On the source (live) table, set the property ChangeTrackingEnabled to Yes. This property is required for archive operations to track data changes.

If the Microsoft-managed table doesn't already have this property enabled, you might need to verify with the owning team or check if it's already configured.

Build and synchronize

# Build your model
# Synchronize database to create the field in SQL Server

Learn more in Add fields to tables through extension.

Add custom fields to history table

Mirror the custom field in the corresponding history table so archived data includes your field.

Create history table extension

To create a history table extension, use the following steps:

  1. In Visual Studio, add new item and select Table extension.
  2. Enter a name: [TableName]History_[YourCompany]_Extension.
    • Example: SalesTableHistory_Contoso_Extension.
    • Example: GeneralJournalAccountEntryHistory_Fabrikam_Extension.

Add matching field

To add a matching field, follow these steps:

  1. Add the exact same field with identical properties.
  2. Make sure the Field name, type, and metadata fields match the live table fields.
  3. Don't add indexes to history table extensions.
  4. Don't add unique constraints.

Important

Field matching is critical - the field name, data type, extended data type, and length must be identical between live and history tables. Any mismatch causes archive jobs to fail with schema validation errors.

Build and synchronize

# Build your model
# Synchronize database

During archive operations, the framework copies data from live tables to history tables by using identical field mappings. If fields don't match, the data copy operation fails.

Add custom fields to finance and operations data entity

Ensure the Dynamics 365 finance and operations data entity includes your custom field so it's available for Dataverse virtual entities and long-term retention.

Create entity extension

To create an entity extension, follow these steps:

  1. In Visual Studio, add new item and select Data entity extension.
  2. Enter a name in the format [EntityName]_[YourCompany]_Extension.
    • Example: SalesTableBiEntity_Contoso_Extension.
    • Example: GeneraljournalaccountentryBiEntity_Fabrikam_Extension.

Add data source field

To add the data source field, follow these steps:

  1. In the entity extension designer, expand Data sources.
  2. Find the data source table that matches your live table.
  3. Right-click the data source and select New > Field.
  4. Map the field to your custom field from the source table:
    • Set Data Field to match your custom field name.
    • Set Data Method if you're using computed fields.
  5. Configure field visibility:
    • Set Visible = Yes for the field to appear in the entity.

Verify entity properties on the base entity

Microsoft-managed entities already have the following properties set correctly. Verify they're configured:

  • IsPublic = Yes
  • PublicEntityName = Entity name
  • Is Read Only = Yes
  • Allow Retention = Yes
  • Allow Row Version Change Tracking = Yes
  • Auto Create = Yes (enables automatic Dataverse sync)
  • Auto Refresh = Yes (keeps metadata in sync)

Note

These properties are on the base entity, not your extension. Microsoft-managed entities already have these properties configured. You only need to add your field to the entity extension.

Build and test

# Build your model
# Test entity in Data Management workspace

Refresh virtual entity in Dataverse

Update the Dataverse virtual entity metadata to include your custom field.

If the base entity has Auto Create = Yes and Auto Refresh = Yes set:

  • Virtual entity metadata synchronizes automatically.
  • Changes might take 24 to 48 hours to propagate.
  • No manual action is required.

Manual refresh

If automatic sync doesn't occur or you need an immediate refresh, use one of these methods:

To manually refresh via advanced find, follow these steps:

  1. Go to Advanced find in your Dataverse environment.
  2. Select Available Finance and Operation entities.
  3. Filter the Name field to contain your entity name (for example, generaljournalaccountentrybientity).
  4. From the results, open the record.
  5. Select Refresh.
  6. Save the record.
  7. Wait for refresh to complete.

To refresh using Power Apps Maker Portal, follow these steps:

  1. Go to Power Apps Maker Portal.
  2. Select your environment.
  3. Go to Tables and filter by Available Finance and Operations entities.
  4. Search for your entity (for example, mserp_salestablebiEntity).
  5. Open the entity and trigger refresh.

If the refresh doesn't work, follow the guidance in Virtual entity refresh troubleshooting.

Verify field appears in the entity

To verify the field appears in the entity, follow these steps:

  1. In Power Apps, open the virtual entity.
  2. Go to the Columns tab.
  3. Search for your custom field.
  4. Verify the field type matches your Dynamics 365 finance and operations field.

Test archive and restore

Verify that archive jobs include your custom field data throughout the archive lifecycle. Create a test archive job.

To create a test archive job, follow these steps:

  1. Go to the Dynamics 365 finance and operations archive workspace.
  2. Select the relevant archive type. For example, Sales order archive or General ledger archive.
  3. Create a test archive job with criteria that includes records containing your custom field.
  4. Ensure test data has values in your custom field.
  5. Run the archive job.

Verify data movement

Check the history table.

-- Verify custom field moved to history
SELECT CustomField, * FROM SalesTableHistory WHERE RecId = [TestRecId]
SELECT CustomField, * FROM GeneralJournalAccountEntryHistory WHERE RecId = [TestRecId]

To test the restore operation, follow these steps:

  1. Create a reversal (restore) job for the archived data.
  2. Execute the restore operation.
  3. Verify that the custom field data returns to the live table.
  4. Validate that the field values match the original data.
-- Compare before and after restore
SELECT CustomField, * FROM SalesTable WHERE RecId = [TestRecId]

Validation and troubleshooting

Validate field synchronization:

  • The live table has the custom field.
  • The history table has a matching field with identical properties.
  • The finance and operations data entity extension includes the field.
  • The virtual entity in Dataverse shows the field.
  • Last updated on