Partial Records

by Oct 22, 2020AL Language, SQL

Home 9 Development 9 AL Language 9 Partial Records

Partial Records are a new capability of Business Central introduced in Business Central 2020 release wave 2. It allows specifying fields that should be loaded when accessing SQL based data.

How does it work? Without using partial records, Business Central normally load all data from the record even if only one of them is required. The partial records, developer specify which fields are needed and the Business Central than load only that fields.

This is especially (based on Microsoft notes) important when Tableextensions are used. In that case, each Tableextension is stored as a separate table on the SQL server and, when the data from this table are loaded, SQL server joins the primary table, extension table (and other extension tables if exist) using join. Although the join is done using primary keys, the query is still much more performance-intensive than the query that accesses only one table.

Related Functions

There are four related functions:

  • [Ok := ] Record.SetLoadFields([Fields: Any,…])
    • Using the function SetLoadFields we specify a set of fields that server should load from the database. Fields have to be specified before the record is retrieved from the database (similar as SetAutoCalcFields()).
    • If the function is called more than once, only fields specified within the last call are loaded.
  • [Ok := ] Record.AddLoadFields([Fields: Any,…])
    • Similar function to SetLoadFields that has one big difference: if the function is called multiple times, the new call does not reset fields that were already set (even if they were set using SetLoadFields).
  • Ok := Record.AreFieldsLoaded(Fields: Any,…)
    • Checks whether the fields specified as parameters are already retrieved from the database.
  • [Ok := ] Record.LoadFields(Fields: Any,…)
    • This function allows loading data from the data source that was not loaded when the last partial record was retrieved (because the field was not specified using SetLoadFields nor AddLoadFields).

More specifically

In the first part, I wrote that only specified fields are loaded when the row is fetched from the database; however, if the fields were not loaded (using SetLoadFields / AddLoadFields) and system needs their value, they are automatically fetched using Just-In-Time (JIT) mechanism.

When JIT loading occurs, the system automatically loads data using the primary keys of the current record. This fetch might fail if the record was changed (modified / renamed / deleted) since the original data was retrieved. And that is the reason why the function LoadFields exists – when JIT loading occurs automatically, there is no way how to resolve if the loading fails. With LoadFields developers can implement explicit error handling for these fails.

Example

The example below is from Microsoft Docs and shows how to use SetLoadFields. As is mentioned on the Docs, in this case, the example is nine times faster than the same code without the Partial Records functionality.

 procedure ComputeArithmeticMean(): Decimal;
 var
     Item: Record Item;
     SumTotal: Decimal;
     Counter: Integer;
 begin
     Item.SetLoadFields(Item."Standard Cost");
     if Item.FindSet() then begin
         repeat
             SumTotal += Item."Standard Cost";
             Counter += 1;
         until Item.Next() = 0;
         exit(SumTotal / Counter);
     end;
 end

Microsoft example

As this is a very different approach to developing custom functionalities, I will definitely check the performance with some advance example codes in some of the next articles.

Recent Articles from the category

Connect to Azure Function in BC 2022 wave 2 (v21)

Connect to Azure Function in BC 2022 wave 2 (v21)

The new version of the Microsoft Dynamics 365 Business Central brought a new system module "Azure Functions" that makes integration with Azure Functions much easier and straightforward. The typical scenario, why to use Azure Functions together with Business Central,...

read more
Collectible Errors?! Is it already in use?

Collectible Errors?! Is it already in use?

Collectible Errors?! Is it already in use? This is the second part of my new article series about Collectible Errors. Let's check out the first part here: Collectible Errors?! | Microsoft Dynamics 365 - Ing. Tomáš Kapitán (kepty.cz) or you might be interested in my...

read more
Collectible Errors?!

Collectible Errors?!

Collectible Errors?! It has been already almost a year since ErrorInfo datatype & CollectibleErrors were introduced (I already have an article about basic structure: ErrorInfo data type & Collectible Errors). This article was released for the first time in...

read more
Substituting standard reports

Substituting standard reports

Report objects cannot be extended in versions previous to the Business Central 2021 release wave 1 version when ReportExtensions object was introduced. Since then, many of changes we need to do in reports can be done without creating a new copy of object. On the other...

read more
Isolated events

Isolated events

With Business Central 2022 wave 1, a new setting for event publishers was introduced. Until this version, any error in any event subscriber caused interruption to the current running process and stopped the whole activity. In some cases (such as log-in), this is...

read more
Custom Filter Tokens

Custom Filter Tokens

As a user of the Business Central you have some constants you can use to filter or insert values. These constants contain useful values for data manipulation such as t / today for date field - return current dateq / quarter for date field filters - return range of the...

read more
1D & 2D Barcodes in Business Central 19.1

1D & 2D Barcodes in Business Central 19.1

Business Central (cloud-only!) includes one-dimensional (1D) barcode fonts since Microsoft Dynamics 365 Business Central 2020 wave 2 (17.0) was released in October 2020. It is a great improvement to how developers can handle requirements from their clients to print...

read more

Sign Up for News

Certifications

Highest certification
Microsoft Data Management and
also in D365 Business Central

Microsoft Certified: Dynamics 365 Business Central Functional Consultant Associate

See other certifications here