Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / NAV RecordID in Record...
Microsoft Dynamics NAV (Archived)

NAV RecordID in Record Links Table

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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

  • jipsen Profile Picture
    jipsen 92 on at
    RE: NAV RecordID in Record Links Table

    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.

  • jipsen Profile Picture
    jipsen 92 on at
    RE: NAV RecordID in Record Links Table

    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

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 43,745 on at
    RE: NAV RecordID in Record Links Table

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

    Use the third parameter as 2.

  • Suggested answer
    keoma Profile Picture
    keoma 32,675 on at
    RE: NAV RecordID in Record Links Table

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans