Jet Reports - Using NL(BinaryText) to Retrieve Notes
Issue
Notes attached to records in Business Central are not stored directly on the table you are viewing. They are stored as BLOB fields in the Record Link table. Attempting to retrieve notes using a standard NL function against the source table returns no data.
Environment
| Component | Detail |
|---|---|
| Product | Jet Reports |
| Integration | Microsoft Dynamics 365 Business Central |
| Feature | NL Functions - BinaryText |
Cause
Business Central stores record notes in the Record Link table (table number 200000068) as BLOB fields, not on the source tables (e.g. Sales Invoice Header, Customer, Vendor). A standard NL("Rows") or NF() function against the source table cannot access these notes directly.
Resolution
Use the NL("BinaryText") function to retrieve notes from the Record Link table. Follow the steps below to build and test your formula.
Step 1 - Explore the Record Link table
Before building your NL("BinaryText") formula, run an NL("Rows") function against the Record Link table to
understand how data is stored and which filters you need.
Step 2 - Build the NL(BinaryText) formula
Use the following syntax to retrieve the note for a specific record:
NL(BinaryText) Syntax
=NL("BinaryText","Record Link","Note","Record ID",$C6,"Link ID",$D6)
| Parameter | Value | Description |
|---|---|---|
| Function type | "Binary Value" | Instructs Jet Reports to read a binary (BLOB) field as text |
| Table | "Record Link" | The table where Business Central stores notes (table number 200000068) |
| Field | "Note" | The BLOB field containing the note text |
| Filter 1 Field | "Record ID" | Identifies which record the note belongs to |
| Filter 1 Value | $C6 | Cell reference containing the Record ID value |
| Filter 2 Field | "Link ID" | Uniquely identifies the link record |
| Filter 2 Value | $D6 | Cell reference containing the Link ID value |
Matching Record IDs with Concatenation
The Record ID field in the Record Link table stores a combined value that includes both the table name and the document identifier. You must concatenate these values to match correctly. For Sales Invoice Header records, use the following formula to build the Record ID value:
Record ID Concatenation Example
="Sales Invoice Header: "&$J5&""&$E5
In this example, $J5 contains the document type and $E5 contains the invoice number. Adjust the cell references and table name to match your report layout
Note: The exact format of the concatenated Record ID varies by table. Use the NL("Rows") exploration step first to confirm the exact format stored in your Business Central environment before building the final formula.
Related Articles