Microsoft Dynamics NAV (Archived)

NAV RecordID in Record Links Table

(0) Share

Report

Posted on by Community Member

Hello

I am writing to the NAV Record Link table via a SQL Query and I can get this to work on NAV2009 but not in NAV2015.

The problem I have is with the RecordID field. The encoding seems to have changed between the two NAV versions.

I am entering a link for the Purchase Header Table for the Document Type of Order.

The Query I am using below worked for 2009, but not for 2015.

Select @RECORDID = 0x26000000008B01000000027BFF+CONVERT(VARBINARY(448),@CHVPONUMBER)

Up until the characters BFF that is not a problem as this is the table number and the keys, this part of the binary value can be hardcoded in the query as it will not change.

The problem is the Convert of the PO number to Varbinary.

In NAV2009, the PO Number would translate to this using the Query Below, and this works

0x26000000008B01000000027BFF505246303030303033

However for NAV2015, if I manually create the Record Link in NAV, the same PO Number is converted to

0x26000000008B01000000027BFF50005200460030003000300030003000330000000000

How can I convert this in SQL please? There are additional bytes in between and at the end and I don't know what they mean!

Thank you

Ruth

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

keoma 32,729 on at

Like (0)

Report

it seems the used base datatype in the part right of BFF is changed in nav2015. means they use 2 bytes instead of one byte, the original byte is followed by a zero double-byte: 50 goes to 5000, 52 goes to 5200, 46 goes to 4600. split the substring right of BFF into substrings containing 2 digits (50,52,...). then add a double 0, join the new substrings to a new right part. at the end add 4 double-zero digits. done.

Was this reply helpful? Yes No
Suggested answer

Suresh Kulla 50,245 Super User 2025 Season 2 on at

Like (0)

Report

Try this CONVERT(VARBINARY(448),@CHVPONUMBER,2).

Use the third parameter as 2.

Was this reply helpful? Yes No
jipsen 12 on at

Like (0)

Report

First Thanks, I had a similar problem creating Record links, and this post gave me some ideas.

I believe your ,@CHVPONUMBER = 'PRF000003'

What will work is resulting in exactly the above record link you got when entered manually is :

Select 0x26000000008B01000000027BFF + convert(varbinary(16), N'PRF000003') +0x00000000

Was this reply helpful? Yes No
jipsen 12 on at

Like (0)

Report

As I mentioned:

Select 0x26000000008B01000000027BFF + convert(varbinary(16), N'PRF000003') +0x00000000

results in

0x26000000008B01000000027BFF50005200460030003000300030003000330000000000

the the Record Link in NAV Ruth got when she created the record manually.

the query needs to be changed to:

Select @RECORDID = 0x26000000008B01000000027BFF+convert(varbinary(16), @CHVPONUMBER) +0x00000000

Note that you need N'(unicode string constant) only when applied to literals.

If the variable @CHVPONUMBER is nvarchar it is already Unicode data.

Was this reply helpful? Yes No