Record Link table auditing

Business Central/NAV Technical Forum
1

Hi there everybody.

We’re using the ‘Link’ function in Nav 5.0, where people are associating documents on the network to records in NAV.

I’m writing a script in SQL to audit this table to ensure that a) files are on the network and not local, and 2) The physical files exist. That’s proven the easy part.

However I am unsure how to determine what record in Nav any given row in the SQL table refers to I know there is a ‘Record ID’ field however I am unable to translate that to anything meaningful, like Order No., etc.

Can anybody help me with this translation of Record ID

2

Table name : PK1, PK2, PK3

3

I should have been a little more clear. When I look at the table through Object Explorer in NAV, I see this as the Record ID:

Sales Header: Quote,CL000504

However, that same record in SQL looks like this (varbinary):

0x24000000008B000000000089FF434C303030353034000000

My question, how can I make SQL return the result NAV does through the front end?

4

It’s seems that is in ascci stored in hexadecimal number.

Table 36 = Sales Header = 0x24000000008B000000000089FF434C303030353034000000

Key CL000504 = 0x24000000008B000000000089FF434C303030353034000000

I’m just guessing

5

Check ascii table in http://pt.wikipedia.org/wiki/ASCII

6

That’s exactly what I was after. Thanks for your help.

I’m going to try to write a SQL function to parse the RecordID field. I’ll post it back here should I get it working.

7

So far so good. I’ve got a function sort of working.

In between the 24 for the table ID, and the 434C30303035303400000

for the key, do you know how to resolve remaining information?

000000008B000000000089FF

I believe it is related to document type, in this example Quote I believe is what it should translate to, somehow.

cheers

8

Hi Mark,

did you ever finish your SQL script to generate a Record ID?

I’m currently facing the same situation where I should create new records from a SQL script including a Record ID.

MS has sort of documented the Record ID in a blog post so we finally know a little more about it.