How do Record Links encode their data in SQL server?

How do Record Links encode their data in SQL server?

Rate This
  • Comments 5

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:

Anette Hill

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

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.

 

-- jtorres

Leave a Comment
  • Please add 3 and 5 and type the answer here:
  • Post
  • Thanks for great article

    1. Can I have more sample for the case when String is not started by 0xFF please?

    2. when you said:

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

    The correct string is ‘1924-W’

    3. Where can I find numbers for diffrent datatypes like Integer (135) and CODE (137) ?

  • 1. 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.

    2. Thanks for this! I have corrected the text, as indeed it was wrong.

    3. I will soon do a blog post with a table for the most used data types.

  • Really useful information - thanks.

    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.

  • Is there any information about TableFilter?

    Thank you

  • Great article

    Can you please talk about the how this changed in Nav 2013 and give some guidance in how to do something similar in Nav 2013

    Thanks

Page 1 of 1 (5 items)