Dynamics 365 Business Central: How to get fields in the keys of the table (Using KeyRef Data Type)

Hi, Readers.
Today I would like to talk about an interesting method in Business Central customization, how to get fields in the keys of the table.

As you might know, in AL, a key definition is a sequence of one or more field IDs from a table. You can define keys in table objects and table extension objects, depending on the type of key. There are two types of keys: primary and secondary.

For example, in table 18 Customer.

So if we want to get the third field of Key7 (City), what should we do?

This time we are going to use KeyRef Data Type.

KeyRef Data Type: Identifies a key in a table and the fields in this key.

The following methods are available on instances of the KeyRef data type.

Method name	Description
Active()	Indicates whether the key is enabled.
FieldCount()	Gets the number of fields that have been defined in a key. Returns an error if no key is selected.
FieldIndex(Integer)	Gets the FieldRef of the field that has this index in the key referred to by the KeyRef variable. Returns an error if no key is selected.
Record()	Returns a RecordRef for the current record referred to by the key.

And in RecordRef Data Type, we can also use the following method.

KeyIndex(Inte ger)

Gets the KeyRef of the key that has the index specified in the table that is currently selected. The key can be composed of fields of any supported data type. Data types that are not supported include BLOBs, FlowFilters, variables, and functions. If the sorting key is set to a field that is not part of a key, then the KEYINDEX is -1.

Note: The first field in the index must have index 1, the second index 2, and so on. The last field must have index = FieldCount.

For the above requirements, we only need a few lines of code to achieve.

On the page:

Source Code:

pageextension 50113 MyExtension extends "Customer List"
{
    trigger OnOpenPage()
    var
        RecRef: RecordRef;
        FldRef: FieldRef;
        KRef: KeyRef;
    begin
        RecRef.Open(18);
        KRef := RecRef.KeyIndex(7);
        FldRef := KRef.FieldIndex(3);
        Message('The Field name is: %1', FldRef.Caption);
    end;
}

We can also filter this field to get the current record.
For example,

Since the record of the formatted RecordRef data type are too long, I only make it display within 50 for test.

On the page:

Source Code:

pageextension 50113 MyExtension extends "Customer List"
{
    trigger OnOpenPage()
    var
        RecRef: RecordRef;
        FldRef: FieldRef;
        KRef: KeyRef;
    begin
        RecRef.Open(18);
        KRef := RecRef.KeyIndex(7);
        FldRef := KRef.FieldIndex(5);
        //Message('The Field name is: %1', FldRef.Caption);
        FldRef.SetFilter('Munchen');
        if RecRef.FindFirst() then
            Message(CopyStr(Format(RecRef), 1, 50));
    end;
}

This is just a simple test, hope it gives you some new inspiration. In fact, this method will be used more on the primary key.

Believe your solution will be better. Give it a try!!!😁

PS:
If the specified index (KeyIndex & FieldIndex) is out of the range or if no table is selected, the method returns an error.

END

Hope this will help.

Thanks for reading.

ZHU