How do Record Links encode their data in SQL server?

Recently I was asked about the encoding that it is done for record links in SQL.

If you are not familiar with Record Links, which have been available since NAV version 5.0, just open any Card or List and then click Edit -> Links (Ctrl + L).

“Record Links enable users to add links to documents to any record in Microsoft Dynamics NAV, such as a sales order or purchase order. The document or order can be stored in Microsoft SharePoint or on a file server and the user can access the document from Microsoft SharePoint or give access to others.”

In SQL, these Record Links are saved under the “Record Link” table, and contains (among others) the Record ID, URLs, Description, etc.

Basically, the Record ID should be able to uniquely identify to which record this link belongs, so…. Not surprisingly, this Record ID will contain the table number and corresponding key values (+ 2 null bytes).

In principle, what I have just written should be enough to understand the format, but I will further explain with the help of some examples.

Let’s start with Annette Hill, one of the employees that work at CRONUS International:

Annette’s Record ID on the Record Link table (in SQL) will look as “0x501400000089FF4148000000”. As I wrote before, this should correspond to the table number, the keys and 2 null bytes… That is:

The first 4 bytes correspond to the table number, in this case 0x50 14 00 00, which should correspond to table 5200.

Now, you must be wondering, how comes 5200 can be converted to such a strange binary representation. Well, we will have to remember two things: 1) The number needs to be in hexadecimal format, and 2) It needs to be encoded in little endian as we are working with Intel processors (just use your search engine, if you don’t know/remember).

So, we need 4 bytes for (table) 5200, that in hex is: 00 00 14 50, if we convert it to little endian, we will get: 50 14 00 00.

The next 2 bytes correspond to the data type of the key. The type is CODE, which is type 137, which is only converted to hex: 00 89. At this point you must be thinking that I am pulling your leg, as this is supposed to be further converted using little endian, but it turns out that the keys data types are encoded in their binary representation, in order to reuse code when searching for them.

The following 4 bytes correspond to the key itself. Interestingly enough, code data types will start with 1 byte that can tell you the length of the code (if numeric) or FF which would correspond to a character based code type. In this case, we have 0xFF, meaning that we have characters and those are 0x41 and 0x48 + null termination 0x00. Of course 0x41 corresponds to ‘A’ and 0x48 corresponds to ‘H’ (in “extended” ASCII), which is the key, as can be seen on the ‘No.’ field.

The last 2 bytes will always be null characters.

Let’s do something a little bit more interesting, now with Bill of Materials:

This link will look as: 0x5A0000000089FF313932342D57000087102700000000

The first 4 bytes correspond to the table number, in this case 0x5A 00 00 00, which should correspond to table 90.

The next 2 bytes correspond to the data type of the first key. The type is CODE, which is type 137, which is only converted to hex: 00 89.

The following 8 bytes correspond to the key itself. In this case, we have 0xFF, meaning that we have characters and those are ‘1924-W’ (+ null).

The next 2 bytes correspond to the data type of the next key. The type is Integer, which is 135, that is converted to hex: 00 87.

The following 4 bytes correspond to the key itself. In this case, we have 0x10270000, converting this to an integer, corresponds to 10000 (using the little endian rule).

The last 2 bytes will always be null characters.

If you are wondering where these values came from, Open Table 90, and verify that the Key is in fact ‘Parent Item No.,Line No.’ and that the values that we have decoded, correspond to the data we have added the links to.

Of course, you should always remember that this format is subject to change and there is no guarantee that it will be preserved on future versions, also remember the standard following claim.

Sure… Following the Employee Card, I have added myself as President of CRONUS, and my employee No. is 1234, and added a Record Link for this record. Since the "No." is CODE, what I got is: 0x50140000 0089 043132333400 0000

The first two blocks, I have discussed before…. the 0x04 corresponds to the length of the CODE data, and the data is 0x31, 0x32, 0x33, 0x34 and 0x00 (code themselves are null terminated), which corresponds to ‘1’, ‘2’, ‘3’, ‘4’ and NULL. The last 2 bytes are the null characters.

I too would be interested in knowing the other values for data type, but I would especially like to know how the Note column is coded. Like Record ID it is a binary field, but I can't see how the value corresponds to the text for a note entered in the Role Tailored Client.

I’m curious in NAV in the case of primary keys where the key actually has multiple parts. Per above, the 5th and 6th bytes hold the data type, and 9-16 have the key value. If it’s a multi-part key (like say purchase header which is – Type, “No.”) it’s an integer + code. How is that encoded?

We have an issue in NAV 2016 where record links to posted documents (only one field in the primary key) work, but ones to the unposted header (which have a type, “no.” configuration) do not.