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.