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.