Exercise - Use data manipulation statements

  • 12 minutes

You're a developer for CRONUS International Ltd. You learned how to use data manipulation statements in AL and now want to practice this new skill in the development environment. You want to be able to export GL/Entry records to a specific table. The customer needs to complete this task and source code so that you have an overview of the total amounts by customer and source code.

Tasks

  • Create a new AL extension.

  • Create a new table.

  • Create a page that is linked to the table.

  • Create a codeunit to copy data from different tables to the newly created table.

Steps

  1. In Visual Studio Code, in the project you created earlier in this learning path, create a new folder named: CustomerOverview

  2. Create all of the new .al files mentioned in the next steps, in the CustomerOverview folder.

  3. Select File > New File and then save this file by selecting File > Save. Name the file CustomerOverview.Table.al.

  4. Create a new table in this file by using code snippets. Enter ttable and then press the Tab key.

  5. Change the ID to 50120 and the name to "Customer Overview".

  6. Set the DataClassification property to CustomerContent and set the Caption property to Customer Overview.

  7. Remove the field MyField.

  8. Create the following fields. You can use the tfield snippet.

    Field no. Field name Data type Length
    1 Entry No. Integer
    2 Customer No. Code 20
    3 Customer Name Text 100
    4 Source Code Code 20
    5 Amount Decimal
    6 LastRunDate DateTime

  9. Set the DataClassification property for each field in the table to CustomerContent.

  10. Set the Caption property for each field in the table.

  11. Set the Primary Key to Entry No.

  12. Remove the global variables section.

  13. Remove the table triggers.

  14. Your file now resembles this code block:

    table 50120 "Customer Overview"
{
    DataClassification = CustomerContent;
    Caption = 'Customer Overview';

    fields
    {
        field(1; "Entry No."; Integer)
        {
            DataClassification = CustomerContent;
            Caption = 'Entry No.';
        }
        field(2; "Customer No."; code[20])
        {
            DataClassification = CustomerContent;
            Caption = 'Customer No.';
        }
        field(3; "Customer Name"; Text[100])
        {
            DataClassification = CustomerContent;
            Caption = 'Customer Name';
        }
        field(4; "Source Code"; Code[20])
        {
            DataClassification = CustomerContent;
            Caption = 'Source Code';
        }
        field(5; "Amount"; Decimal)
        {
            DataClassification = CustomerContent;
            Caption = 'Amount';
        }
        field(6; "LastRunDate"; DateTime)
        {
            DataClassification = CustomerContent;
            Caption = 'LastRunDate';
        }
    }

    keys
    {
        key(Pk; "Entry No.")
        {
            Clustered = true;
        }
    }
}

  15. Create a new file, named CustomerOverviewList.Page.al.

  16. Create a new page in this file by using code snippets. Enter tpage and then press the Tab key.

  17. Change the ID to 50120 and the name to Customer Overview List.

  18. Set the SourceTable property to Customer Overview and set the Caption property to Customer Overview List.

  19. Set the PageType property to List and set the UsageCategory property to Lists. Verify that the ApplicationArea property is set to All.

  20. Set the Editable property to false.

  21. Verify that a content area is created in the layout section. If not, create an area called content.

  22. Change the name of the repeater to General.

  23. Add all fields of the Customer Overview table you created earlier, within the repeater.

  24. Verify that the ApplicationArea property is set to All for all fields on the page.

  25. Add meaningful tooltips to all fields in the page.

  26. Remove the factboxes area.

  27. Remove the actions area.

  28. Your file now resembles this code block:

    page 50120 "Customer Overview List"
{
    PageType = List;
    ApplicationArea = All;
    UsageCategory = Lists;
    SourceTable = "Customer Overview";
    Caption = 'Customer Overview List';
    Editable = false;

    layout
    {
        area(Content)
        {
            repeater(General)
            {

                field("Entry No."; Rec."Entry No.")
                {
                    ToolTip = 'Specifies the value of the Entry No. field.';
                    ApplicationArea = All;
                }
                field("Customer No."; Rec."Customer No.")
                {
                    ToolTip = 'Specifies the value of the Customer No. field.';
                    ApplicationArea = All;
                }
                field("Customer Name"; Rec."Customer Name")
                {
                    ToolTip = 'Specifies the value of the Customer Name field.';
                    ApplicationArea = All;
                }
                field("Source Code"; Rec."Source Code")
                {
                    ToolTip = 'Specifies the value of the Source Code field.';
                    ApplicationArea = All;
                }
                field(Amount; Rec.Amount)
                {
                    ToolTip = 'Specifies the value of the Amount field.';
                    ApplicationArea = All;
                }
                field(LastRunDate; Rec.LastRunDate)
                {
                    ToolTip = 'Specifies the value of the LastRunDate field.';
                    ApplicationArea = All;
                }
            }
        }
    }
}

  29. Create a new file, named CustomerOverviewMgmt.CodeUnit.al.

  30. Create a new codeunit in this file by using code snippets. Enter tcodeunit and then press the Tab key.

  31. Change the ID to 50120 and the name to Customer Overview Mgmt.

  32. Add the following global variables.

    Name DataType Subtype
    CustomerOverview Record Customer Overview
    Customer Record Customer
    SourceCode Record Source Code
    GLEntry Record G/L Entry
    NextEntryNo Integer

  33. In the OnRun trigger, add the following code.

    Clear(SourceCode);
Clear(CustomerOverview);
Clear(GLEntry);

if CustomerOverview.FindLast() then
    NextEntryNo := CustomerOverview."Entry No." + 1
else
    NextEntryNo := 1;

if SourceCode.FindSet() then
    repeat
        if Customer.FindSet() then
            repeat
                GLEntry.SetRange("Source Type", GLEntry."Source Type"::Customer);
                GLEntry.SetRange("Source Code", SourceCode.Code);
                GLEntry.SetRange("Source No.", Customer."No.");
                if GLEntry.FindSet() then begin
                    GLEntry.CalcSums(GLEntry.Amount);
                    CustomerOverview."Entry No." := NextEntryNo;
                    CustomerOverview."Customer No." := Customer."No.";
                    CustomerOverview."Customer Name" := Customer.Name;
                    CustomerOverview."Source Code" := SourceCode.Code;
                    CustomerOverview.Amount := GLEntry.Amount;
                    CustomerOverview.LastRunDate := CurrentDateTime();
                    CustomerOverview.Insert();
                    NextEntryNo += 1;
                end;
            until Customer.Next() = 0;
    until SourceCode.Next() = 0

  34. Open the CustomerOverviewList.Page.al file.

  35. Add an actions area to the page.

  36. In the actions area, add a Processing section.

  37. In the Processing section, add an action.

  38. Change the name of the action to Import Records and then set the following properties on the action:

    • Caption: Import Records

    • Image: Import

    • ApplicationArea: All

  39. In the OnAction trigger, create a local variable.

    Name DataType Subtype
    UpdateCustomerOverview Codeunit Customer Overview Mgmt

  40. Call the codeunit RunTrigger from the OnAction trigger.

  41. Your code now resembles this code block:

    page 50120 "Customer Overview List"
{
    PageType = List;
    ApplicationArea = All;
    UsageCategory = Lists;
    SourceTable = "Customer Overview";
    Caption = 'Customer Overview List';
    Editable = false;

    layout
    {
        area(Content)
        {
            repeater(General)
            {

                field("Entry No."; Rec."Entry No.")
                {
                    ToolTip = 'Specifies the value of the Entry No. field.';
                    ApplicationArea = All;
                }
                field("Customer No."; Rec."Customer No.")
                {
                    ToolTip = 'Specifies the value of the Customer No. field.';
                    ApplicationArea = All;
                }
                field("Customer Name"; Rec."Customer Name")
                {
                    ToolTip = 'Specifies the value of the Customer Name field.';
                    ApplicationArea = All;
                }
                field("Source Code"; Rec."Source Code")
                {
                    ToolTip = 'Specifies the value of the Source Code field.';
                    ApplicationArea = All;
                }
                field(Amount; Rec.Amount)
                {
                    ToolTip = 'Specifies the value of the Amount field.';
                    ApplicationArea = All;
                }
                field(LastRunDate; Rec.LastRunDate)
                {
                    ToolTip = 'Specifies the value of the LastRunDate field.';
                    ApplicationArea = All;
                }
            }
        }
    }
    actions
    {
        area(Processing)
        {
            action("Import Records ")
            {
                Caption = 'Import Records';
                ToolTip = 'Import Records';
                ApplicationArea = All;
                Image = Import;

                trigger OnAction()
                var
                    CustomerOverviewMgmt: Codeunit "Customer Overview Mgmt";
                begin
                    CustomerOverviewMgmt.Run();
                end;
            }
        }
    }
}

  42. Open the launch.json file in the .vscode folder. Set the startupObjectId setting to 50120 and the startupObjectType to Page.

  43. Publish your extension to the sandbox. Select View > Command Palette... (Ctrl+Shift+P).

  44. Enter AL: Publish in the search box (or press the F5 key) and then select the command from the list.

  45. Verify that the Microsoft Dynamics 365 Business Central application launches and that the Customer Overview List page appears.

  46. Test your extension by selecting the Import Records action.