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:
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.
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:
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.
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
Thanks, glad you liked it!
This is a very useful and awesome information. And making it easy to understand. Thanks for sharing.
Thanks for your kind words