Table object

[アーティクル]
10/01/2024

Tables are the core objects used to store data in Business Central. No matter how data is registered in the product—from a web service to the phone app—the results of that transaction are recorded in a table.

Table syntax

The structure of a table has four sections:

The first block contains metadata for the overall table, such as the table type.
The fields section describes the data elements that make up the table, such as their name or the type of data they can store.
The keys section contains the definitions of the keys that the table needs to support.
The final section details the triggers and code that can run on the table.

The order in which the sections appear matters. The following example illustrates the ordering:

table ObjectId TableName
{
    // Specify table properties, for example 
    Caption = 'Sample table';
    DataPerCompany = true;

    // Define the table schema
    fields {}
   
    // Define keys section (optional)
    keys {} 
    
    // Add AL code here (optional)
}

Table object limits

The table object has limitations that are mostly dictated by SQL Server, such as the maximum record size, number of fields, and the number of keys.

Learn more in Object specifications and limitations.

Table extensibility limitations

Important

Only tables with the Extensible property set to true can be extended.

Note

Extension objects can have a name with a maximum length of 30 characters.

Important

System and virtual tables can't be extended. System tables are created in the ID range of 2.000.000.000 and higher. Learn more in Object ranges.

System fields

The Business Central platform automatically adds several system fields to tables. Learn more in System fields.

Defining default values for fields

It's common to have field values set to a default value. To set a default, you set the InitValue property on the field.

Learn more in InitValue property.

Defining validation rules for fields

If you want to run business logic to validate the value of a field, you can define the OnValidate trigger on the field.

Learn more in OnValidate (Field) trigger.

Snippet support

Typing the shortcut ttable creates the basic layout for a table object when using the AL Language extension for Microsoft Dynamics 365 Business Central in Visual Studio Code.

Tip

Use Ctrl+Space to trigger IntelliSense and get assistance on code completion, parameter info, quick info, and member lists. For more information about snippets, see Syntax and snippets.

Add tooltips on table fields

Starting in Business Central 2024 release wave 1, you can define tooltips on table fields. When a tooltip is defined on a table field, any page that uses the field automatically inherits the tooltip.

Learn more in Add tooltips to table and page fields.

Enable full-text search on table fields

APPLIES TO: Business Central 2024 release wave 2 (v25) and later.

You can specify whether table fields are optimized for text search by setting the OptimizeForTextSearch property to true.

Learn more in Enable text search on table fields.

Table example

This table stores address information and it has five fields; Address, Locality, Town/City, County, and IsValidated.

table 50104 Address
{
    Caption = 'Sample table';
    DataPerCompany = true;

    fields
    {
        field(1; Address; Text[50])
        {
            Caption = 'Address retrieved by Service';
            // in 2024 release wave 2, you can define that table fields are included in optimized text search
            OptimizeForTextSearch = true;
        }
        field(2; Locality; Text[30])
        {
            Caption = 'Locality retrieved by Service';
            Description = 'Locality feature likely to change in vNext'; // Internal note (not shown in the client)
            OptimizeForTextSearch = true;
        }
        field(3; "Town/City"; Text[30])
        {
            Caption = 'Town/City retrieved by Service';
            // in 2024 release wave 1, you can define tooltips on the table field level            
            ToolTip = 'Town/City retrieved by Service';
            OptimizeForTextSearch = true;            
        }
        field(4; County; Text[30])
        {
            Caption = 'County retrieved by Service';
            OptimizeForTextSearch = true;

            // this is how you define field validation on the table level
            trigger OnValidate()
            begin
                ValidateCounty(County);
            end;

        }
        field(5; IsValidated; Boolean)
        {
            Caption = 'Address validated yet?';
            InitValue = false; // this is how you define default values 
        }        
    }

    keys
    {
        key(PrimaryKey; Address)
        {
            Clustered = true;
        }
    }
}

次の方法で共有