How to SetCurrentKey to Sort in a RecordRef – Nav / D365 Business Central

Recently I had the need to use a Secondary Key to sort a RecordRef. As I could not find another clear example, I decided to share my solution here.

Introduction

To make it easier to understand, let’s assume we have this Test table with only 2 fields, both Integers just to simply the example:

The primary key is on field “PK” and we also have a secondary key on field “Replication Counter”:

table 50100 Test
{
    Caption = 'Test';

    fields
    {
        field(1; PK; Integer) {}
        field(2; "Replication Counter"; Integer) {}
    }
    keys
    {
        key(PK; PK)
        {
            Clustered = true;
        }

        key(Key2; "Replication Counter") {}
    }
}

In real life we use the “Replication Counter” field and secondary key for a lot of tables, not just for one table. So this is why we have to use RecordRefs.

What we need to achieve

For understanding the concept, let’s say we just want to find the highest Replication Counter, greater than 100. In our example that would be 500.

So we need something like this:

FieldRef.SetFilter('>%1', 100);
RecRef.FindLast();

The problem

The above code will return the result 400 as the records are sorted by default using the Primary key, so the last record is the one with PK = 5 and Replication Counter = 400.

If we were using a Record variable it was very easy to use function “Record.SetCurrentKey(“Replication Counter”)” so result would be 500 because the content of the table would be sorted by “Replication Counter”.

But because we need to make a generic functionality that works for any table, we need to use RecordRef and there is no “SetCurrentKey” function on RecordRef.

The solution – How to Sort in a RecordRef

In order to make the RecordRef use the secondary key that we need, we have to:

– use KeyRef to retrieve the Index of our Secondary Key.
– use function RecRef.CurrentKeyIndex(ourKeyIndex) to set the current key of the table referred to by the RecordRef.

So, let’s first create a function that gets the index of the key. For this we first need to know which field(s) our key is using. In this example, we know the key contains just one field “Replication Counter”, so function will ask as arguments the “RecordRef” of the record and the “FieldRef” of the “Replication Counter” field:

local procedure GetReplicationCounterKeyIndex(RecRef: RecordRef; RepCounterFieldRef: FieldRef): Integer
    var
        i: Integer;
        KRef: KeyRef;
        FRef: FieldRef;
    begin
        For i := 1 to RecRef.KeyCount do begin
            KRef := RecRef.KeyIndex(i);
            IF KRef.Active then
                IF KRef.FieldCount = 1 then begin //we are looking for the key with only one field --> Replication Counter
                    FRef := KRef.FieldIndex(1);
                    IF FRef.Name = RepCounterFieldRef.Name then
                        exit(i);
                end;
        end;
    end;

This function loops through all the keys of the table refered to by the RecordRef and returns the index of the key if all below conditions are met:
– The Key is Active
– The Key contains just one field
– The Field in the key is the “Replication Counter” field.

We use this function in this way:

trigger OnAction()
var
     RecRef: RecordRef;
     FRef: FieldRef;
     ReplicationKeyIndex: Integer;
begin
     RecRef.Open(50100); //open RecordRef using table id.

     FRef := RecRef.Field(2); //get FieldRef using the field id.

     ReplicationKeyIndex := GetReplicationCounterKeyIndex(RecRef, FRef);
     IF ReplicationKeyIndex <= 1 then
             Error(MissingKeyReplicationCounterErr, RecRef.Name, FRef.Name);

     RecRef.CurrentKeyIndex(ReplicationKeyIndex);
     FRef.SetFilter('>%1', 100);
     RecRef.FindLast();

     Message('%1', RecRef.Field(2).Value);
end;

//global variable
var
        MissingKeyReplicationCounterErr: Label 'Secondary Key for table ''%1'' on field ''%2'' is missing. This is a programming error.';

Now the RecordRef uses the secondary key on field “Replication Counter” to sort the records. So the result of the “FindLast” is the record with PK = 4 and Replication Counter = 500.

Other issues to consider

Be aware that I noticed an issue, in Business Central 17, when there are Keys marked as Obsolete: when we have Obsolete Keys in a table extension, KeyRef.Keyindex(i) is ignoring the Obsolete Keys, but RecRef.CurrentkeyIndex(i) seems is not.

So when using the RecRef.CurrentkeyIndex(i) system is setting one of the Obsoleted keys instead of the one we want.

I already created an issue on Microsoft github about this where I explained the problem in detail:
https://github.com/microsoft/AL/issues/6734

For now the solution is to not use property “Obsolete” for Keys, but instead use property “Enabled = false”. Hopefully Microsoft will fix this is newer releases.

If you are interested what this field “Replication Counter” is used for in real life you can also analyze this ticket where we submitted to Microsoft an issue with the SQL Timestamp field:
https://github.com/microsoft/ALAppExtensions/issues/13474

Alternative solutions:

Alternatively, function SetView can be used, but this could have issues depending on the language of the system. So I think above approach is better.