Tag: sql

Optimizing Business Central Reports with the DataAccessIntent Property

When developing reports in Microsoft Dynamics 365 Business Central, performance and scalability are crucial—especially in cloud environments where system efficiency impacts everything from responsiveness to cost. One often-overlooked feature that can significantly enhance report performance is the DataAccessIntent property.

What Is DataAccessIntent?

The DataAccessIntent property is available on report objects in AL. It specifies how a report should access the database—whether it’s read-only or read-write.

The property supports two values:

  • DataAccessIntent = ReadOnly
  • DataAccessIntent = ReadWrite

ReadOnly

When you set DataAccessIntent = ReadOnly, you are explicitly telling the platform that your report only needs to read data from the database and will not perform any write operations.

Why is this important?

  • Better Performance: In Business Central SaaS, reports marked as ReadOnly can run on read-only replicas of the database. This reduces the load on the primary (read-write) database and enhances scalability.
  • Improved Report Execution Time: Since queries are routed to optimized replicas, report rendering can be faster and more efficient.

ReadWrite

If your report needs to modify data during execution—a rare scenario—you should use DataAccessIntent = ReadWrite. This forces the report to run on the primary database.

However, you should avoid using ReadWrite unless absolutely necessary because:

  • It eliminates the benefit of using read replicas.
  • It may degrade performance, especially under high concurrency.

When to Use Each

ScenarioUse ReadOnly?Use ReadWrite?
Standard data listing/reporting✅❌
Reports that update records❌✅
Diagnostic or audit reports✅❌

How to Set DataAccessIntent in AL

report 50100 "Customer Balance Report"
{
    DataAccessIntent = ReadOnly;

    dataset
    {
        dataitem(Customer; Customer)
        {
            column(Name; Name) { }
            column(Balance; "Balance (LCY)") { }
        }
    }

    layout
    {
        // Define RDLC or Word layout
    }
}

By default, if you don’t specify the property, it behaves as ReadWrite. So it’s a good practice to explicitly set it to ReadOnly when applicable.

Important Considerations

  • The DataAccessIntent property is only a hint to the Business Central server. The server may not always be able to use a read-only replica, even if the property is set to ReadOnly.
  • If a report with DataAccessIntent set to ReadOnly attempts to modify data, a runtime error will occur.
  • The DataAccessIntent property can be overridden by the user through the “Database Access Intent List” page in Business Central.

In short: if your report doesn’t write data, use DataAccessIntent = ReadOnly. It’s an easy win for performance and best practice compliance.

Stay tuned for more….

Boosting Performance in Business Central with SetAutoCalcFields

In today’s fast-paced business environments, performance optimization is not a luxury — it’s a necessity. Whether you’re building custom reports, extending pages, or writing integration APIs in Microsoft Dynamics 365 Business Central, slow operations can frustrate users and strain resources.

One simple but powerful technique to improve performance is using the SetAutoCalcFields method.

Why FlowFields Matter for Performance

In Business Central, FlowFields are virtual fields calculated on the fly, not stored physically in the database. Examples include:

  • Inventory on Item
  • Balance on Customer

When you retrieve a record, FlowFields are not calculated automatically unless you explicitly call CalcFields().

Manually calculating FlowFields for every record during loops, leading to multiple SQL queries — one for each record — causing heavy load and slower performance.

SetAutoCalcFields solves this by batch-calculating FlowFields along with the record fetch.
Instead of running one query per record, it combines the calculation in a single optimized query.

Imagine fetching 1,000 customers and displaying their balances without SetAutoCalcfields:

CustomerRec.FindSet();
repeat
    CustomerRec.CalcFields(CustomerRec.Balance); // Triggers a DB call every time!
    DisplayBalance(CustomerRec."No.", CustomerRec.Balance);
until CustomerRec.Next() = 0;

Result:
  • 1 SQL query to get Customers
  • + 1,000 SQL queries for Balances

With SetAutoCalcFields:

CustomerRec.SetAutoCalcFields(CustomerRec.Balance);
CustomerRec.FindSet();
repeat
    DisplayBalance(CustomerRec."No.", CustomerRec.Balance);
until CustomerRec.Next() = 0;

Result:
  • 1 SQL query to get Customers and Balances together

Benefits of Using SetAutoCalcFields

  • Improved Page Load Times: By deferring calculations, pages with numerous records and calculated fields will load significantly faster.
  • Faster Report Generation: Reports that rely on calculated fields will be generated more quickly as the calculations are performed only when the field’s value is actually needed for display or processing.
  • Reduced Database Load: Fewer automatic calculations translate to fewer database queries, reducing the overall load on your Business Central database.
  • Enhanced User Experience: Snappier performance leads to a more responsive and enjoyable user experience.

Best Practices for Performance Gains

To maximize the benefits of SetAutoCalcFields:

  • Only specify necessary FlowFields:
    Don’t auto-calculate every FlowField — focus on what your process needs.
  • Use it before data retrieval:
    Call SetAutoCalcFields before FindSet(), FindFirst(), or FindLast()
  • Avoid unnecessary recalculations:
    Once FlowFields are set to auto-calculate, do not manually call CalcFields() again for the same fields.
  • Monitor heavy FlowFields:
    Some FlowFields (e.g., Inventory) involve complex sums across tables — only auto-calculate when really needed.
  • Profile your code:
    Use Performance Profiler to measure improvements.

Using SetAutoCalcFields properly can lead to dramatic performance improvements in Business Central.
By reducing SQL traffic, simplifying code, and batch-fetching FlowFields intelligently, you can create faster, cleaner, and more scalable applications.

A small change in your coding habits can create a big impact for your users.

Hope this will help..

FieldRef.IsOptimizedForTextSearch() Method in Business Central 2025 Wave 1

With the release of Business Central 2025 Wave 1, Microsoft continues to empower developers with more control and insights into the performance and behavior of their extensions. Among the new additions is the method FieldRef.IsOptimizedForTextSearch(), designed to help developers make more performance-conscious decisions when implementing search functionalities.

💡 What is FieldRef.IsOptimizedForTextSearch()?

FieldRef.IsOptimizedForTextSearch() is a method that returns a Boolean value indicating whether a particular field in a table is optimized for text search.

It is a method on the FieldRef data type, which is used in AL code to dynamically refer to fields of records, especially in scenarios involving field iteration, metadata handling, or dynamic filters.

✅ Syntax:

Boolean := FieldRef.IsOptimizedForTextSearch();

⚙️ How to Optimize a Field for Text Search

While IsOptimizedForTextSearch() only checks if a field is optimized, setting it up is done via the table metadata or through the table schema in AL.

To mark a field for text search:

field(10; Description; Text[100])
{
    Caption = 'Description';
    DataClassification = ToBeClassified;
    OptimizeForTextSearch = true;
}

Setting OptimizeForTextSearch = true; enables text search optimization (depending on SQL backend settings as well for on-premise).

Lets see how we can utlize above method to check optimize search

var
    MyRecordRef: RecordRef;
    MyFieldRef: FieldRef;
    IsOptimized: Boolean;
begin
    MyRecordRef.Open(Database::Customer);
    if MyRecordRef.FindSet() then begin
        MyFieldRef := MyRecordRef.Field(Name); // Let's check the "Name" field
        IsOptimized := MyFieldRef.IsOptimizedForTextSearch();

        if IsOptimized then
            Message('The "%1" field in the Customer table is optimized for text search.', MyFieldRef.Name())
        else
            Message('The "%1" field in the Customer table is NOT optimized for text search.', MyFieldRef.Name());
    end;
    MyRecordRef.Close();
end;

To be optimized for full-text search, a field typically needs:

  • A Text or Code data type.
  • An active index that supports full-text search (defined in the table metadata or via table extensions).
  • Proper settings in SQL Server or Azure SQL (if full-text search is enabled).

The FieldRef.IsOptimizedForTextSearch() method is a small but powerful tool in the AL developer’s toolkit. Whether you’re designing smarter search UIs or optimizing performance in large datasets, this method gives you the metadata visibility to make informed choices.

By leveraging this feature, you can:

  • Improve app performance
  • Avoid slow queries
  • Create better user experiences

Stay tuned for more..

Implicit Record and Record Ref Conversion in Business Central AL

When working with Microsoft Dynamics 365 Business Central, one of the most powerful capabilities is the dynamic handling of data using RecordRef. However, as a developer, you may have run into scenarios where you need to switch between the strongly typed Record and the flexible RecordRef—and wondered if there’s a clean way to do it.

Good news: Business Central now supports implicit conversion between Record and RecordRef

📘 Understanding the Core Types:

Before exploring the implicit conversion, it’s crucial to understand the distinct roles of Record and RecordRef:

  • Record: A strongly-typed variable that represents a specific table in the Business Central database. The compiler enforces type safety, ensuring that operations performed on a Record variable are valid for the defined table structure.
  • RecordRef: A more dynamic variable that provides a generic reference to any table in the Business Central database. It allows for runtime manipulation of table structures and data, often used in scenarios like generic data access, metadata exploration, and dynamic query building.

✅ Implicit Conversion – What’s New?

With recent updates, AL now supports implicit conversion:

  • From a Record to a RecordRef
  • From a RecordRef back to a Record (if the types match)

This means cleaner, safer, and more readable code without the boilerplate RecordRef.GetTable() or RecordRef.SetTable().

🔍 Example: Record to RecordRef

procedure LogAnyRecord(rec: RecordRef)
begin
    Message('Table ID: %1', rec.Number);
end;

procedure RunLogging()
var
    customer: Record Customer;
begin
    customer.Get('10000');
    LogAnyRecord(customer); // Implicit conversion from Record to RecordRef
end;

No need for recRef.SetTable(customer) — it’s handled under the hood.

🔄 Example: RecordRef to Record

procedure GetCustomerName(recRef: RecordRef): Text
var
    customer: Record Customer;
begin
    customer := recRef; // Implicit conversion from RecordRef to Record
    exit(customer.Name);
end;

Implicit conversions between Record and RecordRef bring AL language making it easier to write dynamic yet type-safe code. While it’s a small feature on the surface, it greatly enhances developer productivity and code clarity.

Stay Tuned for more updates.

What’s New for Developers (2024 Wave 1)

As you all aware of that new version of business central 2024 Wave 1 is knocking the door but before that we already got new AL language extension with many changes. The points which I can feel most useful as below

  1. Support for multiple extensions to same target and extensions and target in same app
    • This means if you are working in one of the app and based on some requirement if you want add field in one of the base table then you can create table extension for that table in the same app. Personally, I still not figure it out how I can use it in my application. (Lets give a try).
  2. Support ended for debugging Business Central server versions less than 20
    • Once you install this new version of AL language then Business Central server versions released before the April 2022 release (version 20) cannot be debugged with AL extension package versions that are greater than or equal to version 13.
  3. Tooltips on table fields
    • Introduced the tooltip property on table fields, which like the caption will be applied on page controls that reference the table field. This includes a code action that helps with moving the tooltip from page controls to table fields or cleans them up from the page in case of duplicates.
    • To get this property enabled please set runtime version as 13.0 in app.json

Note : If you already set tooltip property on table and then you try to move the page tooltip then you can see code actions is not available.

4. Placeholder text on Page Fields

Page fields can now leverage the InstructionalText and InstructionalTextML properties to define placeholders. These values will be showed when the field would otherwise be empty.

Using this new capability, you can provide example values to your users. This feature is currently supported only for text types like Text, BigText, Code, and Guid.

5. New “Create AL Project” button

  • This is replacement of AL:GO!




Keep exploring and stay in touch for more.