[QODBC Desktop] Retrieving All Customer Notes with QODBC - Using the CustomerAddtionalNote Table

Problem Description:

The Customer table in QODBC exposes only a single Note column per customer record - typically the first or pinned note. Customers often need to retrieve all notes stored on a QuickBooks customer record (which can number into the dozens), but querying Customer.Note returns at most one line.

Affected products: QODBC Desktop, QuickBooks Desktop
Expected outcome: By querying the CustomerAddtionalNote table instead of Customer, all notes for a customer are returned as separate rows via the AdditionalNotesRetNote field.

Symptoms:

• Querying SELECT Note FROM Customer WHERE … returns only one note, even when the customer has many notes in QuickBooks.
• The note returned by Customer.Note appears to be the first or pinned note - not all notes are accessible through this column.
• A customer record shows dozens of notes in the QuickBooks UI, but an ODBC query returns zero or one row from CustomerAddtionalNote.

Cause:

QuickBooks Desktop stores a single short note on the main customer record (exposed by QODBC as Customer.Note). Additional notes are stored separately in the QuickBooks SDK's internal note collection. QODBC exposes this secondary storage through the virtual table CustomerAddtionalNote (note: QODBC uses the original spelling from the SDK, not "Additional").

The key detail that causes most queries to return zero rows: CustomerAddtionalNote does not use the customer's full name. Instead, it requires FirstName or LastName as the filter - because QuickBooks SDK stores names internally in the format "LASTNAME, FIRSTNAME", and queries using Name = 'LASTNAME, FIRSTNAME' or other variations may not match.

Environment:

Solution:

1. Query the CustomerAddtionalNote table, not Customer.Note, to retrieve all notes.

-- Retrieve all additional notes for a customer by last name
SELECT AdditionalNotesRetNote FROM CustomerAddtionalNote WHERE LastName = 'DELALUZ'

-- Retrieve all additional notes for a customer by first name
SELECT AdditionalNotesRetNote FROM CustomerAddtionalNote WHERE FirstName = 'MICHAEL'

-- Retrieve both the note and the raw additional note
SELECT Notes, AdditionalNotesRetNote FROM CustomerAddtionalNote WHERE LastName = 'DELALUZ'

2. If you only need the count of notes, use:

SELECT COUNT(*) FROM CustomerAddtionalNote WHERE LastName = 'DELALUZ'

3. To discover all available columns, run:

sp_columns CustomerAddtionalNote

Using the UNOPTIMIZED Hint

In some cases, adding the UNOPTIMIZED hint ensures all rows are returned without being filtered by the optimizer:

SELECT AdditionalNotesRetNote FROM CustomerAddtionalNote UNOPTIMIZED WHERE LastName = 'DELALUZ'

Discovering Available Columns

To list all available columns on CustomerAddtionalNote, use the QODBC Test Tool or any ODBC client:

SP_Columns CustomerAddtionalNote

You can also consult the online table documentation at:
https://doc.qodbc.com/qodbc/usa/TableDetails.php?tableName=CustomerAddtionalNote

SQL Examples:

Example - Retrieving and displaying all customer notes in a report:

-- Get ListID first from Customer table
SELECT ListID, FullName FROM Customer WHERE LastName = 'DELALUZ'

-- Then fetch all notes using the ListID
SELECT Notes, AdditionalNotesRetNote FROM CustomerAddtionalNote WHERE ListID = '164900-1234567890'

Example - Filtering notes by date range (via the Notes timestamp):

-- Notes field typically contains a timestamp prefix like "7/24/2023 9:08:50 AM: note text"
SELECT AdditionalNotesRetNote FROM CustomerAddtionalNote
WHERE LastName = 'DELALUZ'
  AND Notes LIKE '%2024%'

Example Scenarios:

Scenario 1 - Importing all customer notes into an external system:

A company needs to migrate customer interaction history from QuickBooks into a CRM or ticketing system. Instead of only getting the single Customer.Note field, they query CustomerAddtionalNote by LastName to retrieve every note row as a separate record, preserving timestamps embedded in the Notes column.

Scenario 2 - Debugging zero-row results:

A developer queries CustomerAddtionalNote WHERE Name = 'MICHAEL DELALUZ' and gets zero rows. The QuickBooks SDK stores the name internally as "DELALUZ, MICHAEL", so the Name column does not match. The fix is to use WHERE LastName = 'DELALUZ' or WHERE FirstName = 'MICHAEL' instead.

Additional Notes:

• CustomerAddtionalNote is a virtual/projection table. It does not exist in QuickBooks or any SQL database. QODBC creates it dynamically to expose the SDK's internal note collection for customer records.
• The Name column may not work as expected. Because QuickBooks stores names internally as "LASTNAME, FIRSTNAME" (e.g., "DELALUZ, MICHAEL"), queries using Name = 'MICHAEL DELALUZ' will return zero rows. Use FirstName or LastName instead.
• The Notes column often contains a timestamp prefix (e.g., "7/24/2023 9:08:50 AM: note text"). Parse the timestamp from the left portion of this field if you need to sort or filter by date.
• Pin/star notes in QuickBooks affect which note appears on Customer.Note. Pinned or highlighted notes in the QuickBooks UI change which single note is returned by Customer.Note - but this has no effect on CustomerAddtionalNote, which returns all rows regardless of pin status.
• The UNOPTIMIZED hint can help in some cases. Adding UNOPTIMIZED to the query bypasses QODBC's optimizer filtering, ensuring all matching rows are returned.

Related Articles:

QODBC Table Reference - Customer Table Columns and Required Fields How to Use sp_columns to Discover QODBC Table Schema

Keywords: