Record.SetCurrentKey(Any [, Any,...]) Method

Article
02/26/2024

Version: Available or changed with runtime version 1.0.

Selects a key for a table.

Syntax

[Ok := ]  Record.SetCurrentKey(Field1: Any [, Field2: Any,...])

Parameters

Record
Type: Record
An instance of the Record data type.

Field1
Type: Any

[Optional] Field2
Type: Any

Return Value

[Optional] Ok
Type: Boolean
true if the operation was successful; otherwise false. If you omit this optional return value and the operation does not execute successfully, a runtime error will occur.

Remarks

You can use SetCurrentKey to hint a sort order to the Business Central server. With the fields suggested in SetCurrentKey, the Business Central server then searches available key definitions and adds an ORDER BY clause with the fields from the key to the SQL statement issued to the database.

When you use SetCurrentKey, the following rules apply:

You can't use SetCurrentKey on:
- FlowFilter fields
- Nested FlowFields
- FlowFields on ExternalSQL tables
- Blob Fields
- FlowFields on Virtual tables
- FlowFields that query across app / tenant db
- FlowFields that query different table types
Only active keys are considered.
When the system searches for a key, the first occurrence of a key that starts with the specified fields is selected, which means:
- Fields that are part of a IncludedFields definition aren't used when searching for a matching key.
- If you specify only one field as a parameter when you call SetCurrentKey, the key that is selected might consist of more than one field.
- If the field that you specify is the first component of several keys, the key that is selected might not be the key that you expect.
- If no keys can be found that include the fields that you specify, the return value is false.

Example - robust coding

When writing AL code, you should always assume that calls to SetCurrentKey might fail. Use the following code snippet to write robust AL code that doesn't break if table keys change.

    var
        MyRecord: Record MyTable;
    begin
        if not MyRecord.SetCurrentKey(Field1, Field2) then
          // handle if no key was found

If a SetCurrentKey call fails, an error message is shown to the user:

Sorting cannot be done based on the {field1 [, field2]*} field(s) in the {table name} table.

Page {page name} has to close.

Example - successful SetCurrentKey call

Suppose that you have the following table with keys defined:

table 50100 MyTable
{
    fields
    {
        field(1; Field1; Integer)
        {
        }
        field(2; Field2; Integer)
        {
        }        
        field(3; Field3; Integer)
        {
        }
        field(4; Field4; Integer)
        {
        }

        field(5; BlobField; Blob)
        {
        }
    }

    keys
    {
        key(Key1; Field1, Field2) 
        {
        }

        key(Key2; Field3, Field1, Field2)
        {
        }

        key(Key3; Field1, Field2, Field4)
        {
        }

        key(Key4; Field1, Field2)
        {
            IncludedFields = Field5;
        }
    }
}

In AL code, you then use the SetCurrentKey method to imply your preferred sort order of the data:

    procedure MyDataProc()
    var
        MyRecord: Record MyTable;
    begin
        if not MyRecord.SetCurrentKey(Field1, Field2) then
          // handle if no key was found 

        if not MyRecord.Find then
          // handle no records
        
        // more code here
    end;

In the previous code example, the Business Central server searches for a key that starts with the fields Field1 and Field2. For the table MyTable, two such keys exist: Key1 and Key3. It's not deterministic which of the keys the Business Central server will use. For illustration, assume that the Business Central server picks Key3. The SQL statement generated by the Business Central will then look something like this:

SELECT Field1, Field2, Field3, Field4, Field5
  FROM MyTable
 // this ORDER BY clause is added by the Business Central server due to the SetCurrentKey call 
 // assuming that the server picks Key3  
 ORDER BY Field1, Field2, Field4

In the end, the SQL query optimizer might choose not to use the index corresponding to Key3, if it determines that it can get a faster execution plan without using the index.

Example - unsuccessful SetCurrentKey call without a runtime error

Assume that you have the MyTable table as defined in the previous example.

The following example illustrates the behavior of SetCurrentKey if no keys can be found.

    procedure MyDataProcNoKeyFound()
    var
        MyRecord: Record MyTable;
    begin
        if not MyRecord.SetCurrentKey(Field4) then
          // handle if no key was found 

        if not MyRecord.Find then
          // handle no records
        
        // more code here
    end;

In the previous code example, the Business Central server searches for a key that starts with the field Field4. For the table MyTable, no such key exists. So, the Business Central server returns false, but still adds an ORDER BY clause and doesn't raise a runtime error. In this case, the SQL statement generated by the Business Central will then look something like this:

SELECT Field1, Field2, Field3, Field4, Field5
  FROM MyTable
 // this ORDER BY clause is added by the Business Central server due to the SetCurrentKey call 
 // assuming that the server could not find any key
 ORDER BY Field4

Example - unsuccessful SetCurrentKey call with runtime error

Assume that you have the MyTable table as defined in the previous example.

The following example illustrates the behavior of SetCurrentKey if no keys can be found and if the SetCurrentKey was using a field that isn't supported in SetCurrentKey (in this case, a field of data type Blob).

    procedure MyDataProcNoKeyFound()
    var
        MyRecord: Record MyTable;
    begin
        if not MyRecord.SetCurrentKey(BlobField) then
          // handle if no key was found 
          // VERY IMPORTANT AS IN THIS CASE, THE SERVER WILL RAISE A RUNTIME ERROR

        if not MyRecord.Find then
            // handle no records
        
        // more code here
    end;

In the code example, the Business Central server searches for a key that starts with the field BlobField. For the table MyTable, the field is non-searchable, so the Business Central server returns false. The Business Central server doesn't add an ORDER BY clause in this case and raises a runtime error (the error surfaces to the user if it's not handled). In this case, the SQL statement generated by the Business Central will then look something like this:

SELECT Field1, Field2, Field3, Field4, Field5
  FROM MyTable
 // no ORDER BY clause added by the Business Central server due to the SetCurrentKey call