Journey into Mystery! Working with RecordRef Variables in Microsoft Dynamics NAV

The RecordRef and FieldRef variable types are used in Microsoft Dynamics NAV to handle working with a record and fields in a record when you don’t necessarily know which record or fields you’ll be handling. In this blog entry, I’ll write about how to work with them. NOTE: These instructions should apply to all versions of Dynamics NAV from version 3.7 up to and including Dynamics NAV 2013 and Dynamics NAV 2013 R2.

Writing code around Microsoft Dynamics NAV, you may have seen the RecordRef, FieldRef, and KeyRef variable types. If you’ve never gotten to use them, you probably don’t quite know what they are or how they work. These three variable types are used when you know that you need to work with a record or set of records, but you don’t know exactly which table you’ll need to access.

Generally, I’ve used RecordRef variables under two different circumstances. Either I needed to do the same operation with several very similar tables, like Sales Header and Sales Invoice Header and Sales Shipment Header, or I’ve needed to do the same operation on a whole bunch of tables having nothing in common. In either situation, the thing that drove me to RecordRef variables was that I didn’t know which table I’d be dealing with until runtime. (The biggest project I had to do using RecordRef involved synchronizing records between NAV and an external database in real-time via web service calls; I used the OnGlobalInsert, OnGlobalModify, OnGlobalDelete, and OnGlobalRename triggers in the ApplicationManagement codeunit to call some web services along with RecordRef variables to send the data over.)

RecordRef is basically a normal Record variable, only requiring that you feed it the table number so that NAV knows which table you want to work with. You do that via the RecordRef.OPEN function, which takes three parameters:

• No. (required): This is the table number that you want to work with. You can use the DATABASE::[table name] option variable to find the number of the table that you’re wanting to use, if you don’t know it ahead of time.
• Temp (optional): This is a boolean telling the system whether you want to open up a temporary table (TRUE) or a real table (FALSE). If you omit this, the system assumes you want a real table.
• CompanyName (optional): This is the NAV company name that you’re working with. If you omit this, the system assumes you’re working with the current company.

There are some other important functions to know about with RecordRef variables. RecordRef.GETTABLE is used to point a RecordRef at the same table instance as a Record variable, and RecordRef.SETTABLE is used to point a Record variable at the same table instance as a RecordRef variable. You can use GETTABLE and SETTABLE to transfer data between a RecordRef and a normal Record variable.

You can use the FINDFIRST, FINDLAST, FINDSET, and NEXT functions of a RecordRef variable to loop through a recordset, just like you would with a Record variable. And you can set filters on a RecordRef by using FieldRef variables; we’ll cover that below. You can also use the INSERT, MODIFY, DELETE, and RENAME functions to modify a record using RecordRef; note that they can be run with TRUE or FALSE parameters to trigger the OnInsert/OnModify/OnDelete/OnRename trigger in the appropriate table.

To access the fields of a record in a RecordRef variable, you have to use a FieldRef. A FieldRef is basically like a field in a Record variable, but with some special changes made to handle the fact that it could be any time.

There are two ways to access a record field with FieldRef. You can either load the field into a FieldRef using the RecordRef.FIELDINDEX function, or you can use the RecordRef.FIELD function. FIELDINDEX requires that you feed it the order in which the field appears in the table—the first field is 1, the second field is 2, and so on, regardless of the Field No. property in the underlying table. FIELD requires that you feed it the Field No. property from the underlying table. You can find the Field No. for a field by accessing the FIELDNO property of any record variable, feeding it a field name.

If you want to look at all the fields in a record variable, you can use an integer counter and the RecordRef.FIELDCOUNT property to load them with RecordRef.FIELDINDEX. If you want to look at specific fields, then you use RecordRef.FIELD.

FieldRef variables can be read from or written to with the FieldRef.VALUE property. The data type for FieldRef.VALUE is a variant, so you can write any valid NAV data type to it.

You can use FieldRef.SETRANGE and FieldRef.SETFILTER to set filters on a RecordRef. You can also use RecordRef.RESET to clear all the filters on a RecordRef variable.

For regular Record variables in NAV, you can use the GET function along with the primary key values to grab a specific record from the database. However, you have to use a special type of variable called a RecordID to grab a specific record from the database for a RecordRef. A RecordID is a specially-encoded value holding the primary key for a RecordRef variable. You can use the RecordRef.RECORDID function to find it for a RecordRef variable. If you wanted to get the RecordID from a Record variable to use with a RecordRef, you need to convert the Record variable into a RecordRef first by using RecordRef.GETTABLE and feeding that the Record variable.

Here’s a sample codeunit I wrote with RecordRef and FieldRef that would compare two different records and give you a message if there were any differences. You might want to use something like this if you were checking to see if a record had changed and updating some things accordingly.

NOTE: You can download this codeunit here.

OnRun()

Cust.GET('10000');

Cust2.GET('10000');

Cust2.Name := 'Changed';

CLEAR(RecRefCust1);

RecRefCust1.GETTABLE(Cust);

CLEAR(RecRefCust2);

RecRefCust2.GETTABLE(Cust2);

RecordCompare(RecRefCust1,RecRefCust2);

RecordCompare(RecRef1 : RecordRef;RecRef2 : RecordRef)

ChangesMade := FALSE;

lCounter := 1;

REPEAT

 CLEAR(FldRef1);

 FldRef1 := RecRef1.FIELDINDEX(lCounter);

 CLEAR(FldRef2);

 FldRef2 := RecRef2.FIELDINDEX(lCounter);

 ChangesMade := (FldRef1.VALUE <> FldRef2.VALUE);

 lCounter += 1;

UNTIL ChangesMade OR (lCounter > RecRef1.FIELDCOUNT);

IF ChangesMade THEN BEGIN

 MESSAGE('The records do not match.');

END ELSE BEGIN

 MESSAGE('The records are the same.');

END;

Subscribe to Okello Dan Kairo's Blog, or, for more information on topics related to Microsoft Dynamics NAV development, read the Okello Dan Kairo's Developer Blog written specifically for Microsoft Dynamics NAV developers.