164
When developing AL table extensions, it is possible to create one or more keys, but the scope of that key is solely in the extended table (not the base table that is being extended).

VS Code will not allow you to create that key if the key references one or more fields found in the parent table that the table extension is extending. IMHO the current tableextension architecture is too limiting and can pose a roadblock for legacy NAV customers interested to upgrading their codebase to Business Central + AL extensions.

I suggest that tableextension objects should be able to do the following:

- ALLOW to create a key where ALL fields are in the parent table
- ALLOW to create a key where ALL fields are in the extended table
- DO NOT ALLOW creating a key where that have fields in both the parent table and extended table (which would make sense, since you cannot create a sql index against two or more tables)

For larger tables such as ledger tables, not having the ability to extend keys in the parent table I fear will have an unwelcome performance impact where custom filtering and sorting is needed in some situations.
Category: Development
STATUS DETAILS
Under Review
Ideas Administrator

Thank you for your feedback. We are considering adding it to our longer term roadmap.

Your help is greatly appreciated,
Business Central Team

Comments

S

It's a good idea,hope Microsoft work well on it !

Category: Development

S

I would add that flowfields frequently require a key in a base table and some will require spanning across ISV AND custom table extension fields.

Category: Development

S

If you want to have a sorting in a report, the key must exist. But you cannot setup new keys on table extension the fields are not in the extension.

This is a must have.

Category: Development

S

I have the same problem, I wanted to add a key with the property SumIndexField, but I get an error message----'"Reference '"G/L Account No.)' in application object 'GLBudgetEntryExt' does not existAL(AL0186)---
We hope that Microsoft will give us the possibility in the future to add custom keys to base tables with extensions.

Category: Development

S

You can mirror the standardfields in your extension using table events. The you can add a key in your extension to your fields. This will solve the general problem and this is the only workaround today.

Category: Development

S

I would add an extra option:

ALLOW to create a key where ALL fields are in the extended table OR in the *main* table key.

Fields in the main table key (the record ID) are actually copied to the extended table, so mixing these with fields in the extended table would not be a problem (as we would be creating a new SQL index in the extended table).

Category: Development

S

Thanks for posting this, Steve!
In our solution we have some logic that has to make lots of transactions using a small table (base app) with only two columns.
We have a large customer with 200+ users. In that case performance is very important. By default the execution for this customer took ~18hrs. With the custom key added in the On Prem version, execution takes roughly 4 hrs.

Please, please, please give us the option to add custom keys to base tables with extensions, otherwise we won't be able to switch to extensions. This is a dealbreaker for us.

Category: Development