Record.SetCurrentKey(Any [, Any,...]) Method
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
These fields aren't sortable. If any of these fields are used in the method, it returns
falseat runtime and an error occurs if not handled.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.
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; Field5; Code[10])
{
FieldClass = FlowFilter;
}
}
keys
{
key(Key1; Field1, Field2)
{
}
key(Key2; Field3, Field1, Field2)
{
}
key(Key3; Field1, Field2, Field4)
{
}
key(Key4; Field2, Field3)
{
}
}
}
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 uses. For illustration, assume that the Business Central server picks Key3. The SQL statement generated by the Business Central then looks 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. However, the Business Central server returns true, doesn't raise a runtime error, and still adds an ORDER BY clause. In this case, the SQL statement generated by the Business Central then looks 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 the SetCurrentKey uses a field that isn't supported in SetCurrentKey. In this case, the field has the data type FlowField.
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 Field5. For the table MyTable, the field is nonsearchable, 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 then looks 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