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