Category Archives: C/AL Language

C/AL + AL: SetAutoCalcFields

SetAutoCalcFields is very similar method to CalcFields method. The only difference is that CalcField is run on the records already loaded from the database. On the other hand, SetAutoCalcFields is set before the records are loaded from the database.

Although it can be seen as a small difference, it could have a huge impact on performance, especially if the FlowField’s values are used in loops.

Imagine, you want to check the value of the FlowField on each record from the Item table. That will be probably done using FindSet/repeat statements. It can be definitely done using CalcFields in a repeat loop, however, it will generate SQL query for each cycle of repeat statement. So, in case of production environment with 200 000 items, it will generate 200 000 queries just to load the value of the FlowField.

However, if the SetAutoCalcFields method is used, it will generate much less number of queries.

Let’s look on the example

 // Load FlowField using CalcFields method
 if Item.FindSet() then
   repeat
     // CalcFields statement generates SQL query for each Item
     Item.CalcFields(Inventory); 
     Message('Inventory of item no. %1 is %2.', Item."No.", Item.Inventory);
   until Item.Next() < 1; 

 // Now, let's do the same using SetAutoCalcFields
 Item.SetAutoCalcFields(Inventory); 
 if Item.FindSet() then
   repeat
     Message('Inventory of item no. %1 is %2.', Item."No.", Item.Inventory);
   until Item.Next() < 1; 

C/AL + AL: CalcFields

CalcFields is a method to calculate the FlowFields in a record.

Flowfields are special fields (virtual) that are not stored physically in the database/table. Standard fields are loaded once the record is retrieved from the database. However, as the flowfields are virtual and show data (or grouped data) from another table, calculating these fields means to query another table with another SQL statement. As this can (and usually is) very performance-intensive task, FlowFields are not automatically calculated when the record is loaded from the database.

For fields shown on pages, values are automatically loaded (as if the field is shown on the page, it is usually intended to show the value of the field. However, if a developer works with FlowField in AL or C/AL language and needs to work with its value, the value must be calculated manually first (using CalcFields method).

See example below

 SalesHeader.SetRange("Document Type", SalesHeader."Document Type"::Order);
 SalesHeader.SetRange("Date Filter", 010119D, 123119D);
 if SalesHeader.FindSet() then
   repeat
     // Nothing is shown as the FlowField is not calculated
     // Shown message: "Order XYZ: 0"
     Message('Order %1: %2', SalesHeader."No.", SalesHeader.Amount);

     // Calculate Amount field using CalcFields method
     SalesHeader.CalcFields(Amount); 

     // Now the message show right Amount of each Sales Order
     // Shown message: "Order XYZ: 123 665.23"
     Message('Order %1: %2', SalesHeader."No.", SalesHeader.Amount);
   until SalesHeader.Next() < 1; 

Optimized Progress Dialogues in C/AL

In the previous articles (see here) we discussed how to build standard progress dialogue in C/AL language. However, as mentioned at the end of the article, this way is definitely not applicable to a production environment due to the performance issues. In this article, we will look at how to build dialogues more efficiently with lower (and usually acceptable) impact on performance…

Let’s look at our example from the previous article.

 Local variables: 
   DialogVar (Dialog)
   MyRecord (Record of any table)
 --------------------
 Code:
   DialogVar.OPEN('My own text');
   REPEAT
     DialogVar.UPDATE();
   UNTIL MyRecord.NEXT < 1
   DialogVar.CLOSE();

What are the biggest drawbacks of this design? Definitely, one of them is performance. However, usually, the user needs (or want) to see more information about the progress. Using this code, the user sees only static text “My own text” and has no information about the current state of progress. Let’s look at both issues in more detail.

Progress Information

To show more details about current progress, we have two possible ways. One of them is to show custom string values (for example, document number, customer number, …). Another possibility is to show progress in percentage to indicate how many cycles (in comparison to the total number of cycles) has been done/remains.

To specify what should be shown and how the progress should be displayed, we use placeholders. Placeholders are special characters that are placed in the text we want to show in the position, where the value should be shown. The number of placeholder characters specifies the length of the variable field.

We have two types of placeholders. For custom string values, we use “#”, for percentage, we use “@”. In one text label, more values can be shown. To achieve it, the placeholders must be indexed. The index must be defined as a second character just after the first placeholder.

For example:

  • #1##########
    • First variable field that shows custom strings.
  • @2@@@@@@@
    • Second variable field that shows percentage progress dialogue.
    • The percentages are defined as values 0 to 9999. That means that to display a percentage progress indicator properly, we need to normalize the value to this range.

Once we have text with placeholders defined, we can change our UPDATE function usage and add parameters. As we discussed in the first article, the first parameter is the index of placeholder and the second one value. Let’s look at the example below.

 Local variables: 
   DialogVar (Dialog)
   MyRecord (Record of any table)
   Counter (Integer)
   TotalCounter (Integer)
 --------------------
 Code:
   Counter := 1;
   TotalCounter := MyRecord.COUNT();
   DialogVar.OPEN('Current doc. no #1####### \ Current progress @2@@@@@@');
   REPEAT
     DialogVar.UPDATE(1, MyRecord."Document No.");
     DialogVar.UPDATE(2, (Counter / TotalCounter * 10000) DIV 1);

     Counter += 1;
   UNTIL MyRecord.NEXT < 1
   DialogVar.CLOSE();

As mentioned above, the value of the percentage indicator has to be normalized to range 0 to 9999. In the example above, we did it using “(Counter / TotalCounter * 10000) DIV 1”. This statement calculates how many cycles have been done (in per cent), multiple it by 10000 to normalize it within 0-9999 and cast it as an integer value.

Performance

The biggest issue from performance point of view is the complexity of UPDATE method. Even if the indicator should shown the same value, the system still needs to update the UI. The update of UI makes UI responsible (so the user does not see unresponding application) but it is not necessary to update the dialog each time the function is run.

So, we have two ways how to solve it. We can update the dialog only each Xth run (for example every 100th run). I used it in the past, however, I found that it is not ideal solution. The biggest drawback is that for tasks where one run took a longer time, the application can still be unresponding.

Better approach, in my opinion, is to update UI every second. See example below.

 Local variables: 
   DialogVar (Dialog)
   MyRecord (Record of any table)
   Counter (Integer)
   TotalCounter (Integer)
   LastUpdateAt (DateTime)
 --------------------
 Code:
   Counter := 1;
   TotalCounter := MyRecord.COUNT();
   DialogVar.OPEN('Current doc. no #1####### \ Current progress @2@@@@@@');
   REPEAT
     IF LastUpdateAt < (CURRENTDATETIME() - 1000) THEN BEGIN
       LastUpdateAt := CURRENTDATETIME();
       DialogVar.UPDATE(1, MyRecord."Document No.");
       DialogVar.UPDATE(2, (Counter / TotalCounter * 10000) DIV 1);
     END;

     Counter += 1;
   UNTIL MyRecord.NEXT < 1
   DialogVar.CLOSE();

Progress Dialogues in C/AL

One of the most neglected things I found on projects I have reviewed is progress dialogue for tasks, that run for more than a seconds. Although it is not always easy to estimate how long the task will run on production data, it is necessary to use progress dialogue as much as possible.

On the other hand, the badly designed dialogue could lead to major performance issues, so it is crucial to know how to work with dialogues.

Example

Let’s start with how dialogues are created. In C/AL language, dialogues are created using Dialog data type variable. From all functions that are defined for the Dialog data type, we use

  • OPEN() function to open a new dialogue. It has one required parameter – text of the dialogue that could have placeholders to show a value from the UPDATE function or percentage value.
  • UPDATE() to update the value of the dialogue. It has two parameters, the first one that specifies field we want to update (the number is defined as an order of the placeholders in the text that was used as a parameter for OPEN function). The second one specifies the value of the placeholder. If the function is called without any parameter, it just updates the dialogue, and so the application is in responding state (if we do not use UPDATE function and the loop took some time, the application just froze and the user may be notified about unresponding application).
  • CLOSE() to close the dialogue.

So the easiest dialogue could be created with only these three lines. Before the loop, open the dialogue, update it in each run of the loop and once the loop is exited, close the dialogue (see the code below).

 Local variables: 
   DialogVar (Dialog)
   MyRecord (Record of any table)
 --------------------
 Code:
   DialogVar.OPEN('My own text');
   REPEAT
     DialogVar.UPDATE();
   UNTIL MyRecord.NEXT < 1
   DialogVar.CLOSE();

We created a basic example of how to use progress dialogues. HOWEVER, this approach is really not ideal, and in loops with thousands of records, the impact on the performance will be significant.

The next article will describe how to develop performance-optimized progress dialogues and how to avoid some of the classic problems.