Junction Tables Reference

A comprehensive reference of NetSuite's junction tables - hidden tables that link records together in one-to-many and many-to-many relationships.

---

What Are Junction Tables?

JUNCTION TABLE CONCEPT
═══════════════════════════════════════════════════════════════════════════════

ONE-TO-MANY (Sublist pattern)                MANY-TO-MANY (Mapping pattern)
┌─────────────┐     ┌─────────────┐         ┌─────────────┐     ┌─────────────┐
│ Transaction │     │ Transaction │         │  Customer   │     │ Lead Source │
│   Header    │     │    Line     │         │             │     │    List     │
│   ID: 100   │────▶│ Trans: 100  │         │   ID: 50    │     │   ID: 1-5   │
│             │     │ Line: 1,2,3 │         │             │     │             │
└─────────────┘     └─────────────┘         └──────┬──────┘     └──────┬──────┘
                                                   │                   │
Parent table        Junction table                 │   ┌───────────┐   │
(1 row)             (many rows)                    └──▶│ map_table │◀──┘
                                                       │ 50 → 1    │
                                                       │ 50 → 3    │
                                                       │ 50 → 5    │
                                                       └───────────┘

---

Transaction Junction Tables

Transaction to Lines

Table	Links	Purpose	Key Columns
TransactionLine	Transaction → Lines	Item lines, expense lines	transaction, linesequencenumber, item, quantity, amount
TransactionAccountingLine	Transaction → GL Lines	Debit/Credit entries	transaction, transactionline, account, debit, credit
InventoryAssignment	TransactionLine → Lot/Serial	Inventory detail	transaction, transactionline, inventorynumber, quantity, binnumber
TransactionShipGroup	Transaction → Shipping Groups	Multiple ship-to addresses	transaction, shipaddress, shipmethod

Transaction to Transaction Links

Table	Links	Purpose	Key Columns
NextTransactionLineLink	Parent → Child (line)	SO → Invoice, PO → Receipt	previousdoc, nextdoc, previousline, nextline, quantity
PreviousTransactionLineLink	Child → Parent (line)	Reverse lookup	Same as above
NextTransactionLink	Parent → Child (header)	Header-only linking	previousdoc, nextdoc
PreviousTransactionLink	Child → Parent (header)	Reverse lookup	Same as above

Example: Transaction with Lines and GL

-- Complete transaction with lines and accounting impact
SELECT
    t.tranid,
    t.trandate,
    tl.linesequencenumber AS line,
    BUILTIN.DF(tl.item) AS item,
    tl.quantity,
    tl.amount AS line_amount,
    BUILTIN.DF(tal.account) AS gl_account,
    tal.debit,
    tal.credit
FROM Transaction t
INNER JOIN TransactionLine tl ON tl.transaction = t.id
LEFT JOIN TransactionAccountingLine tal
    ON tal.transaction = t.id
    AND tal.transactionline = tl.id
WHERE t.id = 12345
ORDER BY tl.linesequencenumber, tal.account

---

Item Junction Tables

Item Components and Relationships

Table	Links	Purpose	Key Columns
ItemMember	Assembly/Kit → Components	BOM components	parentitem, item, quantity, memberunit
ItemVendor	Item → Vendors	Preferred vendors	item, vendor, purchaseprice, preferredvendor
ItemImage	Item → Images	Product photos	item, name, alttagcaption
ItemPrice	Item → Price Levels	Pricing matrix	item, currency, pricelevel, unitprice

Item Location Tables

Table	Links	Purpose	Key Columns
InventoryItemLocations	Item → Locations	Stock by location	item, location, quantityonhand, quantityavailable
AggregateItemLocation	Item → Location (summary)	Aggregated inventory	item, location, quantityonhand, averagecost
ItemLocationConfiguration	Item → Location Settings	Reorder points, bins	item, location, preferredbin, reorderpoint

Lot/Serial Tables

Table	Links	Purpose	Key Columns
InventoryNumber	Item → Serial/Lot Numbers	Serial/lot master	item, inventorynumber, expirationdate, status
InventoryNumberLocation	InventoryNumber → Locations	Lot/serial by location	inventorynumber, location, quantityonhand
InventoryBalance	Item → Location → Bin	Current bin inventory	item, location, binnumber, quantityonhand

Example: Item with Components and Vendors

-- Assembly item with BOM and vendor info
SELECT
    parent.itemid AS assembly,
    parent.displayname AS assembly_name,
    member.itemid AS component,
    im.quantity AS component_qty,
    BUILTIN.DF(im.memberunit) AS uom,
    iv.vendor,
    BUILTIN.DF(iv.vendor) AS vendor_name,
    iv.purchaseprice
FROM Item parent
INNER JOIN ItemMember im ON im.parentitem = parent.id
INNER JOIN Item member ON member.id = im.item
LEFT JOIN ItemVendor iv ON iv.item = member.id AND iv.preferredvendor = 'T'
WHERE parent.id = 500  -- Assembly item ID
ORDER BY im.linenumber

---

Entity Junction Tables

Customer/Vendor Relationships

Table	Links	Purpose	Key Columns
EntityAddress	Entity → Addresses	Address book	entity, addressee, address1, city, state, zip
Contact	Entity → Contacts	Related contacts	company, firstname, lastname, email, phone
CustomerSubsidiaryRelationship	Customer → Subsidiaries	Multi-subsidiary	entity, subsidiary
EntityGroupMember	Group → Members	Group membership	entitygroup, entity

Employee Tables

Table	Links	Purpose	Key Columns
EmployeeRolesForSearch	Employee → Roles	Role assignments	employee, role
RolePermissions	Role → Permissions	Permission grants	role, permission, level

Example: Customer with Addresses and Contacts

-- Customer with all addresses and primary contact
SELECT
    c.id,
    c.companyname,
    c.email AS company_email,
    addr.label AS address_type,
    addr.addressee,
    addr.address1,
    addr.city,
    addr.state,
    addr.zip,
    con.firstname || ' ' || con.lastname AS contact_name,
    con.email AS contact_email,
    con.phone AS contact_phone
FROM Customer c
LEFT JOIN EntityAddress addr ON addr.entity = c.id
LEFT JOIN Contact con ON con.company = c.id AND con.isprimarycontact = 'T'
WHERE c.id = 100
ORDER BY addr.label

---

File/Attachment Tables

Table	Links	Purpose	Key Columns
File	Standalone	File cabinet entries	id, name, folder, filetype, url
MediaItemFolder	Folder hierarchy	Folder structure	id, name, parent, appfolder
MessageFile	Message → Files	Email attachments	message, file
ItemImage	Item → File	Product images	item, name → joins to File

Example: Message with Attachments

-- Email messages with file attachments
SELECT
    m.id AS message_id,
    m.subject,
    m.messagedate,
    f.name AS attachment_name,
    f.filetype,
    f.filesize,
    f.url
FROM Message m
INNER JOIN MessageFile mf ON mf.message = m.id
INNER JOIN File f ON f.id = mf.file
WHERE m.transaction = 12345
ORDER BY m.messagedate, f.name

---

Audit/History Tables

Table	Links	Purpose	Key Columns
SystemNote	Any Record → Changes	Change audit trail	recordid, recordtype, field, oldvalue, newvalue, date, name
LoginAudit	User → Logins	Login history	user, date, ipaddress, status
DeletedRecord	N/A	Deleted record log	recordtype, customrecordtype, name, deleteddate, deletedby

Example: Audit Trail for Transaction

-- All changes to a transaction
SELECT
    sn.date AS change_date,
    BUILTIN.DF(sn.name) AS changed_by,
    sn.field AS field_changed,
    sn.oldvalue,
    sn.newvalue,
    sn.context  -- UIF=UI, SCH=Scheduled, SLT=Suitelet, etc.
FROM SystemNote sn
WHERE sn.recordid = 12345
  AND sn.recordtype = -30  -- Transaction record type
ORDER BY sn.date DESC

Example: Line-Level Audit

-- Changes to specific transaction lines
SELECT
    sn.date,
    BUILTIN.DF(sn.name) AS changed_by,
    sn.lineid AS line_number,
    sn.field,
    sn.oldvalue,
    sn.newvalue
FROM SystemNote sn
WHERE sn.recordid = 12345
  AND sn.lineid IS NOT NULL
ORDER BY sn.date DESC, sn.lineid

---

Currency/Rate Tables

Table	Links	Purpose	Key Columns
CurrencyRate	Currency → Rates	Historical exchange rates	basecurrency, transactioncurrency, exchangerate, effectivedate
ConsolidatedExchangeRate	Subsidiary → Period → Rates	Consolidation rates	fromsubsidiary, tosubsidiary, period, currentrate, averagerate, historicalrate

Example: Historical Exchange Rate

-- Get exchange rate for a specific date
SELECT
    cr.effectivedate,
    BUILTIN.DF(cr.basecurrency) AS base,
    BUILTIN.DF(cr.transactioncurrency) AS target,
    cr.exchangerate
FROM CurrencyRate cr
WHERE cr.basecurrency = 1  -- USD
  AND cr.transactioncurrency = 3  -- EUR
  AND cr.effectivedate <= TO_DATE('2024-12-01', 'YYYY-MM-DD')
ORDER BY cr.effectivedate DESC
FETCH FIRST 1 ROW ONLY

---

Multi-Select Mapping Tables

Auto-generated for every multi-select field:

Pattern	Structure	Example
map_{record}_{field_id}	mapone, maptwo	map_customer_custentity_skills

See Multi-Select Tables for detailed documentation.

---

Pricing Tables

Table	Links	Purpose	Key Columns
ItemPrice	Item → Prices	Base pricing	item, pricelevel, currency, unitprice
PricingGroup	Item → Pricing Groups	Group-based pricing	item, pricinggroup
CustomerPricing	Customer → Item Prices	Customer-specific	customer, item, price

Example: Item with All Price Levels

-- All prices for an item across levels and currencies
SELECT
    i.itemid,
    i.displayname,
    BUILTIN.DF(ip.currency) AS currency,
    BUILTIN.DF(ip.pricelevel) AS price_level,
    ip.unitprice
FROM Item i
INNER JOIN ItemPrice ip ON ip.item = i.id
WHERE i.id = 500
ORDER BY ip.currency, ip.pricelevel

---

Billing/Revenue Tables

Table	Links	Purpose	Key Columns
BillingSchedule	Transaction → Billing	Billing milestones	transaction, billdate, billamount
RevenueRecognitionSchedule	Transaction → Revenue	Rev rec schedules	transaction, scheduledate, amount

---

Quick Reference: Finding Junction Tables

Common Patterns

-- Transaction to lines
TransactionLine, TransactionAccountingLine, InventoryAssignment

-- Transaction to transaction
NextTransactionLineLink, PreviousTransactionLineLink

-- Record to addresses
EntityAddress, Address (joined via addressbook)

-- Record to files
MessageFile, ItemImage (name → File.name)

-- Record to history
SystemNote (recordid + recordtype)

-- Multi-select fields
map_{record}_{field_id}

Discovery Methods

1. Records Catalog: Setup → Records Catalog → Search record type
2. SuiteQL Query Tool: Tables Reference feature
3. Tim Dietrich Reference: ~800 tables documented at timdietrich.me

---

Common Join Patterns

One-to-Many (Header → Lines)

FROM {header_table} h
INNER JOIN {line_table} l ON l.{header_fk} = h.id

Many-to-Many (via Mapping)

FROM {table1} t1
INNER JOIN map_{table1}_{field} m ON m.mapone = t1.id
INNER JOIN {table2} t2 ON t2.id = m.maptwo

Hierarchical (Self-Join)

FROM {table} child
LEFT JOIN {table} parent ON parent.id = child.{parent_fk}

Audit (Record → History)

FROM {record_table} r
INNER JOIN SystemNote sn ON sn.recordid = r.id
                        AND sn.recordtype = {type_id}

---

See Also

• Transaction Link Tables - Detailed transaction linking
• Multi-Select Tables - Multi-select field patterns
• Snapshot Tables - How master data copies to transactions