Category Archives: AL Language

OnAfterLookup: New page field trigger coming in v18

One of the news in AL Language that is coming with the upcoming major version (Business Central 2021 W1) in April is a new trigger AnAfterLookup. The new trigger is described on Microsoft Docs (however, the description is still a bit confusing…).

As the Lookups are often used, I already tried what’s new and how we can improve our projects with these changes. Unfortunately, there is no documentation available at the time of this article, so I am unsure what is intended and what is currently just a bug (as this is still only a part of the preview version).

Although I could use the triggers in some small examples, I still can not find any scenario where the OnAfterLookup trigger can be useful in the real world. Let’s look at some examples.

OnLookup & OnAfterLookup

The OnLookup trigger is known from the beginning of C/AL Language. It is used to build more complex Lookups that can not be created using TableRelation (or any similar property). It replaces standard lookup features (runs in a place of regular lookup feature).

Until the Roletailer Client (NAV2009RT or rather NAV 2013), there was no visual/functional difference between lookups created through any property or OnLookup trigger. However, with Roletailored client (and Web Client), there is a vast difference – a dropdown list available only for relations made through TableRelation property. The dropdown list is beneficial for users, and developers must have it in mind when creating relations.

Examples

OnAfterLookup trigger does not override the standard trigger from the TableRelation. It is triggered once the record is selected from the Dropdown box; however, if the user decides to use a standard lookup list using “Select from a full list”, the trigger is not fired.

With this in mind, it is unsurprising that both triggers written for the same field are useless (OnAfterLookup is not used as OnLookup does not use dropdowns). In the first example below, the OnAfterLookup trigger is never called.


 field("TKA Tariff No."; Rec."TKA Tariff No.")
 {
     trigger OnLookup(var Text: Text): Boolean
     begin
         Message('Old Value: ' + Text);
         Text := YourOwnLookupReturningSelectedRecord()
         Message('New Value: ' + Text);
         exit(true);
     end;

     trigger OnAfterLookup(Selected: RecordRef)
     begin
         Message('Selected Value: ' + Format(Selected));
     end;
 }

If we only use the OnAfterLookup trigger, the trigger is fired once the user chooses the DropDown list value. As a developer, you get a RecordRef variable that contains the selected record.

The second example shows how you can print information about the selected record. However, I am still unsure how to use it in the real world – we can just get information from the selected record (and even only for those made through DropDown List, not the full list).


 field("TKA Tariff No."; Rec."TKA Tariff No.")
 {
     trigger OnAfterLookup(Selected: RecordRef)
     var
         TariffNumber: Record "Tariff Number";
     begin
         Selected.SetTable(TariffNumber);
         Message('Selected value: ' + TariffNumber."No.");
     end;
 }

Do you have any ideas on how to use this functionality within real projects? Have you ever used the Text parameter in the OnLookup trigger? Please share your thoughts in the comment section.

How to install Business Central (developer) preview version

In the last weeks, I got some questions about installing the developer’s preview version (the Insider build) of the Business Central. This version is available through Artifacts for docker similarly to standard versions; however, they are available only for Microsoft’ partners.

If you are a partner, you can find your access code directly in the Microsoft Partner Center (Collaborate -> Overview -> Packages -> Working with Business Central Insider Builds). On this page, you can download the .txt file that contains sasToken (token you need to download any of insiders builds).

Creating the container is similar to creating the standard container; the only difference is the “sasToken” parameter in the Get-BCArtifactUrl command. See the whole command below.


 $sasToken = "YOUR-OWN-TOKEN-FROM-PARTNER-CENTER"
 Measure-Command {
   $artifactUrl = Get-BCArtifactUrl -country base -select nextmajor -sasToken $sasToken
   $credential = New-Object pscredential 'UserName', (ConvertTo-SecureString -String 'Password' -AsPlainText -Force)
   New-BcContainer          
     -accept_eula
     -containerName Your-Container-Name          
     -artifactUrl $artifactUrl
     -Credential $credential          
     -auth UserPassword
     -updateHosts          
     -imagename Your-Own-Image-Name
     -includeTestToolkit `
     -licenseFile 'C:\PartnersLicense.flf'
 }

Visual Studio Code AL Language extension from Insider’s build

With the new major version, there are always many changes to development. To use these changes and try to create new objects, new structures, or check changes to standard procedures, you have to use the new version of the AL Language extension for Visual Studio Code.

The new version is not available directly through Visual Studio Code until the full version of the Business Central is officially published. However, the extension is available directly on the image you downloaded when the container was created (it could be a little different path in your case):

C:\bcartifacts.cache\sandbox\18.0.22317.0\platform\ModernDev\program files\Microsoft Dynamics NAV\180\AL Development Environment

This .VSIX package can be installed manually in Visual Studio Code (Extensions -> … -> Install from VSIX…).

A quick look into the upcoming major version in April 2021 (Business Central 2021 release wave 1) from the developer’s point of view

It is only four months since the last major version of the Business Central (2020 wave 2) has been released, and we can already look forward to another version that will be released in April.

Microsoft has already provided the list of planned and confirmed functionality (available here). Let’s look at some of the planned features that will be usable (or, at least available) for programmers.

Most useful changes for developers

Extensible reports

One of the most annoying things for customising in the AL language are definitely reports. Till now, to add own field to the existing report from the base app or another extension, there was no other way but to copy the report and do the change in this copy.

With the upcoming version, developers will be able to create a report extension objects. Using this extension object type, we will be able (at least) to add new fields to existing datasets and request pages.

In my opinion, this is one of the most critical changes and as so I will look at it in another article once it is available in public preview.

Returning of complex types from AL procedures

One of the greatest news is that with the new update almost every complex type (record, page, query, list, …) can be returned from a procedure. Let’s see an example:


 procedure GetSellToCustomer(): Record Customer
 var
   Customer: Record Customer;
 begin
   Rec.TestField("Sell-to Customer No.");
   if Customer.Get("Sell-to Customer No.") then
     exit(Customer);
 end;

As this is a big change and as it is something known and often used in almost any other language, I will look at it in details in the next weeks.

Ability to add own keys/indexes to base table/tableextension

To increase custom processes’ performance, Microsoft added support of adding own keys to original tables and tables from any extension.

Entitlement and PermissionSet objects

There will be new object types for better administration and security of user’s roles: Entitlements, PermissionSets and PermissionSetExtension.

PermissionSets (and PermissionSetExtension) are already familiar to anybody. However, in current versions of the Business Central, they are stored and managed directly in the Modern Client.

Entitlements is a new object type that will allow specifying, which objects are available for specific users based on their assigned license (Full, Team Member, …) or Azure Active Directory role.

Easier working with 1-D barcodes in report layouts

There are new fonts available to use in report layouts. These fonts allow generating one-dimensional barcodes. For now (2021/02) licensed fonts are as follow

Some of other improvements

  • Availability to specify report layout both using AL or directly in the Modern Client (so as developers, we can use the same report for more layouts dynamically)
  • Performance improvements to Role Centers. The content of the Role Center is loaded in a similar way to FactBoxes. That means that Role Centers are loaded part by part from top-level (from menus), and only the visible parts are loaded. For developers, that is the improvement that helps with the performance a lot as users will be able to use menu actions without waiting for bricks and other stats shown on the main page.
  • Interfaces can be obsoleted as any other objects (In my opinion, this is a fix, not an improvement…)
  • Lookups triggers will return record link of selected record instead of its name.
  • Many improvements to the extensibility of reservation management. I will cover this topic as a separate article in the upcoming weeks.

ALRM: AL ID Range Manager introduction

AL ID Range Manager is VS Code & Microsoft Dynamics 365 Business Central extensions made for developers who need to manage their object/field IDs across multiple projects (or in the multi-developers environment).

Source codes are available online on GitHub

The project was originally created as a part of our project for hackathon Coding4BC that took place in November 2020 (https://www.coding4bc.com).

Made by developers for developers.

Partial Records in detail (part 2)

This is another article about Partial Records (as we already discussed earlier in the article about Partial Records and later in the first part of articles about partial record’s performance). In this article, we will explore the ration between performance, partial records and system with more tableextensions.

Let’s start with the example of our AL Code which we use for performance testing. This code is very similar to the previous article’s code; we only removed parts that were linked to the JIT loading example.

 report 90001 "TKA Partial Records 2"
 {
     Caption = 'Partial Records 2';
     UsageCategory = Administration;
     ProcessingOnly = true;
     ApplicationArea = All;

     trigger OnPostReport()
     var
         GLEntry: Record "G/L Entry";
     begin
         ForEachGLEntries(GLEntry);
         Clear(GLEntry);

         GLEntry.SetLoadFields(Amount);
         ForEachGLEntries(GLEntry);
     end;

     procedure ForEachGLEntries(var GLEntry: Record "G/L Entry"): Decimal;
     var
         SumTotal: Decimal;
         Counter: Integer;
         TempName: Text;
     begin
         if GLEntry.FindFirst() then
             repeat
                 SumTotal += GLEntry.Amount;
                 Counter += 1;
             until GLEntry.Next() < 1;
         Message(TempName);
         exit(SumTotal / Counter);
     end;
 }

The first example is trivial. We just run our AL example without any change on the empty Cronus database. Unsurprisingly, if we loop through all G/L Entry table records, generated SQL Command load all fields.


 SELECT 
      "17"."timestamp",
      "17"."Entry No_",
      "17"."G_L Account No_",
      ... All Table Fields (56 fields hidden) ...
      "17"."Last Modified DateTime"
      "17"."$systemId",
      "17"."$systemCreatedAt",
      "17"."$systemCreatedBy",
      "17"."$systemModifiedAt",
      "17"."$systemModifiedBy"
 FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17"  WITH(READUNCOMMITTED)  
 ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

If we change our code to use SetLoadField method and specifies all fields that we need, the generated SQL Command is optimized. It contains fewer fields (= less data that needs to be transferred from a database server to an application server).

 
 SELECT 
      "17"."timestamp",
      "17"."Entry No_",
      "17"."G_L Account No_",
      "17"."Amount",
      "17"."Bal_ Account Type",
      "17"."Source Type",
      "17"."FA Entry Type",
      "17"."$systemId",
      "17"."$systemCreatedAt",
      "17"."$systemCreatedBy",
      "17"."$systemModifiedAt",
      "17"."$systemModifiedBy" 
 FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17"  WITH(READUNCOMMITTED)  
 ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

Although it could look like a significant difference, from database server performance point of view, both queries are almost the same (the first one needs a bit more performance to load & process additional unnecessary fields).

HOWEVER, the biggest difference comes with the environment, which uses table extensions!

Table Extension impacts on performance.

Let’s imagine that we have an environment that has our extensions (that has one table extension on G/L Entry table with two Text[2048] fields) and also another 3 AL Extensions each with G/L Entry table extension each with two Text[2048] fields. For example, the table extension can look like:


 tableextension 90003 "TKA G/L Entry Extension 4" extends "G/L Entry"
 {
     fields
     {
         field(90006; "TKA Veeery Long Description 7"; Text[2048])
         {
             Caption = 'Veeery Long Description 7';
             DataClassification = CustomerContent;
         }
         field(90007; "TKA Veeery Long Description 8"; Text[2048])
         {
             Caption = 'Veeery Long Description 8';
             DataClassification = CustomerContent;
         }
     }
 }

We have one table from the Base App, one Table from our Extension and another 3 Tables from other extensions.

Once we rerun the example, the generated SQL Command is much complicated, contains INNER JOIN for all tables and also in addition to standard table fields, also all fields from all tables extensions (and we have absolutely no control about their design and performance-requirements).

 
 SELECT 
      "17"."timestamp",
      "17"."Entry No_",
      "17"."G_L Account No_",
      ... All Table Fields (56 fields hidden) ...
      "17"."Last Modified DateTime",
      "17_e1"."TKA Veeery Long Description",
      "17_e1"."TKA Veeery Long Description 2",
      "17_e3"."TKA Veeery Long Description 5",
      "17_e3"."TKA Veeery Long Description 6",
      "17_e4"."TKA Veeery Long Description 7",
      "17_e4"."TKA Veeery Long Description 8",
      "17_e5"."TKA Veeery Long Description 3",
      "17_e5"."TKA Veeery Long Description 4",
      "17"."$systemId",
      "17"."$systemCreatedAt",
      "17"."$systemCreatedBy",
      "17"."$systemModifiedAt",
      "17"."$systemModifiedBy" 
 FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17"  WITH(READUNCOMMITTED)  
 JOIN "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$425df973-0cda-4173-9e7c-e91ae677bae1" "17_e1"  WITH(READUNCOMMITTED)  ON ("17"."Entry No_" = "17_e1"."Entry No_") 
 JOIN "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$fa14d844-0ad9-4210-9171-9a86c033de53" "17_e3"  WITH(READUNCOMMITTED)  ON ("17"."Entry No_" = "17_e3"."Entry No_") 
 JOIN "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$fa14d844-0ad9-4210-9271-9a86c033de53" "17_e4"  WITH(READUNCOMMITTED)  ON ("17"."Entry No_" = "17_e4"."Entry No_") 
 JOIN "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$fa14d944-0ad9-4210-9171-9a86c033de53" "17_e5"  WITH(READUNCOMMITTED)  ON ("17"."Entry No_" = "17_e5"."Entry No_") 
 ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

Nevertheless, the second command generated absolutely same SQL Command as earlier when we had no additional tables installed. The performance difference is obvious now.

 
 SELECT 
      "17"."timestamp",
      "17"."Entry No_",
      "17"."G_L Account No_",
      "17"."Amount",
      "17"."Bal_ Account Type",
      "17"."Source Type",
      "17"."FA Entry Type",
      "17"."$systemId",
      "17"."$systemCreatedAt",
      "17"."$systemCreatedBy",
      "17"."$systemModifiedAt",
      "17"."$systemModifiedBy" 
 FROM "CRONUS".dbo."CRONUS International Ltd_$G_L Entry$437dbf0e-84ff-417a-965d-ed2bb9650972" "17"  WITH(READUNCOMMITTED)  
 ORDER BY "Entry No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

SQL stats

What is behind these SQL Commands? In the previous article, we saw that the environment’s execution plan without any extension is not really interesting… It is just one Index Scan from the table itself.

However, the same Plan is used for environments with table extensions too, if we use fields specified using SetLoadFields! Furthermore, if we use the “old” approach (= without using SetLoadFields), SQL Server must join all table extension using INNER JOIN command that results in much complicated (and much performance-consuming)! It can be easily seen in the picture below; for example, the relative query cost of the command generated using SetLoadField is 4% compared to 93%! That is a huge difference and could result in many troubles and performance issues.

Conclusion

To summarize, it is essential to start using Partial Records for any logic with bigger-than-small complexity. Furthermore, it is crucial to minimize the number of extensions on tables, which can have many rows and/or are used within standard processes (which unfortunately does not use Partial Records yet).

AL ID Range Manager is available!

I am glad to announce that the very first version of AL ID Range Manager is available for everyone on Visual Studio Code Marketplace!

AL ID Range Manager is an extension for VS Code that helps with managing AL objects between developers who are working on the same project / within the same object license range. The source codes are available to everyone: https://github.com/TKapitan/ALRM-VSCode

Do you want to try it?

If you want to use this extension, just type “AL ID Range Manager” (or “Kepty” to list all my extensions) and select install like any other extension you are using.

To set up the extension before the first use, see README directly on my GitHub.

Let me know what you think! Do you have an idea, or have you found a bug? Please report it on the GitHub as an Issue.