RecordRef is a powerful tool that you can learn to level up your coding skills in Microsoft Dynamics 365 Business Central. In this post, you will learn what is it and how to use it along with FieldRef and KeyRef. At the end, you will find an example of how to use them.

  • What Is RecordRef
    • RecRef.Field
    • RecRef.FieldIndex
    • RecRef.KeyIndex
  • What Is FieldRef
  • What Is KeyRef
  • Use Case Example

What Is RecordRef


RecordRef is a data type that can refer to any table in the database. It´s similar to a record but it can change dynamically at runtime. It provides flexibility to work with any table.

The way to declare and initiate a RecordRef is the following. In this image, we can open the Customer table by using the ‘Open‘ method.


We will have a look at some methods that we will use in the use case example at the end.

RecRef.Field()


With the RecRef.Field method we can refer to any field of the table. The first field is the primary key.


The value of the primary key of the first customer is ‘10000’.


Or we can see the caption of the field by using RecRef.Field.Caption:


RecRef.FieldIndex


The RecRef.FieldIndex is similar to RecRef.Field, the difference is that this one counts the index of the field. Not the field itself.


The FieldIndex 26 is ‘Fin. Charge Terms Code’ while Field 26 is ‘Statistics Group’.


As you can see in the Customer table, there is no field 0 or field 25. So the field index 26 is ‘Fin. Charge Terms Code’. And field 26 is ‘Statistics Group’.


So this code will get the same field:


RecRef.KeyIndex


Something similar happens with the keys of a table. This method gets the field of the key we are looking for. In this case, the field of the second key is the third field of the Customer table.


When we look for KeyIndex 2, the result is Field 3.


You can see the comparison in the following image. On the left, we can see that ‘Search Name’ is field 3. On the right, we see the keys of the Customer table, we can see ‘Search Name’ is the second key.


You can find the full information about RecRef in the Microsoft documentation:

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/recordref/recordref-data-type

What Is FieldRef


It provides a way to interact with the fields of a table. RecordRef and FieldRef work together to have full flexibility in data management.

So a FieldRef is basically a data type that can refer to any field in any given table in the database.

The method RecRef.Field() returns this data type:


We can declare FieldRef similar to RecordRef. The following code will show two messages with the same result.


Get more information about FieldRef at the following link.

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/fieldref/fieldref-data-type


What Is KeyRef


Similar to FieldRef, KeyRef can refer to any key of a given table. Now we have the possibility to search for specific keys and their values.

We also declare KeyRef similar to RecordRef. By using RecRef.KeyIndex as we did in the first section, we can assign it to a KeyRef variable.


The code returns the fields that compose key 18 of the Customer table.


We can also use the FieldIndex method for a key.


It returns ‘US’, let´s see why.


We can use KeyRef.FielIndex.Caption to learn which field it´s coming from:


The same result can be achieved by dotting the RecRef variable like the following. This way, we don´t need a KeyRef variable.


In the following image, you can see the keys of the Customer table. We are looking for the second field of the key index 18. Note that it is Key 20. The second field of Key 20 is ‘Country/Region Code’ which has the value of ‘US’ for the first customer in the database.


Get more info about KeyRef in the Microsoft documentation:

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/keyref/keyref-data-type

Use Case Example


Now that we can open tables dynamically and search for values, keys, etc, we can take advantage of some useful tables such as the ‘Field’ table.

It contains all the fields in the database as if it were one table that includes the fields of all the rest.

We can see the table number and field numbers as well as names and types. But the most important for this tool is ‘RelationTableNo’, which indicates the table relation property.

This example will check that a given payment method can´t be deleted if it has been used already in another table. In other words, we wish to check if a given payment method exists in a related table before deleting it.


For a payment method (Table 289), we can see that it´s been used in the following tables:


These are the tables we wish to search for the payment method.

First, let´s filter the Field table and loop through it. The RecRef parameter will have the Payment Method table in this example.


Then, we can add a second RecRef variable to open every table and search in them. We use a SetRange and filter the value to search. If we found it, we can show an error:


Finally, for the table we wish to check, we can use the ‘OnBeforeDelete’ trigger:


Video test:


You can find a small example of RecordRef in the following article:


Source code:

codeunit 50013 "RecordRefExample"
{
    procedure PreventDataDeletionIfUsed2(RecRef: RecordRef)
    var
        Field: Record Field;
        RecRef2: RecordRef;
        DataUsedLbl: Label 'The value %1 exists in the field %2 of %3 table. Deletion is not allowed.';
    begin
        //Gets the record Field to filter the Type of Field table with the info. from the RecordRef parameter
        Field.Get(RecRef.Number, RecRef.KeyIndex(1).FieldIndex(1).Number);
        Field.SetRange(Type, Field.Type);
        //Filters RelationTable No with the RecordRef table Number
        Field.SetRange(RelationTableNo, RecRef.Number);
        //Only filter no Obsoleted tables
        Field.SetRange(ObsoleteState, Field.ObsoleteState::No);
        //Loop for searching in every table
        if Field.FindSet(false) then begin
            repeat
                //Opens the table provided by Field table
                RecRef2.Open(Field.TableNo);
                //Filters the field with the value of the primary key of RecordRef
                RecRef2.Field(Field."No.").SetRange(RecRef.KeyIndex(1).FieldIndex(1).Value);
                //If values are found we can´t delete it
                if not RecRef2.IsEmpty then begin
                    Error(DataUsedLbl, RecRef.KeyIndex(1).FieldIndex(1).Value, Field."Field Caption", RecRef2.Caption);
                end;
                //Closes the table
                RecRef2.Close();
            until Field.Next() = 0;
        end;
    end;
}

tableextension 50000 "Pyment Method" extends "Payment Method"
{
    trigger OnBeforeDelete()
    var
        RecordRefExample: Codeunit RecordRefExample;
        RecRef: RecordRef;
    begin
        RecRef.GetTable(Rec);
        RecordRefExample.PreventDataDeletionIfUsed(RecRef);
    end;
}



That´s all. Hope you find it helpful.

How To Use RecordRef, FieldRef, And KeyRef In Business Central

Post navigation


4 thoughts on “How To Use RecordRef, FieldRef, And KeyRef In Business Central

  1. Dear Sir,

    This is an awersome post. I effectively used this in my project, thank you very much for your work (code) make available. Understood new concepts..

    Stephen

  2. This is a very useful and awesome information. And making it easy to understand. Thanks for sharing.

Leave a Reply

Your email address will not be published. Required fields are marked *