NetSuiteFree referencePDF, Excel, Word exportNetSuiteAll NetSuite tools

NetSuite SuiteQL Tables Reference: Keys, Columns and Joins

A lookup of the SuiteQL tables in the NetSuite2.com data source that come up in nearly every report, integration and data extract, with primary keys and the join condition that actually works. Use it next to the Records Catalog when you write SuiteQL, N/query or NetSuite2.com ODBC queries.

Download:

Table (SuiteQL)	What it holds	Primary key	Key columns	Common join (ON clause)	Notes
transaction	Header of every transaction: sales orders, invoices, POs, bills, fulfillments, journals, work orders	id	tranid, type, trandate, entity, status, postingperiod	transactionline.transaction = transaction.id	Filter on type (for example 'SalesOrd', 'CustInvc') before anything else. status is a bare letter; use BUILTIN.DF(status) for the label.
transactionline	Every line of every transaction, including the header (mainline) row and tax rows	transaction + id (uniquekey is unique across all lines)	transaction, id, mainline, taxline, item, quantity, netamount	transactionline.transaction = transaction.id	Add mainline = 'F' for item lines only and taxline = 'F' to drop tax rows. The mainline = 'T' row carries header-level values.
transactionaccountingline	GL impact of each transaction line per accounting book: account, debit, credit	transaction + transactionline + accountingbook	transaction, transactionline, account, accountingbook, debit, credit, amount, posting	transactionaccountingline.transaction = transactionline.transaction AND transactionaccountingline.transactionline = transactionline.id	Use this, not transactionline, for trial balance and GL reports. Filter posting = 'T' and one accountingbook when Multi-Book Accounting is on.
item	All item records: inventory, assembly, non-inventory, service, kit, other charge	id	id, itemid, displayname, itemtype, isinactive, parent	transactionline.item = item.id	itemid is the name/number users see. itemtype values include 'InvtPart', 'Assembly', 'NonInvtPart', 'Service', 'Kit'.
customer	Customer and customer-job records	id	id, entityid, companyname, email, isinactive, terms	transaction.entity = customer.id	transaction.entity points at whichever entity type the transaction uses; join customer only for sales-side types.
vendor	Vendor (supplier) records	id	id, entityid, companyname, email, isinactive, terms	transaction.entity = vendor.id	Use for purchase orders, vendor bills and vendor payments.
employee	Employee records, including supervisors and sales reps	id	id, entityid, firstname, lastname, email, supervisor	transaction.employee = employee.id	Employee data is permission sensitive; many integration roles cannot see it.
entity	Shared parent of customers, vendors, employees, partners and contacts	id	id, entityid, altname, email, isinactive	transaction.entity = entity.id	Use when one query spans sales and purchase transactions and you only need the name.
contact	Contact people attached to customers, vendors and partners	id	id, entityid, firstname, lastname, email, company	contact.company = customer.id	company holds the parent entity id.
account	Chart of accounts	id	id, acctnumber, fullname, accttype, isinactive, parent	transactionaccountingline.account = account.id	accttype values include 'Bank', 'AcctRec', 'AcctPay', 'Income', 'COGS', 'Expense'.
accountingperiod	Fiscal periods, quarters and years	id	id, periodname, startdate, enddate, isquarter, isyear, closed	transaction.postingperiod = accountingperiod.id	Filter isquarter = 'F' AND isyear = 'F' to get month periods only. isadjust flags adjustment periods.
subsidiary	Legal entities in a OneWorld account	id	id, name, fullname, parent, currency, iselimination	transactionline.subsidiary = subsidiary.id	Subsidiary sits on the line in SuiteQL; the header value is on the mainline row. Exclude iselimination = 'T' for operational reports.
department	Department segment values	id	id, name, fullname, parent, isinactive	transactionline.department = department.id	fullname includes the parent path, for example 'Operations : Machining'.
classification	Class segment values	id	id, name, fullname, parent, isinactive	transactionline.class = classification.id	The table is classification but the line column is class.
location	Warehouses, plants and stores	id	id, name, fullname, parent, subsidiary, isinactive	transactionline.location = location.id	Location on the line is the inventory location for item lines.
currency	Currencies enabled in the account	id	id, name, symbol, isbasecurrency, isinactive	transaction.currency = currency.id	symbol holds the ISO code such as 'USD' or 'EUR'. Without Multiple Currencies the table holds only the base currency.
currencyrate	Daily exchange rates between currencies	id	basecurrency, transactioncurrency, exchangerate, effectivedate	currencyrate.transactioncurrency = transaction.currency	Pick the latest effectivedate on or before the transaction date. BUILTIN.CURRENCY_CONVERT usually saves you this join.
consolidatedexchangerate	Period consolidation rates (current, average, historical) between subsidiaries	id	postingperiod, fromsubsidiary, tosubsidiary, currentrate, averagerate, historicalrate	consolidatedexchangerate.postingperiod = accountingperiod.id	OneWorld only. BUILTIN.CONSOLIDATE applies these rates for you.
term	Payment terms such as Net 30	id	id, name, daysuntilnetdue, discountpercent, isinactive	transaction.terms = term.id	Date-driven terms use other columns; check the record in your account.
systemnote	Audit trail of field changes on records and transactions	id	recordid, recordtypeid, field, oldvalue, newvalue, date, name	systemnote.recordid = transaction.id	name is the user who made the change. Large table: always filter by recordid or date.
file	File Cabinet files	id	id, name, folder, filetype, filesize, createddate	file.folder = mediaitemfolder.id	Useful for File Cabinet audits and storage clean-up.
nexttransactionlinelink	Line-level links from a source line to the lines created from it	previousdoc + previousline + nextdoc + nextline	previousdoc, previousline, nextdoc, nextline, linktype	nexttransactionlinelink.previousdoc = transactionline.transaction AND nexttransactionlinelink.previousline = transactionline.id	Trace a sales order line to its fulfillment and invoice lines. linktype tells you the kind of link.
previoustransactionlinelink	The same line links read from the downstream side	previousdoc + previousline + nextdoc + nextline	previousdoc, previousline, nextdoc, nextline, linktype	previoustransactionlinelink.nextdoc = transactionline.transaction AND previoustransactionlinelink.nextline = transactionline.id	Start from an invoice line and walk back to the order line.
nexttransactionlink	Header-level links from a transaction to the transactions created from it	previousdoc + nextdoc	previousdoc, nextdoc, linktype	nexttransactionlink.previousdoc = transaction.id	Faster than line links when you only need document to document.
previoustransactionlink	Header-level links read from the downstream transaction	previousdoc + nextdoc	previousdoc, nextdoc, linktype	previoustransactionlink.nextdoc = transaction.id	Payment to invoice links appear here as well as fulfillment to order links.
inventoryitemlocations	Quantities per item per location	item + location	item, location, quantityonhand, quantityavailable, quantityonorder, quantitycommitted	inventoryitemlocations.item = item.id	Requires inventory permissions. Values are current, not as of a past date.
pricelevel	Price levels such as Base Price and Online Price	id	id, name, isinactive	transactionline.price = pricelevel.id	Item prices per level live in a separate pricing table; confirm its name in your Records Catalog.
unitstype	Units of measure types (for example Each/Box/Case)	id	id, name, isinactive	item.unitstype = unitstype.id	Needs Multiple Units of Measure. transactionline.units holds the unit used on the line.
bom	Bill of materials headers (Advanced BOM)	id	id, name, isinactive	bomrevision.billofmaterials = bom.id	Only present when Advanced Bill of Materials is enabled.
bomrevision	Revisions of each BOM with effective dates	id	id, name, billofmaterials, effectivestartdate, effectiveenddate	bomrevision.billofmaterials = bom.id	Pick the revision whose effective range covers your build date. Component lines sit in a child table; check its name in Records Catalog.
inventorynumber	Lot and serial numbers	id	id, inventorynumber, item, expirationdate	inventorynumber.item = item.id	Needs Lot Tracking or Serialized Inventory.
customrecord_<scriptid>	Pattern row: each custom record type is its own table named by its script ID	id	id, name, isinactive, owner, created, lastmodified	customrecord_xyz.custrecord_xyz_item = item.id	Custom fields appear as custrecord_* columns. The table name is lowercase script ID, for example customrecord_qc_inspection.
customlist_<scriptid>	Pattern row: each custom list is a small table of values	id	id, name, isinactive	transaction.custbody_xyz = customlist_xyz.id	Or skip the join and use BUILTIN.DF(custbody_xyz) to get the list value text.

These are NetSuite2.com (Analytics) data source names used by SuiteQL, N/query, the REST suiteql endpoint and the NetSuite2.com ODBC/JDBC schema. The older NetSuite.com ODBC schema uses different names (for example transactions and transaction_lines), so queries do not port between the two. Which tables and columns you see depends on enabled features and the role's permissions. Confirm any table in Setup > Records Catalog of your own account.

Get your SuiteQL joins checked

Send us a query that returns the wrong totals and we will check the joins and line filters against your Records Catalog.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search the table by name or by the business object you need, then copy the ON clause. The pattern behind most queries is transaction joined to transactionline, then out to item, entity, account, period and segment tables. For GL numbers go through transactionaccountingline instead of transactionline, because only the accounting line knows the account, debit and credit per book. For document flow (order to fulfillment to invoice), use the link tables rather than createdfrom alone, since one order line can feed several downstream lines.

Lines only: transactionline.mainline = 'F' AND transactionline.taxline = 'F'
Header values: join the mainline = 'T' row or read transaction directly
Names without joins: BUILTIN.DF(column)

Worked example

Open sales order lines with item and customer: SELECT t.tranid, BUILTIN.DF(t.entity) AS customer, i.itemid, tl.quantity FROM transaction t JOIN transactionline tl ON tl.transaction = t.id JOIN item i ON tl.item = i.id WHERE t.type = 'SalesOrd' AND tl.mainline = 'F' AND tl.taxline = 'F' AND tl.isclosed = 'F'. Add JOIN accountingperiod ap ON t.postingperiod = ap.id to filter by period name, or JOIN subsidiary s ON tl.subsidiary = s.id to split by legal entity. Expect quantity to come back negative on sales lines; wrap it in ABS() or multiply by -1 for display.

Version differences and gotchas

NetSuite has two analytics schemas. NetSuite2.com is the one SuiteQL uses and the one Oracle develops; the legacy NetSuite.com ODBC schema has plural table names and different columns, so a query copied from an old ODBC report will fail in SuiteQL. Tables also appear and disappear with features: bom and bomrevision need Advanced BOM, consolidatedexchangerate needs OneWorld, inventorynumber needs lot or serial tracking. A role without permission to a record type gets an error or an empty result, not a warning, which looks like missing data.

How to check the result in the system

Go to Setup > Records Catalog. Search the record, open it and look at the Fields tab for every column name and data type and the Joins tab for every supported join, including the exact join fields. The catalog reflects your account's features and custom fields, so it is the final word for your environment. Then run the query in a SuiteQL tool or through N/query with a ROWNUM filter to check row counts against a saved search on the same criteria.

Frequently Asked Questions

Where can I see the list of tables in NetSuite?

Setup > Records Catalog lists every record type available to SuiteQL in your account, with its fields and joins. It reflects your enabled features and custom records. For the older ODBC schema, Oracle publishes a separate NetSuite.com schema browser, but those names do not work in SuiteQL.

How do I join transaction and transactionline in SuiteQL?

Use transactionline.transaction = transaction.id. Each transaction has one mainline = 'T' row plus item, tax and other lines. Add mainline = 'F' and taxline = 'F' for item lines, or keep mainline = 'T' when you want one row per transaction.

Which table has the GL account for a transaction?

transactionaccountingline. Join it with transaction = transactionline.transaction and transactionline = transactionline.id, then join account on account.id. Filter posting = 'T' and pick one accountingbook if Multi-Book Accounting is enabled, or totals will double.

Are NetSuite ODBC tables the same as SuiteQL tables?

Only for the NetSuite2.com data source. If your ODBC connection uses NetSuite2.com, table names match SuiteQL. If it still uses the legacy NetSuite.com data source, names such as transactions and transaction_lines differ and queries need rewriting.

If you want your team to ask NetSuite these questions in plain English, with the SuiteQL shown and roles respected, we can set up a private AI layer over your account.

Talk to an Expert Explore More Free Tools