I’m still scratching my head over this one. How can you link system notes to transaction line items? I found it’s possible, but not intuitive.

Since discovering SuiteQL, I’ve tried to use it instead of saved searches. The advantage is it allows multi-level joins, where saved searches only allow single level (one and only one level) of joins.

Querying system notes in SuiteQL looks something like this.

select
*
from
(
    select
        t.id as tran_id
        , t.tranid as tran_num
        , n.date
        , case n.type when 2 then 'Set' 
            when 4 then 'Change' 
            else to_char(n.type) 
          end as change_type
        , case n.context 
            when 'SLT' then 'Script (Restlet)' 
            when 'UIF' then 'User Interface' 
            when 'WST' then 'Web Store' 
            else n.context 
          end as change_context 
        , n.field
        , n.oldvalue
        , n.newvalue
    from systemnote n
        inner join transaction t 
        on t.id = n.recordid 
        and t.type = 'SalesOrd' 
        and n.date > '10/23/2022'
        and BUILTIN.DF(t.status) in
        (
            'Sales Order : Pending Fulfillment',
            'Sales Order : Pending Billing/Partially Fulfilled',
            'Sales Order : Partially Fulfilled'
        )
    where 1=1
    and n.oldvalue != n.newvalue
    and n.type in (2,4) -- Set or Changed
) as dtbl
order by tran_num, n.date desc

It works great, but has the limitation of not being able to link the system note directly to the transaction line. I searched to the end of the internet, and I don’t see anyone who’s solved this problem, not even Tim Dietrich, the PL/SQL Guru. However, the relationship exists in a saved search. Marty Zigman points this out in one of his blog articles. Thank you, both of you!

Of course you can always use SuiteQL to query your saved search. However, it sure would be nice if NetSuite shared the secret foreign key relationship between transaction lines and system notes in their Schema Browser. I noticed some views described in the Schema Browser “Connect Browser” tab, but I could not translate that into anything that worked in PL/SQL.

If you know more about this than I do, please share it with the rest of us. Respond to this post and I’ll include it and credit you.