Transaction Link Tables

NetSuite provides specialized junction tables for querying relationships between linked transactions. These tables are essential when the createdfrom field is not exposed in SuiteQL.

---

Overview

TRANSACTION LINK TABLES
═══════════════════════════════════════════════════════════════════════════════

                    HEADER-LEVEL                         LINE-LEVEL
              ┌─────────────────────┐             ┌─────────────────────┐
              │                     │             │                     │
  Parent ───▶ │ NextTransactionLink │   Parent ──▶│NextTransactionLineLink│
              │                     │             │                     │
              └─────────────────────┘             └─────────────────────┘
                        │                                   │
                        ▼                                   ▼
              ┌─────────────────────┐             ┌─────────────────────┐
              │                     │             │                     │
  Child ────▶ │PreviousTransactionLink│  Child ──▶│PreviousTransactionLineLink│
              │                     │             │                     │
              └─────────────────────┘             └─────────────────────┘

Use Case:
- Sales Order → Invoice, Fulfillment, Credit Memo
- Purchase Order → Item Receipt, Vendor Bill
- Transfer Order → Fulfillment, Receipt

---

The Four Link Tables

Table	Level	Direction	Use Case
NextTransactionLineLink	Line	Parent → Child	Get child transactions from parent
PreviousTransactionLineLink	Line	Child → Parent	Get parent from child transaction
NextTransactionLink	Header	Parent → Child	Header-only linking
PreviousTransactionLink	Header	Child → Parent	Header-only reverse lookup

When to Use Which

• Use Line-Level tables when you need to track quantities, amounts, or line-specific relationships
• Use Header-Level tables for simpler parent-child lookups without line detail
• Both directions contain the same data - choose based on your starting point

---

NextTransactionLineLink Columns

The primary table for querying transaction relationships at the line level.

Column	Type	Description
PreviousDoc	Integer	Source/parent transaction ID
PreviousType	String	Source transaction type (e.g., SalesOrd)
PreviousLine	Integer	Source transaction line number
NextDoc	Integer	Destination/child transaction ID
NextType	String	Destination transaction type (e.g., CustInvc, ItemShip)
NextLine	Integer	Destination transaction line number
LinkType	String	Relationship type code
Quantity	Decimal	Quantity transferred between transactions
ForeignAmount	Decimal	Line item amount in transaction currency
Status	String	Link status (active, pending, completed)
LastModifiedDate	Date	When the link was established

Common LinkType Values

LinkType	Meaning	Example Flow
ShipRcpt	Ship/Receipt	SO → Item Fulfillment
OrdBill	Order to Bill	SO → Invoice
DropShip	Drop Ship	SO → PO (drop ship)
RcptBill	Receipt to Bill	Item Receipt → Vendor Bill
OrdRcpt	Order to Receipt	PO → Item Receipt

Common Transaction Type Codes

Code	Transaction Type
SalesOrd	Sales Order
CustInvc	Customer Invoice
ItemShip	Item Fulfillment
CustCred	Credit Memo
PurchOrd	Purchase Order
ItemRcpt	Item Receipt
VendBill	Vendor Bill
RtnAuth	Return Authorization (RMA)
TrnfrOrd	Transfer Order

---

Basic Queries

Get All Child Transactions from Sales Order

-- Find all transactions created from Sales Order ID 12345
SELECT DISTINCT
    NTLL.NextDoc AS child_id,
    NTLL.NextType AS child_type,
    t.tranid AS child_tranid,
    t.trandate,
    BUILTIN.DF(t.status) AS status,
    t.foreigntotal AS amount
FROM NextTransactionLineLink NTLL
INNER JOIN Transaction t ON t.id = NTLL.NextDoc
WHERE NTLL.PreviousDoc = 12345
ORDER BY t.trandate, t.id

Sample Result:

child_id	child_type	child_tranid	trandate	status	amount
12350	ItemShip	IF-001	2024-12-01	Shipped	NULL
12355	CustInvc	INV-001	2024-12-02	Open	5000.00

Get Parent Transaction from Invoice

-- Find the Sales Order that created Invoice ID 12355
SELECT DISTINCT
    PTLL.PreviousDoc AS parent_id,
    PTLL.PreviousType AS parent_type,
    t.tranid AS parent_tranid,
    t.trandate
FROM PreviousTransactionLineLink PTLL
INNER JOIN Transaction t ON t.id = PTLL.PreviousDoc
WHERE PTLL.NextDoc = 12355

Get Complete Transaction Chain

-- Full chain: SO → Fulfillment → Invoice
SELECT DISTINCT
    so.tranid AS sales_order,
    so.trandate AS order_date,
    ship.tranid AS fulfillment,
    ship.trandate AS ship_date,
    inv.tranid AS invoice,
    inv.trandate AS invoice_date
FROM Transaction so
-- SO to Fulfillment
LEFT JOIN NextTransactionLineLink ntll1
    ON ntll1.PreviousDoc = so.id
    AND ntll1.NextType = 'ItemShip'
LEFT JOIN Transaction ship ON ship.id = ntll1.NextDoc
-- SO to Invoice
LEFT JOIN NextTransactionLineLink ntll2
    ON ntll2.PreviousDoc = so.id
    AND ntll2.NextType = 'CustInvc'
LEFT JOIN Transaction inv ON inv.id = ntll2.NextDoc
WHERE so.type = 'SalesOrd'
  AND so.id = 12345

---

Line-Level Queries

Track Quantity Flow Between Transactions

-- See how quantities flow from SO lines to fulfillments
SELECT
    so.tranid AS sales_order,
    sol.linesequencenumber AS so_line,
    sol.item,
    BUILTIN.DF(sol.item) AS item_name,
    sol.quantity AS ordered_qty,
    NTLL.Quantity AS shipped_qty,
    ship.tranid AS fulfillment,
    shipl.linesequencenumber AS ship_line
FROM Transaction so
INNER JOIN TransactionLine sol ON sol.transaction = so.id
INNER JOIN NextTransactionLineLink NTLL
    ON NTLL.PreviousDoc = so.id
    AND NTLL.PreviousLine = sol.linesequencenumber
INNER JOIN Transaction ship ON ship.id = NTLL.NextDoc
INNER JOIN TransactionLine shipl
    ON shipl.transaction = ship.id
    AND shipl.linesequencenumber = NTLL.NextLine
WHERE so.type = 'SalesOrd'
  AND so.id = 12345
  AND NTLL.NextType = 'ItemShip'
ORDER BY sol.linesequencenumber

Sample Result:

sales_order	so_line	item	item_name	ordered_qty	shipped_qty	fulfillment	ship_line
SO-001	1	100	Widget A	10	10	IF-001	1
SO-001	2	101	Widget B	20	15	IF-001	2
SO-001	2	101	Widget B	20	5	IF-002	1

Find Partially Fulfilled Orders

-- Orders where shipped qty < ordered qty
WITH OrderedQty AS (
    SELECT
        t.id AS order_id,
        t.tranid,
        tl.linesequencenumber AS line_num,
        tl.item,
        tl.quantity AS ordered
    FROM Transaction t
    INNER JOIN TransactionLine tl ON tl.transaction = t.id
    WHERE t.type = 'SalesOrd'
      AND tl.mainline = 'F'
      AND tl.item IS NOT NULL
),
ShippedQty AS (
    SELECT
        NTLL.PreviousDoc AS order_id,
        NTLL.PreviousLine AS line_num,
        SUM(NTLL.Quantity) AS shipped
    FROM NextTransactionLineLink NTLL
    WHERE NTLL.NextType = 'ItemShip'
    GROUP BY NTLL.PreviousDoc, NTLL.PreviousLine
)
SELECT
    o.tranid,
    o.line_num,
    BUILTIN.DF(o.item) AS item,
    o.ordered,
    NVL(s.shipped, 0) AS shipped,
    o.ordered - NVL(s.shipped, 0) AS remaining
FROM OrderedQty o
LEFT JOIN ShippedQty s
    ON s.order_id = o.order_id
    AND s.line_num = o.line_num
WHERE o.ordered > NVL(s.shipped, 0)
ORDER BY o.tranid, o.line_num

---

Header-Level Queries

Simple Parent-Child Lookup

-- Using header-level tables (simpler, no line detail)
SELECT DISTINCT
    parent.tranid AS parent_doc,
    parent.type AS parent_type,
    child.tranid AS child_doc,
    child.type AS child_type,
    child.trandate
FROM NextTransactionLink NTL
INNER JOIN Transaction parent ON parent.id = NTL.PreviousDoc
INNER JOIN Transaction child ON child.id = NTL.NextDoc
WHERE NTL.PreviousDoc = 12345
ORDER BY child.trandate

Find Orders Without Invoices

-- Sales Orders that have no linked invoices
SELECT
    so.id,
    so.tranid,
    so.trandate,
    so.foreigntotal,
    BUILTIN.DF(so.entity) AS customer
FROM Transaction so
WHERE so.type = 'SalesOrd'
  AND so.id NOT IN (
      SELECT DISTINCT NTL.PreviousDoc
      FROM NextTransactionLink NTL
      WHERE NTL.NextType = 'CustInvc'
  )
  AND so.trandate >= ADD_MONTHS(SYSDATE, -6)
ORDER BY so.trandate DESC

---

Purchase Cycle Queries

PO to Receipt to Bill Chain

-- Track Purchase Order through Receipt to Vendor Bill
SELECT DISTINCT
    po.tranid AS purchase_order,
    po.trandate AS po_date,
    BUILTIN.DF(po.entity) AS vendor,
    rcpt.tranid AS item_receipt,
    rcpt.trandate AS receipt_date,
    bill.tranid AS vendor_bill,
    bill.trandate AS bill_date,
    bill.foreigntotal AS bill_amount
FROM Transaction po
-- PO to Receipt
LEFT JOIN NextTransactionLineLink ntll1
    ON ntll1.PreviousDoc = po.id
    AND ntll1.NextType = 'ItemRcpt'
LEFT JOIN Transaction rcpt ON rcpt.id = ntll1.NextDoc
-- Receipt to Bill
LEFT JOIN NextTransactionLineLink ntll2
    ON ntll2.PreviousDoc = rcpt.id
    AND ntll2.NextType = 'VendBill'
LEFT JOIN Transaction bill ON bill.id = ntll2.NextDoc
WHERE po.type = 'PurchOrd'
  AND po.trandate >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
ORDER BY po.trandate DESC

Unbilled Receipts Report

-- Item Receipts without Vendor Bills
SELECT
    rcpt.tranid AS receipt,
    rcpt.trandate AS receipt_date,
    BUILTIN.DF(rcpt.entity) AS vendor,
    po.tranid AS purchase_order,
    ROUND(SYSDATE - rcpt.trandate) AS days_unbilled
FROM Transaction rcpt
INNER JOIN PreviousTransactionLineLink ptll
    ON ptll.NextDoc = rcpt.id
    AND ptll.PreviousType = 'PurchOrd'
INNER JOIN Transaction po ON po.id = ptll.PreviousDoc
WHERE rcpt.type = 'ItemRcpt'
  AND rcpt.id NOT IN (
      SELECT DISTINCT ntll.PreviousDoc
      FROM NextTransactionLineLink ntll
      WHERE ntll.NextType = 'VendBill'
  )
ORDER BY days_unbilled DESC

---

Drop Ship Queries

Link Drop Ship SO to PO

-- Find drop ship POs created from Sales Orders
SELECT DISTINCT
    so.tranid AS sales_order,
    BUILTIN.DF(so.entity) AS customer,
    po.tranid AS drop_ship_po,
    BUILTIN.DF(po.entity) AS vendor,
    po.trandate AS po_date
FROM Transaction so
INNER JOIN NextTransactionLineLink ntll
    ON ntll.PreviousDoc = so.id
    AND ntll.LinkType = 'DropShip'
INNER JOIN Transaction po ON po.id = ntll.NextDoc
WHERE so.type = 'SalesOrd'
ORDER BY so.tranid

---

Performance Tips

Use DISTINCT

Since these are line-level joins, always use DISTINCT when you only need transaction-level results:

-- Without DISTINCT: May return duplicate rows for each line
SELECT t.tranid FROM NextTransactionLineLink NTLL
INNER JOIN Transaction t ON t.id = NTLL.NextDoc
WHERE NTLL.PreviousDoc = 12345

-- With DISTINCT: One row per transaction
SELECT DISTINCT t.tranid FROM NextTransactionLineLink NTLL
INNER JOIN Transaction t ON t.id = NTLL.NextDoc
WHERE NTLL.PreviousDoc = 12345

Filter Early

Apply filters before joining to reduce the dataset:

-- Good: Filter in subquery
SELECT *
FROM (
    SELECT DISTINCT NextDoc
    FROM NextTransactionLineLink
    WHERE PreviousDoc = 12345
      AND NextType = 'CustInvc'
) links
INNER JOIN Transaction t ON t.id = links.NextDoc

-- Avoid: Filter after large join
SELECT DISTINCT t.*
FROM NextTransactionLineLink NTLL
INNER JOIN Transaction t ON t.id = NTLL.NextDoc
WHERE NTLL.PreviousDoc = 12345
  AND t.type = 'CustInvc'

---

Alternative: CreatedFrom on TransactionLine

For some use cases, the createdfrom column on TransactionLine may be simpler:

-- Using TransactionLine.createdfrom
SELECT
    child.tranid AS child_doc,
    child.type AS child_type,
    parent.tranid AS parent_doc
FROM Transaction child
INNER JOIN TransactionLine tl ON tl.transaction = child.id
INNER JOIN Transaction parent ON parent.id = tl.createdfrom
WHERE child.id = 12355
  AND tl.mainline = 'T'

Limitation

The createdfrom field is not available on the Transaction table in SuiteQL, only on TransactionLine. For header-level lookups, use the link tables.

---

Quick Reference

Find Children (Next)

SELECT DISTINCT NextDoc, NextType
FROM NextTransactionLineLink
WHERE PreviousDoc = {parent_id}

Find Parent (Previous)

SELECT DISTINCT PreviousDoc, PreviousType
FROM PreviousTransactionLineLink
WHERE NextDoc = {child_id}

Common Join Pattern

FROM Transaction parent
INNER JOIN NextTransactionLineLink NTLL ON NTLL.PreviousDoc = parent.id
INNER JOIN Transaction child ON child.id = NTLL.NextDoc
WHERE parent.id = {id}

---

See Also

• Transaction Linkage Map - Visual diagrams of transaction flows
• Multi-Select Tables - Another type of junction table
• Junction Tables - Complete reference of junction tables