MSDyn365FO. Cannot sync entity that uses TableId field in the relations.

Some tables use RefRecId and RefTableId fields to build relations with other tables. Hence building data entities for those tables could be not that straight forward exercise. Let’s take WHSFilterTable as an example. We want to build new entity to show customer filter codes. There is already a standard entity, but we will build new one to illustrate the issue. For our entity will join WHSFilterTable and CustTable. Relations between tables:

WHSFilterTable.jpg

Entity’s query will be similar to this:

CustomerWHSFiltersEntity.jpg

It builds perfectly fine but during DB sync we get huge list of errors. I won’t list all of them here. But main one is “System.Data.SqlClient.SqlException (0x80131904): Invalid column name ‘TABLEID’.“,  that happens during trigger creation.  Sync engine generates SQL statement that has TableId field in it, but TableId is not a real field and does not exist in underlying tables.

So what do we do? Because we need that entity and it’s quite common to use RefTableId, so error is not specific to WHSFilterTable.

Skip triggers

If you look at SQL views generated for entities you could notice that quite a few of them do not have triggers at all. Triggers are generated only for entities that support set base operations, change “Enable Set Based SQL Operations” property to “No”, no triggers will be generated and entity will be synchronized without any issues.

Do not use TableId in the relation

Instead of having relation by tableId we can remove it and add range over RefTableId field. Value for this range could be SysQueryRangeUtil method, that simply returns tableNum(CustTable). Value is calculated only once during DB sync and then embedded into the view definition. And that’s what you can see in the standard WHSCustomerProductFilterEntity entity. It uses WHSCustomerProductFilterEntityHelper::getCustomers() method to resolve this particular issue in the standard product.

Flighting

You can use DbSyncEnableTableIdInDataEntityTriggerRelations flighting

INSERT INTO SYSFLIGHTING (FLIGHTNAME, ENABLED, FLIGHTSERVICEID) VALUES (‘DbSyncEnableTableIdInDataEntityTriggerRelations’, 1, 12719367))

It forces sync to use actual values instead of table ids during triggers generation and looks like it was introduced to deal with this particular issue. I have no idea why it’s not documented anywhere and not enabled by default.

MSDyn365FO. Add postTargetProcess method to a Data Entity via extension.

Quite a while ago I wrote a blog post about postTargetProcess method that could be added to a data entity for post processing. Previously, you could add it only to a newly created entity, but now you can extend any entity using CoC.

This method is a bit special because it is not declared on the parent object, but is called via reflection by DMFEntityBase class. Full list of method that work in the same way:

  • defaultCTQuery
  • copyCustomStagingToTarget
  • postGetStagingData
  • preTargetProcessSetBased
  • postTargetProcess

DMFEntityBase uses tableHasStaticMethod() to check if a method is defined and DictDataEntity.callStatic() to call it. In the latest PUs Microsoft added support of CoC methods to Dict* API and reflection methods, so now it is possible to use CoC method in this scenario as well.

Extension is quite simple and straightforward, all you need to do is to declare static method with respective signature and do not call next because this method is not defined on the parent object:

[ExtensionOf(tableStr(MyDataEntity))]
final public class MyDataEntity_Extension
{
    public static void postTargetProcess(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)
    {
        //do stuff
    }

}

AX 7. Process the data after Data Entity import.

It’s a common requirement to perform post actions on records that are imported by Data Entity, for example to invoice created sales order or to post created journal.

To do this we need to add a new method postTargetProcess() to our Data Entity. This method is automatically called by the DMFEntityWriter class in the end of processRecords() method when all records are transferred from staging to target. It is not available from override method drop down on Data Entity because it is static and is called via reflection.

/// <summary
/// Executes the logic once after processing the target data.
/// </summary>
/// <param name= “_dmfDefinitionGroupExecution">
/// The definition group that should be processed.
/// </param>
public static void postTargetProcess(DMFDefinitionGroupExecution _dmfDefinitionGroupExecution)
{
    //check if import job is finished
    if (_dmfDefinitionGroupExecution.StagingStatus == DMFBatchJobStatus::Finished)
    {
        MyStaging myStaging;

        //select all staging records that were processed by current execution job without errors.
        while select myStaging
            where myStaging.DefinitionGroup == _dmfDefinitionGroupExecution.DefinitionGroup
               && myStaging.ExecutionId     == _dmfDefinitionGroupExecution.ExecutionId
               && myStaging.TransferStatus  == DMFTransferStatus::Completed
        {
            //here you can find newly created records and update\post them.
        }
    }
}

Please note that it can be done only in data management scenarios but not via OData because OData updates\inserts records row by row and there is no post event\method to use.