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.
Comments
Please, why is prohibited "single key can't include fields from both the base table object and table extension object"?
Typically what developer need is extended some Base App key for my new field in Extension. I can not imagine any issue why could not be SEAMLESSLY copied to companion table.
I think that let devopers to ensure field mirroring is wrong idea. Developer then must solve issues with initializing data when install Extension, issues with Insert/Modify/Rename...
Only what Developer needs is to use required key. Platform may do that. If Extension needs to key from field from another table, just maintain copy of it and create requested key...
Category: Development
Exploring some possible options and limitations in SQL, it seems like it is technically possible to create a SIFT where the fields span base and extension tables. See below for example.
In the example I have created "Status","K3Outstanding Quantity" fields as an extension to Sales Line table. To explore the possibility I have created a Indexed View spanning both the base and extended table using the following SQL code
CREATE VIEW [dbo].[Cronus UK$Sales Line$ef933c61-34d2-4d4e-ada4-6fdf714541ab$VSIFT$K3Test]
WITH SCHEMABINDING
AS
SELECT "37"."No_" As "No_"
,"37_e2"."Status" As "Status"
,SUM("37_e2"."K3Outstanding Quantity") AS "SUM$K3Outstanding Quantity"
,SUM("37"."Quantity") AS "SUM$Quantity"
,COUNT_BIG(*) AS "$Cnt"
FROM dbo."Cronus UK$Sales Line$437dbf0e-84ff-417a-965d-ed2bb9650972" "37"
JOIN dbo."Cronus UK$Sales Line$ef933c61-34d2-4d4e-ada4-6fdf714541ab" "37_e2"
ON("37"."Document Type" = "37_e2"."Document Type")
AND ("37"."Document No_" = "37_e2"."Document No_")
AND ("37"."Line No_" = "37_e2"."Line No_")
Group by "37"."No_","37_e2"."Status"
Go
CREATE UNIQUE CLUSTERED INDEX [VSIFTIDX] ON [dbo].[Cronus UK$Sales Line$ef933c61-34d2-4d4e-ada4-6fdf714541ab$VSIFT$K3Test]
(
"No_","Status"
)
It all worked. Since there is no limitation on SQL, it is technically feasible to have SIFT spanning base and extension table. This needs to be supported in AL development environment.
Category: Development
Typically 15% - 20% of the space is taken by unused index and SIFT in a live system. This will cause a overhead in insert/modify/delete operations and will take more space for the database as well as more time for index maintenance. A common performance tuning on a live NAV database is to turn off the index and SIFT which are not practically used by the customer. To accommodate important requirements like this in AL, we also need facility to alter index and SIFT in base table. So something like the following code needs to be supported.
Changes incudes...
1) Turn on/off Enabled
2) Turn on/off MaintainSiftIndex
3) Turn on/off MaintainSQLIndex
4) Ability to Add new field to an existing index (ie "The New field" in example below)
5) Ability to Add new field to an existing SIFT (ie "New Decimal Field" in example below)
These features are essential for many practical enhancements in AL.
tableextension ID SalesLineIndexAndSIFT extends "Sales Line"
{
keys
{
modify
{
key(Key4; "Document Type", "Bill-to Customer No.","The New field","Currency Code", "Document No.")
{
SumIndexFields = "Outstanding Amount", "Shipped Not Invoiced", "Outstanding Amount (LCY)", "Shipped Not Invoiced (LCY)", "Return Rcd. Not Invd. (LCY)","New Decimal Field";
MaintainSiftIndex = false;
MaintainSQLIndex = false;
}
}
}
}
Category: Development
Thinking about it, handlining of new field in base extension will need a re think as follows...
Any new fields needs to be created on the base table rather than in a new table behind the scenes in SQL. This will give a scope to add index and SIFT spanning standard and new fields, which is very common real life requirement to address customer's business needs as customisation extension. I know there are concerns like risk of duplication of field names in different extensions.... But I believe this can be handled as development best practice such as having a prefix and setting the caption to the actual field name. Further Microsoft could develop a checking mechanism while publishing an extension to capture such issues. We have already have this in fashion vertical where all the new fields are prefixed as "PF". This architectural change will allow index and SIFT to be created through extension, spanning existing and new fields overcoming limitation on SQL. Index and SIFT are vital for providing scalable system. Without them, there is a huge risk of performance issues. Further in a table extension, it should be able to just add new index or SIFT without adding any field with an ability to extend any extension (other than base extension with index and SIFT.
Can Microsoft look at re architecture this as a matter or urgency in the next release and announce a day of release as we are now forced to stop all the upgrade work until this feature is available in AL. The performance risk is so great to do an upgrade of any live system with lack of ability to create SIFT and index as customer will suffer from performance problem soon after the upgrade due to lack of Index and SIFT.
Category: Development
All the requested features will also needs to be available for SIFT
Category: Development
It's a good idea,hope Microsoft work well on it !
Category: Development
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
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
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
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
Business Central Team (administrator)