Adding keys to standard tables in v18

In this video, I explore the new feature in Microsoft Dynamics 365 Business Central v18 where it’s possible to add keys on base fields.

https://youtu.be/cGui62yuk9g

In this video, Erik explores one of the exciting features introduced in Business Central version 18: the ability to add keys on existing fields in standard tables through table extensions. Previously, while you could extend tables with new fields and add keys on those new fields, you couldn’t create keys on fields that already existed in the base table. This limitation is now lifted, and Erik walks through the feature hands-on.

The Problem: Missing Keys on Standard Tables

A common performance challenge in Business Central arises when you need to search or filter on fields that don’t have a SQL index. Erik uses a real-world example to illustrate this: the External Document No. field on posted sales documents.

If you look at Table 112 (Sales Invoice Header) in the base app, you’ll find that there is a key that includes External Document No., but it’s combined with Sell-to Customer No. and doesn’t necessarily serve every lookup scenario efficiently. Worse, if you look at Table 114 (Sales Cr. Memo Header), there’s no key on External Document No. at all.

In scenarios where you’re matching posted documents by their external document number — for example, correlating Business Central invoices with documents from an external system — the lack of an index means queries can become painfully slow, especially when you have a large volume of posted invoices and credit memos.

Adding a Key on an Existing Field

With version 18, you can now solve this directly in a table extension. Erik creates a table extension for the Sales Invoice Header and adds a key on the existing External Document No. field:

tableextension 50138 "My Sales Invoice" extends "Sales Invoice Header"
{
    fields
    {
        field(50138; MyField; Code[20])
        {
            Caption = 'My Field';
        }
    }
    keys
    {
        key(ExtDocKey; "External Document No.")
        {
        }
    }
}

The key things to note here:

  • The keys section is now available in table extensions (previously it was either absent or limited to fields added by the extension itself).
  • Every key requires a name (e.g., ExtDocKey), though the name isn’t currently used for much in AL code. Erik speculates that Microsoft may eventually allow you to use key names in methods like SetCurrentKey instead of specifying field lists.
  • You cannot mark the key as clustered — the clustered index belongs to the base table’s primary key and cannot be changed through an extension.
  • The extension deploys successfully, creating a SQL index on the External Document No. field.

The Limitation: Mixing Base and Extension Fields in a Key

Erik also tests whether you can create a key that combines a field added by the extension with an existing field from the base table. For example, creating a composite key on both MyField (the new extension field) and External Document No. (the existing base field):

// This does NOT work:
key(TestOne; MyField, "External Document No.")
{
}

This produces a compiler error: “The key TestOne can only be set if the specified fields are from the same table.”

From the developer’s perspective, both fields exist on the same logical table. However, internally Microsoft treats the base table fields and extension table fields as belonging to different tables (reflecting the underlying SQL implementation of table extensions). This means you cannot mix base table fields and extension fields in a single key.

If you truly need a composite key that spans both base and extension fields, your only workaround is to add a duplicate field in your extension that mirrors the base table field’s value, and then build the key entirely from extension fields. Of course, this means you need to maintain the copied value — typically through event subscribers on insert and modify — which adds complexity.

Project Configuration

For reference, the app.json confirms this feature requires application version 18.0.0.0 and runtime 7.0:

{
  "id": "7a41fffb-480e-4d30-85b6-0036ef18c560",
  "name": "AddKeysToStandardTables",
  "publisher": "Default publisher",
  "version": "1.0.0.0",
  "platform": "1.0.0.0",
  "application": "18.0.0.0",
  "idRanges": [
    {
      "from": 50100,
      "to": 50149
    }
  ],
  "showMyCode": true,
  "runtime": "7.0"
}

Summary

The ability to add keys to existing fields in standard tables is a significant quality-of-life improvement for Business Central developers starting in version 18. While there are limitations — you can’t mix base and extension fields in a single key, and you can’t alter the clustered index — this feature addresses many real-world performance problems. If you’ve ever struggled with slow lookups on fields like External Document No. across high-volume posted document tables, you can now add the appropriate index directly through a table extension without any workarounds. It’s a simple change that can have a meaningful impact on performance for your customers.

Picture of hougaard

hougaard