Retrieve data from the database with AL statements

  • 12 minutes

To retrieve data from the database, you can use data access functions.

You can use the following data access functions to get records:

  • Get

  • Find, FindFirst, FindLast

  • Next

  • IsEmpty

Get function

The Get function retrieves a record based on the primary key. If your table has a primary key of one field, you need to provide one parameter to the Get function. If the primary key is a combined key of multiples fields, you need to provide them in the order of the primary key definition.

In the following example, a record with primary key 30000 from the Customer table is retrieved. Because the primary key of a Customer table is of type Code[20], you need to specify the value as a string. The best way to use the Get function is within an if statement.

If the record isn't found, and you're not using an if statement to check the result of the Get statement, you generate an error in the code.

var
    customer: Record Customer;
begin
    customer.Get('30000');

    if customer.Get('10000') then
        Message('%1', customer)
    else
        Error('Not found');

end;

Find function

The Find function has two derived functions: FindFirst and FindLast. In some standard code, the Find('-') and Find('+') functions continue to be used. These functions were used in older versions of Business Central.

The result of Find('-') and FindFirst is the same, but how the result is requested differs. With the FindFirst function, Business Central asks SQL Server to retrieve the first record in the database by using a SELECT TOP 1 SQL query.

The Find('-') function requests a SELECT from SQL Server and retrieves the first record in Business Central. This process leads to bad performance. Use the FindFirst and FindLast functions instead.

var
    customer: Record Customer;
begin
    customer.Find('-');
    // SELECT * FROM Customer
    Message('%1', customer.Name);

    customer.Find('+');
    // SELECT * FROM Customer Order By No. Desc
    Message('%1', customer.Name);

    customer.FindFirst();
    // SELECT TOP 1 * FROM Customer
    Message('%1', customer.Name);

    customer.FindLast();
    // SELECT TOP 1 * FROM Customer Order By No. Desc
    Message('%1', customer.Name);

To retrieve all records (or a filtered set of records), you can use the FindSet function.

customer.FindSet();
// SELECT * FROM Customer

Next statement

The Next statement is used to get the next record in a record set. Typically, you would use the Next statement in a repeat until statement. The Next statement optionally has one parameter that defines how many steps you want to go forward. If you give the parameter a value of 3, it always gets every third record on the list.

customer.FindSet();
// SELECT * FROM Customer
repeat
    // process record
    Message(customer.Name);
until customer.Next() = 0;

IsEmpty function

If you want to check if a record exists, but you don't want to retrieve that record, then it's better to use the IsEmpty function.

AL database methods and performance on SQL Server

The AL language offers several methods to retrieve record data. In Dynamics 365 Business Central, records are retrieved using multiple active result sets (MARS). Generally, retrieving records with MARS is faster than with server-side cursors. Additionally, each function is optimized for a specific purpose. To achieve optimal performance, you must use the method that is best suited for a given purpose.

  • Record.Get is optimized for getting a single record based on primary key values.

  • Record.Find is optimized for getting a single record based on the primary keys in the record and any filter or range that has been set.

  • Record.Find('-') and Record.Find('+') are optimized for reading primarily from a single table when the application might not read all records. Find('-') is implemented by issuing a self-tuning TOP X call, where X can change over time, based on statistics of the number of rows read. The following are examples of scenarios in which you should use the Find('-') function to achieve optimal performance:

    • Before you post a general journal batch, you must check all journal lines for validity and verify that all lines balance. After the first line when an error is found, you don't have to retrieve the rest of the rows.

    • If you want to fulfill multiple outstanding orders from a recent purchase, but you don't know how many orders are covered by the purchase.

  • Record.FindSet(ForUpdate, UpdateKey) is optimized for reading the complete set of records in the specified filter and range. The UpdateKey parameter doesn't influence the efficiency of this method in Dynamics 365 Business Central, such as it did in older versions of the product. FindSet isn't implemented by issuing a TOP X call.

  • Record.FindFirst and Record.FindLast are optimized for finding the single first or last record in the specified filter and range.

  • Record.Next can be called at any time. However, if Record.Next isn't called as part of retrieving a continuous result set, then Business Central calls a separate SQL statement in order to find the next record.