Add custom tables to standard archive scenarios

This article describes how to add related custom tables to existing Microsoft archive scenarios. For example, you can add a custom settlement table to the General Ledger archive scenario or add custom shipment tracking tables to the Sales Order archive scenario.

Overview

This scenario applies when you have custom tables that are related to Microsoft-managed tables and should be archived together. The custom tables have foreign key relationships to the standard tables.

Example scenarios include:

• Custom ledger settlement table related to GeneralJournalAccountEntry
• Custom shipment tracking table related to SalesTable
• Custom quality inspection table related to InventJournalTable

The components involved in this scenario include:

• Custom live tables (new)
• Custom history tables (new)
• Custom finance and operations data entities (new)
• Job contract creator extension (code required)
• Dataverse configuration

Code required - extend the scenario's job contract creator class to add your tables to the archive scope.

Prerequisites

• Access to Visual Studio with Dynamics 365 finance and operations development tools.
• Development environment with Archive framework deployed.
• Understanding of parent-child table relationships.
• Understanding of job contract builder API.
• System Administrator role in Dynamics 365 finance and operations.

Create custom live table

Create your custom transaction table that you include in the archive scope.

Design table structure

Before creating the table, identify:

1. Parent table: Which Microsoft table does this table relate to? (for example, GeneralJournalAccountEntry, SalesTable, InventJournalTable)
2. Relationship type: Is it one-to-one or one-to-many?
3. Foreign key: Which field links to the parent table?
4. Business data: What other fields do you need?

Create table in Visual Studio

1. Right-click the project, and then select Add > New item > Table.
2. Name your table using your naming convention:
   • Example: CustomLedgerTransSettlement
   • Example: CustomShipmentTracking
   • Example: CustomQualityInspection

Add required fields

Foreign key to parent table:

• Field name convention: [ParentTable]RecId (for example, TransRecId, SalesRecId)
• Type: Int64
• Relation to parent table's RecId

Segregation fields if needed(match parent table):

• DataAreaId (EDT: DataAreaId) - For legal entity segregation
• Partition (EDT: PartitionKey) - For multitenant scenarios

Business-specific fields:

• Add your custom transaction fields
• Follow Dynamics 365 finance and operations field naming conventions
• Use appropriate Extended Data Types (EDTs)

Add relation to parent table

1. Expand Relations in table designer.
2. Right-click, select New, and then select Relation.
3. Configure relation:
   • Name: Descriptive name (for example, GeneralJournalAccountEntry).
   • Related Table: Parent table name.
   • Cardinality: ZeroMore (typically).
4. Add relation constraint:
   • Field: Your foreign key field (for example, TransRecId).
   • Related Field: RecId on parent table.

Example table structure:

CustomLedgerTransSettlement
├── RecId (Int64, Primary Key)
├── TransRecId (Int64, Foreign Key to GeneralJournalAccountEntry)
├── DataAreaId (DataAreaId)
├── Partition (PartitionKey)
├── SettlementDate (TransDate)
├── SettlementAmount (AmountMST)
└── SettlementStatus (Enum)

Configure table properties

1. In Visual Studio, select your live table.
2. In the Properties window, find ChangeTrackingEnabled.
3. Set the value to Yes.

Add archive criteria index (Required)

This index is critical for archive job performance and validation.

<AxTableIndex>
    <Name>ArchiveCriteriaIdx</Name>
    <Fields>
        <AxTableIndexField>
            <DataField>DataAreaId</DataField><!-- Match parent segregation if needed-->
        </AxTableIndexField>
        <AxTableIndexField>
            <DataField>TransRecId</DataField><!-- Foreign key for JOIN -->
        </AxTableIndexField>
    </Fields>
    <IncludedColumns>
        <AxTableIndexIncludedColumn>
            <DataField>RecId</DataField><!-- If not clustered key -->
        </AxTableIndexIncludedColumn>
        <AxTableIndexIncludedColumn>
            <DataField>SysRowVersion</DataField>
        </AxTableIndexIncludedColumn>
        <AxTableIndexIncludedColumn>
            <DataField>SysDataStateCode</DataField>
        </AxTableIndexIncludedColumn>
    </IncludedColumns>
</AxTableIndex>

Add reconciliation index for long-term retention (required)

<AxTableIndex>
    <Name>ArchiveSysVersionIdx</Name>
    <Fields>
        <AxTableIndexField>
            <DataField>SysDataStateCode</DataField>
        </AxTableIndexField>
        <AxTableIndexField>
            <DataField>SysRowVersion</DataField>
        </AxTableIndexField>
    </Fields>
</AxTableIndex>

Create history table

To create the corresponding history table for archived data storage, follow these steps:

1. Right-click the project, and then select Add > New Item > Table.
2. Enter a name: [YourTable]History.
   • Example: CustomLedgerTransSettlementHistory
   • Example: CustomShipmentTrackingHistory

Mirror all fields from live table

Copy all fields from your live table except:

• SysRowVersion (system-managed)
• SysDataState (system-managed)

All other fields must be identical:

• Same field names
• Same data types
• Same EDTs
• Same lengths (for strings)

Configure history table properties

History tables don't require unique indexes from the live table.

Add indexes for criteria fields

Add indexes on fields used in archive criteria and queries:

<AxTableIndex>
    <Name>TransRecIdIdx</Name>
    <Fields>
        <AxTableIndexField>
            <DataField>TransRecId</DataField>
        </AxTableIndexField>
    </Fields>
</AxTableIndex>

<AxTableIndex>
    <Name>SettlementDateIdx</Name>
    <Fields>
        <AxTableIndexField>
            <DataField>SettlementDate</DataField>
        </AxTableIndexField>
    </Fields>
</AxTableIndex>

These indexes enable efficient querying of archived data and are required for reversal job scheduling to work properly.

Create finance and operations data entity

To create a finance and operations data entity to enable Dataverse virtual entity for long-term retention, follow these steps:

1. Right-click the project, and then select Add > New Item > Data Entity.
2. Enter a name: [YourTable]BiEntity
   • Example: CustomLedgerTransSettlementBiEntity
   • Example: CustomShipmentTrackingBiEntity

Configure primary data source

1. In entity designer, set Data Sources:
   • Set Primary Data Source to your live table.
   • Ensure all fields are included.
2. Set field visibility:
   • Set all fields to Public visibility.
   • Keep fields that are inaccessible externally as public for archive.

Configure critical entity properties

Set these properties for archive and LTR entities:

Enable rowversion support

For more information, see Entity change tracking.

Build and test entity

# Build your model
# Test in Dynamics 365 finance and operations:
# - Navigate to Data Management
# - Export/Import using your entity
# - Verify all fields are accessible

Publish virtual entity to Dataverse

Make your entity available in Dataverse for long-term retention by using one of the following methods:

1. Automatic publishing (recommended) - When you set Auto Create to Yes:

   • The virtual entity automatically publishes after the first entity sync.
   • Automatic publication might take 24 to 48 hours.
   • No manual action is required.

2. Manual publishing - If you need immediate publishing or automatic publishing didn't work:

• Go to Power Platform Admin Center.
• Select your environment.
• Go to Settings > Product > Features.
• Find Dynamics 365 Finance and Operations Virtual Entities.
• Search for your entity.
• Select Enable to publish.

Refresh virtual entity metadata (if needed)

If you need to refresh entity metadata after making changes, use one of the following options:

1. Manual refresh via advanced find:
   • Go to Advanced Find in your Dataverse environment.
   • Select Available Finance and Operation entities.
   • Filter Name to include your entity name (for example, customledgertranssettlementbientity).
   • From the results, open the record.
   • Select the Refresh checkbox.
   • Save the record.
2. Use the Power Apps Maker Portal:
   • Go to Power Apps Maker Portal.
   • Go to Tables and find your entity.
   • Open the entity and trigger a refresh.

For more information, see Virtual entity refresh troubleshooting.

Configure change tracking and LTR

Third parties (ISVs, partners, and customers) use two approaches to configure entities for long-term retention:

• Manual configuration - Recommended for most scenarios - Use this simpler approach for most implementations. Manually enable change tracking and LTR for each entity in Power Apps Maker.

1. Go to Power Apps Maker Portal.
2. Select your environment > Tables.
3. Find your virtual entity (for example, mserp_customledgertranssettlementbientity).
4. Enable change tracking and long-term retention for the entity.

For more information, see:

• Enable a table for long-term retention.
• Enable change tracking for entities.

Automated solution deployment (Advanced)

Build Dataverse solutions that package your entity configurations for automated deployment across multiple environments. For complete instructions on building and deploying Dataverse solutions, see Configure Dataverse for long-term retention.

Extend job contract to include custom table

Modify the archive job contract to include your custom table in the archive scope.

Locate existing job contract creator

Find the contract creator class for the scenario you're extending:

• General ledger: LedgerArchiveAutomationJobRequestCreator
• Sales order: SalesOrderArchiveAutomationJobRequestCreator
• Inventory journal: InventJournalArchiveAutomationJobRequestCreator

Create extension in your customer-owned model

Create this extension in your customer-owned model. Don't create it in the BusinessIntelligence model.

1. In your model project, create a new class.
2. Name: [ExistingCreator]_[YourModel]_Extension.
3. Add the extension attribute.

using Microsoft.Dynamics.Archive.Contracts;

[ExtensionOf(classStr(LedgerArchiveAutomationJobRequestCreator))]
public final class LedgerArchiveAutomationJobRequestCreator_CustomExtension
{
    // Extension logic here
}

Extend createPostJobRequest method

Use Chain of Command to extend the method that builds job contracts:

public ArchiveJobPostRequest createPostJobRequest(var _criteria)
{
    // Call base implementation to get existing contract
    ArchiveJobPostRequest postRequest = next createPostJobRequest(_criteria);
    
    // Add your custom table
    postRequest = this.addCustomTableForLongTermRetention(postRequest, _criteria);
    
    return postRequest;
}

Implement method to add custom table

private ArchiveJobPostRequest addCustomTableForLongTermRetention(
    ArchiveJobPostRequest _postRequest,
    var _criteria)
{
    ArchiveServiceArchiveJobPostRequestBuilder builder = 
        ArchiveServiceArchiveJobPostRequestBuilder::constructFromArchiveJobPostRequest(_postRequest);

    builder.addSourceTableForLongTermRetention(
        ArchiveServiceSourceTableConfiguration::newForChildSourceTable(
            tableStr(CustomLedgerTransSettlement),
            tableStr(CustomLedgerTransSettlementHistory),
            tableStr(CustomLedgerTransSettlementBiEntity),
            tableStr(GeneralJournalAccountEntry)))
        .addJoinCondition(
            tableStr(CustomLedgerTransSettlement),
            fieldStr(CustomLedgerTransSettlement, TransRecId),
            fieldStr(GeneralJournalAccountEntry, RecId))
        .addDataAreaIdWhereCondition(tableStr(CustomLedgerTransSettlement), _criteria.DataAreaId)
        .addPartitionWhereCondition(tableStr(CustomLedgerTransSettlement));

    return builder.finalizeArchiveJobPostRequest();
}

Implementation points

• Parent table specification:
  • Must match exactly the parent table name in the scenario.
  • Typos in parent table name cause validation failures.
• JOIN conditions:
  • Must match actual foreign key relationships.
  • Use the table name, child field, and parent field.
• WHERE conditions:
  • Must include the same segregation fields as parent, for example, DataAreaId.
  • Use addDataAreaIdWhereCondition() and addPartitionWhereCondition() where applicable.
• Entity name:
  • Use the finance and operations entity that corresponds to the live table.

Test archive with custom table

Verify the custom table is properly included in archive jobs.

Create test data

1. Create parent table record, such as a journal entry.
2. Create related custom table record with foreign key reference.
3. Ensure segregation fields match, like DataAreaId.
4. Add meaningful data to test fields.

Create test archive job

1. Go to the Dynamics 365 finance and operations archive workspace.
2. Select the archive type you extended.
3. Create archive job with criteria that includes your test data.
4. Review job detail to verify table count includes your custom table.

Verify job contract

Check archive service logs for job creation:

• Verify custom table appears in job contract.
• Confirm JOIN conditions are correct.
• Validate WHERE conditions match criteria.

Test data movement

To execute archive job, follow these steps:

1. Run the archive job.
2. Monitor job progress.
3. Verify the job completes successfully.

Verify data in history table:

-- Check custom table data moved to history
SELECT * FROM CustomLedgerTransSettlementHistory
WHERE TransRecId = [TestParentRecId]

-- Verify parent-child relationship maintained
SELECT c.*, p.* 
FROM CustomLedgerTransSettlementHistory c
INNER JOIN GeneralJournalAccountEntryHistory p ON c.TransRecId = p.RecId

Verify data in managed data lake:

• Go to Dataverse managed data lake.
• Find CSV files for your custom table.
• Verify records are present.
• Check field values are correct.

Test restore operation

1. Create reversal job for archived data.
2. Run the restore job.
3. Verify custom table data returns to live table.
4. Validate parent-child relationships are intact.

SELECT * FROM CustomLedgerTransSettlement WHERE TransRecId = [TestParentRecId]

Related articles

• Archive customization overview.
• Scenario 1: Add custom fields to Microsoft-managed tables.
• Scenario 3: Build your own custom archive scenario.
• Configure Dataverse for long-term retention.