Defining table structures

Path: Development > Programming in the AL language > Tables and data > Defining table structures · tier official · system development · narrative reviewed (checked by Opus)

Overview

Tables are the fundamental database objects in Business Central. A table has data and a description made up of fields, keys, properties, and triggers. The pages in this section describe each part of that structure, from general concepts to specific properties.

Start with Tables overview for the concepts, then read Table object for syntax, limitations, and extensibility rules. Setting Relationships Between Tables explains the TableRelation property for one-to-many, many-to-many, and one-to-one relations, including conditional relations and filters. Table extension object shows how to add fields, keys, and trigger code without changing the base table.

Smaller pages cover specific topics: system fields that every table gets automatically, tooltips on table and page fields, and the OptimizeForTextSearch property for full-text search on character fields.

Key points

• Tables consist of table data and a table description with fields, keys, properties, and triggers.
• TableRelation defines one-to-many, many-to-many, and one-to-one relationships, with conditions and table filters, for validation and lookups.
• Table extensions add fields, keys, and trigger code without modifying the base table; the Extensible property controls extensibility.
• Every table gets system fields automatically: SystemId for unique record identification, audit fields (SystemCreatedAt, SystemCreatedBy, SystemModifiedAt, SystemModifiedBy), and SystemRowVersion for synchronization.
• Tooltips set on table fields are inherited by pages starting in 2024 release wave 1, and page fields can override them; CodeCop rule AA0234 applies.
• OptimizeForTextSearch uses full-text search in SQL Server and Azure SQL Database for efficient, case- and accent-insensitive searching on character data.
• The Table object page covers InitValue, OnValidate, and Integer to BigInteger migration, and mentions 2024 release wave 1, 2024 release wave 2, and 2026 release wave 2.

Learn pages

• Add Tooltips to Table and Page Fields: Description of how you use AL to add tooltips to table and page fields so that they’re available when users hover over fields in the client.
• Enable optimized text search on table fields: Learn how to allow text search on table fields.
• Setting Relationships Between Tables: Relationships between tables in relational database design for Business Central.
• Table extension object: This article describes the table extension object in AL for Business Central.
• Table object: This article describes the structure, object limits, and extensibility of the table object in AL for Business Central.
• Table System Fields in Business Central: Learn how Business Central adds system fields for record IDs, data auditing, and timestamps to tables, including their behavior and use in AL code.
• Tables overview: Tables are the objects in which you store and manipulate data, and you create pages and reports to access and view the data in the tables.

Videos, posts and code changes

Linked by a Haiku matcher with a grounding quote from the item’s summary (link/topics.ts); machine-generated.

• #10009 Disable low-usage Change Log Entry index (code change): “The Change Log Entry table’s Key4 index on Notification Message Id is disabled to improve write performance”
• #10064 Add support for “External Document No.” in Subscription Contracts Fixes #8818 (code change): “Added External Document No. field to Subscription Contracts”
• #10235 [Bug][SubscriptionBilling] Enforce Subscription Line Start Date change rules on all edit paths (code change): “Subscription Line start date changes are now blocked when billing has occurred”
• #10367 [Quality Management] Add OptimizeForTextSearch to searchable Qlty. Inspection Header fields (AB#620381) (code change): “Added OptimizeForTextSearch property to Description and Source Task No. fields”
• #11748 [Master] - Slice 626292: [Excise Tax][VENDOR] Bonded Locations for Excise Calculations (code change): “Added bonded-location treatment enum with Suspend/Ignore options for excise calculations”
• #9168 [QM] Fix internal put-away from Quality Inspection sourcing wrong bin (code change): “Internal put-away disposition from Quality Inspection now resolves the source bin from actual Bin Content”
• #9725 [Master] - Slice 626127: [Excise Tax][VENDOR] Multiple Excise Taxes per Item (code change): “New Item Excise Tax table (7415) stores multiple tax types per item”
• #125 knowledge(data-modeling): TableRelation delete/rename asymmetry and the xRec before-image contract (code change): “Owning tables must delete dependent records manually since AL lacks cascading delete”
• #158 3 AL/BC patterns from CURABIS’s internal automated-testing training material (code change): “Item Ledger Entry carries the shipment’s document number, not the invoice number”
• #208 2 AL/BC patterns: TableRelation field length and RecordRef.Open Temp parameter (code change): “Table relation fields shorter than their target compile cleanly but fail at runtime”
• How to Use Concealed Text Fields in Business Central AL (community post): “Developers set MaskType = Concealed on field definitions in AL code”
• Business Central v29: SQL Table Extensions Redesign Explained (community post): “storing all custom fields directly in the primary SQL table”
• Debunking Myths related to Table Structure in Dynamics 365 Business Central 2026 Wave 2 (community post): “Maximum of 1018 regular fields can be defined in a table”
• How to Migrate Business Central Table Fields from Integer to BigInteger. (community post): “Integer to BigInteger field migration is a non-destructive schema change supported”
• BC Friday Tips #55 CaptionClass (community post): “CaptionClass property enables dynamic captions that adapt based on data”
• BC Friday Tips #78 InitValue Property (community post): “The InitValue property sets a default value for new table fields”
• Creating TableExtensions in BC29 like we’re back in NAV (But Business Central) (video): “Table extensions; cross-app keys; Load fields for selective field retrieval”
• Business Central 29: How Many Fields Can a Table Really Have? (video): “table extensions; field limits; sql server columns; data types”

Source: Microsoft Learn (CC BY 4.0). Descriptions are Learn’s own.