The table in Business Central
Introduction
The table, which functions as both a data definition and a data container (a table's contents), is distinct from the data. The table specification (container) includes descriptions of data identification, data structure, validation processes, storage, and retrieval. Due to its innate architecture, changing this definition would require the assistance of a programmer.
The information in the data is dynamic and derived from user behaviour. We see that the data is explicitly referred outside of the table as a definition of structure in the permissions setup data.
Table
Tables are the fundamental data storage units in Dynamics 365 Business Central. The data will finally wind up in a table, regardless of whether a person inputs their information via a web service or by swiping their finger on a mobile app.
The framework of a table is made up of four main parts:
- General information about the table, such as its kind, is included in the first section of the table.
- The table's data elements, including their names and the data types they may store, are described in the fields part.
- Specific definitions of the keys that the table must accommodate can be found in the keys subsection.
- At last, we'll go over the many triggers and scripts that can be executed on the table.
Table Snippet
Keys
Database keys are used by SQL Server, the system used to administer databases, to uniquely identify data in each table. A key is a combination of one or more columns in a table that is used to uniquely identify a row. SQL employs indexes to speed up data retrieval from table rows. Learn how to generate key and indexes for tables in Business Central using AL in this tutorial.
An AL key definition is a list of IDs for several fields in a given database. Depending on the type of key, you can declare it in either a table object or a table extension object. Primary keys and secondary keys both exist.
Defining a Key
Replace Name with whatever identifying text you wish to use for the key. Put the name of the field you want to use as the key in instead of "Field." Use commas to divide each field if you like to include more than one in a single key. The first key keyword in a table object specifies the primary key. Subsequent key keywords define secondary keys.
Primary Key
Every single record in a table needs to have a primary key that is completely unique. Every table has a single primary key. The primary key of the table they extend is carried over from the parent object in SQL (the base table object). Therefore, any key that you define in a table extension object is treated as a secondary key.
Secondary Key
Indexes in SQL are generated by secondary keys. They are defined by both table objects and table extension objects. It is possible to define many secondary keys for a given table object or table extension object.
Fields from both the base table object and the table extension object can be used in a key. However, there are a few restrictions. See Limitations & Restrictions for further details.
The following elements affect total speed:
- Size of the database.
- A number of active keys.
- The complexity of the keys.
- A number of records in your tables.
- The hard drive's and your computer's speed.
Key Properties
There are several qualities that may be used to change how a key behaves, including Enabled, Clustered, and Unique.
Unique: Returns true if a unique constraint matching the key has been generated in SQL Server; false otherwise.
Enabled: Controls the responsiveness of control to user input by setting an enabling or disabling value for a field or key.
Clustered: Flags if the database's clustered index is also defined by the key, which is set to a given value.
Simple Table
table 50115 Employeee
{
Caption = 'Employee Table';
DataClassification = ToBeClassified;
fields
{
field(1; "emp code"; Code[10])
{
DataClassification = ToBeClassified;
}
field(10; "emp name"; Text[15])
{
DataClassification = ToBeClassified;
}
field(20; salary; Decimal)
{
DataClassification = ToBeClassified;
}
field(30; DOJ; Date)
{
DataClassification = ToBeClassified;
}
field(40; City; Option)
{
OptionMembers = Chennai,Coimbatore,Bangalore,
DataClassification = ToBeClassified;
}
field(50; gender; Option)
{
OptionMembers = M,F,Notwillingtosay,T;
DataClassification = ToBeClassified;
}
}
keys
{
key(key1; "emp code")
{
Clustered = true;
}
key(sk; City)
{
}
}
trigger OnInsert()
begin
Message('Data inserted');
end;
trigger OnModify()
begin
Message('Data modified');
end;
trigger OnDelete()
begin
Message('Data deleted');
end;
trigger OnRename()
begin
Message('Data Renamed');
end;
}
Conclusion
Check out my previous blogs to know more about the Business central basics, learning path, simple projects, etc.,
Leave your message if you have any doubts.
Meet you soon......😊
0 Comments