Small and medium business | Business Central, N...

Can Notes from Sales Orders be referenced using SQL?

(1) Share

Report

Posted on by John.B@AMETEK

I have an SSRS report for NAV 2016; I'd like to add any Notes content to records that have them. Is there a way to find the record ID of the sales orders & link to the note (Record Links) using only SQL, without adding an assembly or a table column to the existing DB?

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

YUN ZHU 97,376 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi, unfortunately, this cannot be done as far as I know. They are related by Record ID, but this value cannot be read directly from SQL.

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods/devenv-recordid-method-record

Hopefully other experts can give you better advice.

Thanks

ZHU

Was this reply helpful? Yes No
John.B@AMETEK 7 on at

Like (0)

Report
Copy link

Link copied!

Hi Zhu. Thank you for taking the time answer - I appreciate it. I think I can perform a workaround: the Record Link "Description" field can be filtered on "Sales Order - " - I can then pull out the sales orders from that. The RecordID field precedes note-specific entries with '24000000' (if I convert the RecordID to Varchar first). I can convert & decompress the Note field ~mostly~ cleanly using this:

CONVERT(

varchar(max)

, DECOMPRESS(

CONVERT(varbinary(max), 0x1F8B0800000000000400)

+

CONVERT(varbinary(max), SUBSTRING([Note],5,8000))

)

)

That gives me what I need for this user; if I need to pull more information from the Sales Order, I can use the Sales Order # pulled from the Description field.

Was this reply helpful? Yes No