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.
