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;