Experience with workarounds for mixed keys

BlueInTheSky2

Hello everybody,

in AL there is the problem that you can only create keys in TableExtensions with fields from the TableExtension itself.
It is therefore not possible to use fields from the original table in a new key of the TableExtension, e.g. in a new key of a TableExtension that extends the table Item, you cannot use any fields from table Item (e.g. 10 type, 16 statistics group, 31 creditor number, ...).
This problem is also outlined and discussed here: https://github.com/microsoft/AL/issues/1054

Although it is currently theoretically possible to extend the standard original table (e.g. 27 item) for the OnPremise variant, this has already been discontinued in the future.

I would be very interested to know what experiences have been made with workarounds for this problem, especially with the following 2 workarounds:

• 1st workaround: shadow fields

Create the required fields in the original table as shadow fields and use Insert / Modify / Rename-Subscriber to ensure that these are kept in sync with the original fields.
In this case, mixed keys can be created again in the table extension via the shadow fields.
Advantage: It is only necessary to replace the original fields with the shadow fields in the key calls used and no further code modification is necessary.
Disadvantage: The data of the shadow fields are of course saved for the second time, so they use additional storage space. In addition, additional effort is required to maintain the shadow fields.

In the following post it seems as if Microsoft is also considering making this available in a more technical, general way via "platform-managed field mirroring for fields needed in new indexes": https://github.com/microsoft/AL/issues/730#issuecomment-337013729

• 2nd workaround: queries

Use of queries instead of keys
This workaround is e.g. outlined here: https://www.adv-usa.com/post/business-central-workaround-cross-keys-al-table-extension
The question here is certainly how high-performing such query constructs really are compared to the right key.
Disadvantage: If necessary, all codes in which the desired key is used are to be converted, including flow fields that are based on the desired key.
Of course, this also presupposes that the codes to be converted can be accessed at all (via suitable event subscribers).

I am grateful for all assessments / ideas / experiences!

Blue