Microsoft Dynamics NAV: KeyRef and Working with Keys in RecordRef Variables
There are several variable types in Microsoft Dynamics NAV where you look at them and wonder, “What is this? How did it get here? When do I use it?” And you may never find the answers to that on your own, unless you find something or someone who explains it.
One of these variable types is KeyRef. What is a KeyRef? Well, it’s used with a RecordRef variable to deal with secondary keys—that is, keys other than the primary key (although you can use KeyRef to work with the primary key, as well).
What’s a RecordRef, you ask? It’s a special NAV variable type you use to refer to a table when you know the table’s object number, but not its name. You use a RecordRef when you need to do operations on similar tables (like Sales Header, Sales Invoice Header, and Sales Shipment Header), but you don’t want to write the same code three times, or when you need to do the same operations on very different tables, but you don’t know which tables they are or which fields you’ll be using. You can look at the Change Log Management codeunit to see RecordRef and FieldRef in action, or you can consult this blog entry from ArcherPoint’s most handsome and intelligent developer to see an explanation.
The thing about RecordRef is that it defaults to using the primary key of the table you’re working with. If you’re wanting to sort or filter on the primary key, then that works out pretty well. But if you’re working with a table like Item Ledger Entry or General Ledger Entry, where the primary key isn’t always useful for sorting the table, and you’re processing the table via RecordRef, then you need to sort or filter on a different key. You do this for a RecordRef by using the CURRENTKEYINDEX function to specify the key you want. You can use the RecordRef.CURRENTKEYINDEX([number]) to tell the RecordRef which key to use. 1 is always the primary key, and 2 is the first secondary key, and 3 is the second secondary key, and so on.
In addition, you can use CURRENTKEYINDEX along with KeyRef variables to find which key you’re using for a table. You can do [integer] := RecordRef.CURRENTKEYINDEX to get which key you’re using, and you can load a key into a KeyRef variable for examination by using a statement like KeyRef := RecordRef.KEYINDEX([key you want]).
Once you’ve loaded up a KeyRef variable, they work a lot like RecordRef variables—you have to use a FieldRef variable and numbers to determine which fields you’re using in the key. FieldRef := KeyRef.FIELDINDEX(1) would load the first field of the key into a FieldRef variable, and KeyRef.FIELDINDEX(2) would load the second field of the key, and so on. After that, you can use the FieldRef.NAME and FieldRef.VALUE functions to work with the field.
I wrote a little sample codeunit to show how to use secondary keys and KeyRef variables with RecordRefs.
Variables are as follows:
| Name | DataType | Length | Notes | |
|---|---|---|---|---|
| i | Integer | |||
| j | Integer | |||
| TablesToExamine | Integer |
|
||
| TheKeyIndex | Integer | |||
| DocNoFieldRef | FieldRef | |||
| TheFieldRef | FieldRef | |||
| TheKeyRef | KeyRef | |||
| TheRecRef | RecordRef | |||
| KeyFieldNames | Text | 1024 | ||
| KeyFieldValues | Text | 1024 |
And the code goes like this:
TablesToExamine[1] := DATABASE::"Sales Header";
TablesToExamine[2] := DATABASE::"Sales Shipment Header";
TablesToExamine[3] := DATABASE::"Sales Invoice Header";
FOR i := 1 TO ARRAYLEN(TablesToExamine) DO BEGIN
CLEAR(TheRecRef);
TheRecRef.OPEN(TablesToExamine[i]);
CLEAR(TheKeyRef);
TheKeyIndex := i + 1;
TheRecRef.CURRENTKEYINDEX(TheKeyIndex);
TheKeyRef := TheRecRef.KEYINDEX(TheKeyIndex);
TheRecRef.FINDLAST;
KeyFieldNames := '';
KeyFieldValues := '';
FOR j := 1 TO TheKeyRef.FIELDCOUNT DO BEGIN
CLEAR(TheFieldRef);
TheFieldRef := TheKeyRef.FIELDINDEX(j);
KeyFieldNames := KeyFieldNames + TheFieldRef.NAME + ' ';
KeyFieldValues := KeyFieldValues + FORMAT(TheFieldRef.VALUE) + ' ';
END;
DocNoFieldRef := TheRecRef.FIELD(3);
MESSAGE('%1 Table Report: Key %2 is %3Values in last record found are "%4"Document No. is %5.',
TheRecRef.NAME,TheKeyIndex,KeyFieldNames,KeyFieldValues,FORMAT(DocNoFieldRef.VALUE));
CASE TablesToExamine[i] OF
DATABASE::"Sales Header" : BEGIN
// key 2 for Sales Header is No.,Document Type
// last record is 6004, Order
END;
DATABASE::"Sales Shipment Header" : BEGIN
// key 3 for Sales Shipment Header is Bill-to Customer No.
// last record is 50000 for 102031
END;
DATABASE::"Sales Invoice Header" : BEGIN
// key 4 for Sales Invoice Header is Sell-to Customer No.,External Document No.
// last record is 50000, blank for 103020
END;
END;
END;
I’ve also attached the text version of the codeunit to download.
Hopefully, you’ve learned something useful from my exposition on working with alternate keys and RecordRef variables.
Bonus video game update: I’ll always take the opportunity to talk about video games that need more attention and press, and I think one of the games from 2015 that deserved more attention was Persona 4: Dancing All Night. I never would have thought that you could take Persona and make it into a rhythm game successfully, but I was totally wrong. I’ve enjoyed every minute I’ve spent with it, and the story mode has been like getting to hang out with old friends again.
If you have any further questions about this or other development issues, contact one of our development experts at ArcherPoint. If you enjoyed this blog, you might like to read more of Tom Hunt’s blogs, or check out our collection of Development Blogs.