ERP Table and Field Explorer

NetSuite Tables and Field Explorer for SuiteQL and API Integration

Search the core Oracle NetSuite record types of the analytics data source by name, field or description, see each record's key, the columns that matter and its joins, then pick two to four records to get a SuiteQL query for the REST API, SuiteScript N/query or SuiteAnalytics Connect. Every transaction type lives in transaction and transactionline, told apart by type, and the GL impact is in transactionaccountingline.

39 tables, 282 documented fields and 79 join paths across 8 modules. Free, runs in your browser, no login needed.

1Find a table by name, description or field

/

Naming: NetSuite gives no direct database access. SuiteQL reads the analytics data source (NetSuite2.com in SuiteAnalytics Connect), whose tables are record type ids such as transaction, transactionline, customer and item. Every record has an internal id in id, and reference fields hold the internal id of the target record. All transaction types share transaction and transactionline, told apart by type. Subsidiary, department, class and location are fields, not table prefixes. Booleans are 'T' or 'F'. Oracle notes that the casing of record and field names in results can change after a release, so do not depend on it.

Tables by module

2Pick tables

39 of 39

Up and Down browse from the search box, Enter adds the table to the query, Esc clears.

Transactions

transaction

Transaction header: one row per sales order, invoice, purchase order, bill, fulfillment, receipt, journal and every other transaction type.

Also known as: sales order, invoice, purchase order, vendor bill, journal entry, transaction header, SalesOrd, CustInvc, PurchOrd

Key:id

Field	Meaning
id	Internal id of the transaction.
tranid	Document number shown in the UI, for example the sales order or invoice number.
trandate	Transaction date.
type	Transaction type code, for example SalesOrd, CustInvc, PurchOrd, VendBill, ItemShip, ItemRcpt, RtnAuth, Journal, Opprtnty.
entity	Internal id of the customer, vendor, employee or other entity on the transaction.
status	Status letter, for example B. BUILTIN.CF(status) returns the type-qualified code such as SalesOrd:B; BUILTIN.DF(status) returns the label.
foreigntotal	Transaction total in the transaction currency.
recordtype	Record type id, for example salesorder or invoice.
postingperiod	Accounting period the transaction posts to (accountingperiod.id).
currency	Transaction currency (currency.id).
otherrefnum	Customer PO number or other reference number.
memo	Header memo.
duedate	Due date, for invoices and bills.
posting	T when the transaction posts to the general ledger.
createddate	Date and time the record was created.
lastmodifieddate	Date and time of the last change; indexed, use it for incremental extracts.

Joins

transaction.id = transactionline.transaction
Filter tl.mainline = 'F' for item and expense lines, 'T' for the header line.
transaction.id = transactionaccountingline.transaction
GL lines, one set per accounting book.
transaction.entity = customer.id
Only for customer transactions (sales orders, invoices, credit memos).
transaction.entity = vendor.id
Only for vendor transactions (purchase orders, bills).
transaction.entity = entity.id
Any entity type.
transaction.postingperiod = accountingperiod.id
transaction.currency = currency.id
transaction.id = nexttransactionlink.previousdoc
Transactions created from this one, for example the fulfillments of a sales order.
transaction.id = transactionshipment.doc
Item fulfillments with shipping data.
transaction.id = trackingnumbermap.transaction
transaction.id = inventoryassignment.transaction
Lot and serial numbers assigned on the transaction.

All transaction types share this record: always filter type (for example type = 'SalesOrd') or recordtype (for example recordtype = 'salesorder'). Subsidiary, department, class and location are read from the lines, the main line (transactionline.mainline = 'T') for the header values. Custom body fields appear as extra columns with their script ids.

3Generate a JOIN skeleton (2 to 4 tables)

Starters:

Dialect: SuiteQL (analytics data source)

Run it with REST: POST /services/rest/query/v1/suiteql with the header Prefer: transient and the body {"q": "<query>"}, paging with the limit and offset URL parameters. In SuiteScript use the N/query module: query.runSuiteQL or query.runSuiteQLPaged (since 2020.1). Without SuiteAnalytics Connect, N/query returns at most 100,000 results.

Names are analytics data source ids: check fields and joins for your account in the Records Catalog (needs the Records Catalog permission). Custom fields appear with their script ids, for example custcol for line fields and custentity for entity fields.
Oracle recommends Oracle SQL syntax for SuiteQL because ANSI SQL-92 joins can cause timeouts. If a query times out, rewrite the joins as a comma list with WHERE conditions and (+) for outer joins. Do not mix both syntaxes in one query.
Filter dates with TO_DATE, for example trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD'); date literals are not supported. Use || to concatenate, not +.
Batch large reads on id ranges and filter on indexed fields such as id and lastmodifieddate. Avoid SELECT * and calculated fields.
Read only. Write through records (SuiteScript N/record or SuiteTalk REST web services).
No row limit in the query text: pass limit and offset on the REST call (for example ?limit=100), or use query.runSuiteQLPaged.
transaction to transactionline: Filter tl.mainline = 'F' for item and expense lines, 'T' for the header line.
transaction to customer: Only for customer transactions (sales orders, invoices, credit memos).
Filter on recordtype: replace with the record type you need, for example 'invoice', or filter type such as 'PurchOrd' instead

SELECT
    t.id,
    t.tranid,
    t.trandate,
    t.type,
    t.entity,
    tl.transaction,
    tl.linesequencenumber,
    tl.item,
    tl.quantity,
    tl.rate,
    i.itemid,
    i.displayname,
    i.description,
    i.itemtype,
    i.isinactive,
    c.entityid,
    c.companyname,
    c.email,
    c.phone,
    c.isperson
FROM transaction t
LEFT OUTER JOIN transactionline tl
    ON t.id = tl.transaction
LEFT OUTER JOIN item i
    ON tl.item = i.id
LEFT OUTER JOIN customer c
    ON t.entity = c.id
WHERE t.recordtype = 'salesorder'
ORDER BY t.id

Free, optional. Reopen it on any device.

Download:

How to use it

1
Search
Type a record id such as transactionline, a field such as quantitybackordered, a transaction code such as SalesOrd, or a business word such as invoice or bin. Field matches are highlighted under each record.
2
Filter and inspect
Narrow by module, then open a record to see its key, the fields that matter and the records it joins to. Click a join to jump to that record.
3
Build the query
Add two to four records with the plus button or a starter. The SuiteQL uses the documented join fields; a query that starts with transaction filters recordtype, which you change to the type you need.
4
Run it
POST the query to /services/rest/query/v1/suiteql with Prefer: transient, or run it in SuiteScript with query.runSuiteQLPaged. Results follow the permissions of the role you use.
5
Keep it
Star records as favorites, export to PDF, Excel, Word or CSV, and optionally save the setup to your Netray account to reopen it later.

Questions about Oracle NetSuite tables

Can I query NetSuite tables with SQL?

Not the database itself. NetSuite exposes SuiteQL, a SQL language over the analytics data source, the same data SuiteAnalytics Workbook shows. You can run it through SuiteTalk REST web services (POST /services/rest/query/v1/suiteql with Prefer: transient), the N/query module in SuiteScript, or SuiteAnalytics Connect over ODBC, JDBC or ADO.NET. All three are read only and enforce the role's permissions.

Where are sales orders, invoices and purchase orders stored?

All transaction types share one header record, transaction, and one line record, transactionline. Filter transaction.type ('SalesOrd', 'CustInvc', 'PurchOrd', 'VendBill', 'ItemShip', 'ItemRcpt', 'Journal') or recordtype ('salesorder', 'invoice'). Join lines on tl.transaction = t.id and use tl.mainline = 'F' for item lines; the main line ('T') carries header values such as subsidiary and location.

Why are quantities and amounts negative on sales order lines?

transactionline stores quantity and foreignamount with a sign. Lines of sales orders and item fulfillments come back negative, which is why published SuiteQL multiplies them by -1 to report sold quantities. Check the sign per transaction type before summing lines.

How do I get the general ledger impact of a transaction?

Use transactionaccountingline. It holds account, debit, credit, amount and posting per transaction line and accounting book. Join it on tal.transaction = tl.transaction AND tal.transactionline = tl.id, filter posting = 'T', and with Multi-Book Accounting pick one accountingbook. account.accttype gives the account type (for example Income, COGS, Expense), joined to accounttype for the name and the balance sheet flag.

How do I find the fulfillments or invoices created from a sales order?

nexttransactionlink links documents: previousdoc is the source, nextdoc the document created from it, and linktype tells the kind of link (fulfillments use PickPack or ShipRcpt). On lines, transactionline.createdfrom points back to the source transaction, and previoustransactionlinelink maps a line to its source line, for example a receipt line to its PO line.

Where is inventory on hand in NetSuite?

item.quantityonhand and quantityavailable are totals across locations. aggregateitemlocation holds quantities and the reorder point per item and location. Lot and serial numbers are in inventorynumber (with expirationdate), the lots moved by a transaction in inventoryassignment, and bin quantities in itembinquantity with bin for locations that use bins.

How do I read transaction status codes?

transaction.status holds the status letter. BUILTIN.CF(status) returns the type-qualified code, for example Opprtnty:A, and BUILTIN.DF(status) returns the label shown in the UI. Run SELECT DISTINCT type, status, BUILTIN.CF(status), BUILTIN.DF(status) FROM transaction once to see the codes in your account.

How many rows can SuiteQL return?

Up to 100,000 results per query through REST and N/query; query.runSuiteQL returns at most 5,000, so use runSuiteQLPaged or the REST limit and offset parameters for more. For larger extracts Oracle points to SuiteAnalytics Connect with the NetSuite2.com data source, the only Connect data source since NetSuite 2026.1.

Sources

Table names and columns were checked against these public references. Tables whose key or columns we could not confirm say so in their notes. Column sets change between releases, so confirm against the data dictionary of your own version before you build on it.