Task 4: Understanding Table Types in Dynamics 365 Business Central - Beginner to Builder: Your Path in Business Central Development step by step tutorial

Introduction

Microsoft Dynamics 365 Business Central utilizes three primary types of tables to manage and store data: Normal Tables, Temporary Tables, and Virtual Tables. Each table type serves distinct purposes and understanding their functionalities and differences is crucial for optimizing the use of Business Central. This article provides a comprehensive overview of these table types, their unique characteristics, and practical use cases.

1. Temporary Tables

Temporary tables in Business Central serve as transient storage to facilitate speedy data processing without persisting data in the database. They are ideal for temporary data manipulation where data does not need to be stored permanently.

Characteristics of Temporary Tables:

• Non-Persistent Storage: Data in temporary tables is stored in memory and not in the SQL database. This temporary storage is cleared once the table is no longer in use.
• Transaction Independence: Unlike normal tables, transactions in temporary tables do not adhere to database transaction principles, allowing for faster data processing.

Code:

table 50100 MyTable

{

    DataClassification = ToBeClassified;

    TableType = Temporary;

    fields

    

    ...

}

For more detailed information and technical specifications, you can refer to the Microsoft documentation on temporary tables.

2. Normal Tables

Normal tables are the backbone of data storage in Business Central. They are used to store data that needs to be maintained over time and are subject to database management and transaction rules.

Characteristics of Normal Tables:

• Permanent Storage: Data is stored permanently in the SQL database, making it suitable for storing ongoing business data.
• Structured Data Management: Includes metadata, fields, keys, and triggers to manage data effectively.

Code:

table 50104 Address

{

    Caption = 'Sample table';

    DataPerCompany = true;

    fields

    {

        field(1; Address; Text[50])

        {

            Caption = 'Address retrieved by Service';

        }

        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)

        }

        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

            // uncomment the Tooltip line below to try it out

            // ToolTip = 'Town/City retrieved by Service';

        }

        field(4; County; Text[30])

        {

            Caption = 'County retrieved by Service';

            // 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;

        }

    }

}

Further details can be found in the Microsoft documentation on table objects.

3. Virtual Tables

Virtual tables do not store data but dynamically compute and display system information at runtime. They are read-only and primarily used for accessing system-level data.

Characteristics of Virtual Tables:

• Dynamic Data Generation: Virtual tables compute data in real time and do not store any information.
• Read-Only Access: Primarily used to provide insights into the system’s operational data, such as user connections and system states.

A system administrator usually uses these virtual tables. These tables give the system administrator information about the users who are currently connected to the database and the current state of the system.

The virtual tables provide such information as:

• Integers in the range –1,000,000,000 to 1,000,000,000
• Dates in a given period
• Overview of the operating system files
• Overview of the logical disk drives
• Overview of the operating system files that store the database

Because virtual tables aren't stored in the database, you can't view them directly. To view a virtual table, you must create a list page based on the virtual table.

Additional information on virtual tables is available in the Microsoft documentation on virtual tables.

Differences and Use Cases

Temporary vs. Normal Tables

Temporary tables are used for processing large volumes of data temporarily without affecting database performance, ideal for operations like report generation and batch data processing. In contrast, normal tables are essential for long-term data storage and regular data operations within Business Central.

Temporary vs. Virtual Tables

While temporary tables can manipulate and temporarily hold data, virtual tables are strictly for viewing system-generated information and cannot be used for data storage or manipulation.

Case Study: Financial Report Generation

Consider a business scenario where there is a need to generate complex financial reports. Utilizing a temporary table allows for quick processing of large datasets by temporarily storing and manipulating data in memory, enhancing performance significantly. After processing, the temporary data is discarded, ensuring optimal database performance for regular operations.

Conclusion

Understanding the distinct functionalities of Normal, Temporary, and Virtual tables in Business Central is crucial for leveraging each type to its fullest potential. By selecting the appropriate table type for specific tasks, businesses can enhance efficiency, performance, and data management within their operations.

Stay Tuned for the next Task 5: Table Properties

Leave your comments. If you have any specific task in your mind, share that too.