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.

D365FO. “The authentication process was not successful. Please contact your system administrator.” error during Connect to Lifecycle Services setup.

As we know, there are hundreds of task recordings available out of the box in LCS library. Before first use, system administrator should authorize D365FO to access LCS and often it is not possible due to the old bug. When system administrator goes to Settings -> Task recorder -> Play recording as guide -> Open from Lifecycle Services and clicks “Select the Lifecycle Services library” “Connect to Lifecycle Services” dialog pops up.

Connect to Lifecycle Services

There is a link “Click here to connect to Lifecycle Services” and when administrator clicks on it he may receive “The authentication process was not successful. Please contact your system administrator.” error.

It’s is caused by empty value of “LCS.GettingStartedLibrary” parameter in web.config file.

To fix it go to K:\AosService\WebRoot and edit web.config file. Find LCS.GettingStartedLibrary tag and set value to any integer that is greater than 0, save file and restart IIS. Don’t forget to backup web.config before doing any changes.

WebConfigLCSGettingStarted

Probably LCS should put BPM library id there, however, it works with any random positive integer as well. To find BMP project id, go to LCS, open your project and click on “Business process modeler” tile. Then select library that contains task recording.  In the URL of the page grab value of FWK parameter

LCSBPMURL.png

Hopefully, this bug will be fixed soon and we won’t have to play with web.config file anymore.

D365FOE. Issue with enums that have “Use Enum Value” property set to “No”.

Recently we have noticed incorrect behavior of enums that have “Use Enum Value” set to “No” and have gaps in enum values. These enums use values from properties when populated from user interface, however, X++ code uses generated values, that cause inconsistent behavior and data corruption.

To illustrate this issue, we will create new enum with two values: 0 and 10. Also we need to set “Use Enum Value” property to “No”.

MyEnum

MyEnumProperties

Zero:

MyEnumZero

Ten:

MyEnumTen

Also, we need simple table that has only one field, form to populate this field from UI and job to create data from X++.

Table:

MyTable

Form:

MyForm

Runnable class:

RunnableClass

Let’s run the class and check data in DB and UI.

In DB value is equal to value from enum properties – 10:

MyEnumInDBFromX++

On the form we can see empty value:

MyFormInsertFromX++

Let’s create new record using UI and check what is saved to DB.

MyFormInsertFromUI

MyEnumInDBFromUI

As you can see, value is equal to 1. For these enums value entered from UI will never be equal to value entered from X++ code, so if it is created in X++ user would see empty values, if it is created from UI X++ comparison like:

If (myTable.MyEnum == MyEnum::Ten)  

would always return false, even if user see “Ten” on UI, because values are different.

It was a common practice to have gaps in enum values for different layers, so you could avoid conflicts when new values are created in new version or a hotfix. AssetTransType is a good example, where localization related values start from 100, because they used to be on another layer.

However, most of standard enums use enum values, so probably that is why this bug was not spotted before.

As a good citizen, I filled a bug, but I have a sneaky suspicion that it won’t be fixed soon, so be aware of this volatile mix, try to avoid changing “Use Enum Value” property and review your enums in case you have one!

Query::insert_recordset() ignores XDS policy.

Recently we did a customization to create custom XDS policy and found out that Query::insert_recordset() ignores it, so all data is selected.

Simple workaround is to rewrite Query::insert_recordset() to while(queryRun.Next()).

However no one really wants to modify bunch of standard code that uses this method so connect issue was created. Feel free to vote for it.

It could be reproduced in AX 2012 and AX 7 as well.