Create the reward level table object
The Reward Level table stores different reward levels and the points that are required to acquire an award level for your extension.
The table consists of two fields:
Level
Minimum Reward Points
In the Exercise - Create the reward level table object in the previous module, you might have already created this table. Here we'll go over it again and provide more in-depth information.
To create a new table in your extension, first create a new file by selecting the New File button in the side bar of Visual Studio Code.
Make sure that the filename ends with .al. For this example, you might want to use the name RewardLevel.Table.al. Though an .al file can contain multiple objects, we recommend that you have only one object for each file. After the file has been created, open it, and then you'll create the table in the file.
An extension is fully contained in a single folder. This folder often contains multiple files, such as app.json and launch.json files, perhaps an image file representing the extension's logo, various folders for source; "\src", other resources; "\res", and a test folder; "\test" folder. The extension doesn't need to follow a flat structure, which means that, depending on the number of application files, more folders can be used in the "src" or "test" folders to group objects based on their functionality, which can help make maintaining a large .al project easier.
Each file name has object names with only characters [A-Za-z0-9], object type, and dot al, for file type. In your extension, the name of each new application object (table, page, codeunit), can contain a prefix or suffix.
For more information about folder structure and file naming, see, Best Practices for AL.
The following code adds a new table called 50100 Reward Level for storing reward level information that is set up by the user.
table 50100 "Reward Level"
{
Caption = 'Reward Level';
TableType = Normal;
DataClassification = CustomerContent;
fields
{
field(1; Level; Text[20])
{
Caption = 'Level';
DataClassification = CustomerContent;
}
field(2; "Minimum Reward Points"; Integer)
{
Caption = 'Minimum Reward Points';
DataClassification = CustomerContent;
MinValue = 0;
NotBlank = true;
trigger OnValidate();
var
RewardLevel: Record "Reward Level";
tempPoints: Integer;
begin
tempPoints := "Minimum Reward Points";
RewardLevel.SetRange("Minimum Reward Points", tempPoints);
if not RewardLevel.IsEmpty then
Error('Minimum Reward Points must be unique');
end;
}
}
keys
{
key(PK; Level)
{
Clustered = true;
}
key("Minimum Reward Points"; "Minimum Reward Points") { }
}
trigger OnInsert();
begin
Validate("Minimum Reward Points");
end;
trigger OnModify();
begin
Validate("Minimum Reward Points");
end;
}
Entering the ttable shortcut will create the basic layout for a table object when you're using the AL Language extension in Visual Studio Code, but in this case, you can copy the preceding code and paste it in the **RewardLevel.Table.al ** file.
The following sections take a closer look at the different elements of the table.
The structure of a table has four sections:
Metadata section - The first block contains metadata for the overall table:
table 50100 "Reward Level" { Caption = 'Reward Level'; TableType = Normal; DataClassification = CustomerContent;In this example, the table has been assigned the number 50100 and the Name and Caption is Reward Level. By pressing the Ctrl+Space shortcut keys, you'll launch Intellisense and will then be able to select other properties in case you need to use them. In this example, the TableType property is set to Normal. This setting isn't required because Normal is the default value of the TableType property.
The Caption property sets the text string that displays with the object, control, or other element in the user interface for the current language. A caption is the text that is used to show the identity of a control, for example, in the caption bar of a page or a label for another control.
The DataClassification property sets the classification of the data in the table or field. Data classification can be used to adhere to security, compliance, and privacy requirements and processes for collecting, storing, and using someone's personal information.
Fields section - This second section describes the data elements that make up the table, their names, and the type of data that they can store.
fields { field(1; Level; Text[20]) { Caption = 'Level'; DataClassification = CustomerContent; } field(2; "Minimum Reward Points"; Integer) { Caption = 'Minimum Reward Points'; DataClassification = CustomerContent; MinValue = 0; NotBlank = true; trigger OnValidate(); var tempPoints: Integer; RewardLevel: Record "Reward Level"; begin tempPoints := "Minimum Reward Points"; RewardLevel.SetRange("Minimum Reward Points", tempPoints); if not RewardLevel.IsEmpty then Error('Minimum Reward Points must be unique'); end; } }This example includes two fields, each with a number, name, and datatype. If a field has properties, they can be set in between the braces { }.
The Minimum Reward Point field also has an OnValidate trigger. This trigger is run after the default validation behavior when data is entered in a field. During the default validation behavior, the system checks that the data type of the entered value matches the one that is defined for the field. It also checks that the type complies with the property constraints that are set up in such field before the validation occurs. An error message displays if an error occurs in the trigger code. If an error occurs, the user entry isn't written to the database.
Keys section - The third section contains the definitions of the keys that the table needs to support.
keys { key(PK; Level) { Clustered = true; } key("Minimum Reward Points"; "Minimum Reward Points") { } }This example includes two keys: PK and Minimum Reward Points. The first key in this list is always the primary key and the other keys are secondary keys. The primary key determines what makes a record unique. Keys can consist of multiple fields. You can define the name of the key.
The primary key determines the logical order in which records are stored, regardless of their physical placement. Logically, records are stored sequentially in ascending order and are sorted by the primary key. Before adding a new record to a table, SQL Server checks if the information in the record's primary key fields is unique, and only then will insert the record into the correct logical position. Records are sorted dynamically so the database is always structurally correct. This approach allows for fast data manipulation and retrieval.
Triggers section - The fourth section details the triggers and code that can run on the table.
trigger OnInsert(); begin Validate("Minimum Reward Points"); end; trigger OnModify(); begin Validate("Minimum Reward Points"); end;In this example, when an insert or modify operation occurs on the table, the Validate function of the Minimum Rewards Points field needs to be run. The Validate function calls the OnValidate trigger of a field.
Now that you've created the Reward Level table, make sure that you save it by pressing the Ctrl+S shortcut keys.
Note
In Visual Studio Code, you can enable the AutoSave option.
