# NetSuite SuiteQL Transaction and TransactionLine Columns Reference > Source: https://netray.co/tools/netsuite-suiteql-transaction-and-transactionline-columns-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Column-by-column guide to the two SuiteQL tables behind every transaction query, with types, example values and the sign conventions that trip people up. ## Category netsuite ## Erp NetSuite ## Audience - NetSuite developer - BI and reporting analyst - NetSuite administrator ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Table **Key:** table **Type:** text #### Column **Key:** column **Type:** text #### Data type **Key:** data_type **Type:** text #### Meaning **Key:** meaning **Type:** text #### Example value **Key:** example_value **Type:** text #### Gotcha **Key:** gotcha **Type:** text **Rows:** **Table:** transaction **Column:** id **Data type:** integer **Meaning:** Internal ID of the transaction **Example value:** 48213 **Gotcha:** Same number space across all transaction types. **Table:** transaction **Column:** tranid **Data type:** text **Meaning:** Document number users see **Example value:** SO10432 **Gotcha:** Not unique across types; an invoice and an order can share a number. **Table:** transaction **Column:** trandisplayname **Data type:** text **Meaning:** Type plus number as shown in lists **Example value:** Sales Order #SO10432 **Gotcha:** For display only; do not parse it. **Table:** transaction **Column:** type **Data type:** text **Meaning:** Transaction type code **Example value:** SalesOrd **Gotcha:** Codes are case sensitive: CustInvc, PurchOrd, VendBill, ItemShip. **Table:** transaction **Column:** recordtype **Data type:** text **Meaning:** Record type id as used by SuiteScript **Example value:** salesorder **Gotcha:** Lowercase script id, different from type. **Table:** transaction **Column:** trandate **Data type:** date **Meaning:** Transaction date **Example value:** 2026-10-14 **Gotcha:** Compare with TO_DATE('2026-10-01', 'YYYY-MM-DD'), not a bare string. **Table:** transaction **Column:** entity **Data type:** reference **Meaning:** Customer, vendor or other entity on the header **Example value:** 1187 **Gotcha:** Points at different entity types per transaction type; use BUILTIN.DF(entity) for the name. **Table:** transaction **Column:** status **Data type:** text **Meaning:** Status letter for this transaction type **Example value:** B **Gotcha:** Bare letter. BUILTIN.DF(status) gives the label, BUILTIN.CF(status) gives 'SalesOrd:B'. **Table:** transaction **Column:** postingperiod **Data type:** reference **Meaning:** Accounting period **Example value:** 301 **Gotcha:** Join accountingperiod.id; empty on non-posting types such as sales orders. **Table:** transaction **Column:** posting **Data type:** boolean T/F **Meaning:** Whether the transaction posts to the GL **Example value:** T **Gotcha:** Sales orders and estimates are 'F'. **Table:** transaction **Column:** currency **Data type:** reference **Meaning:** Transaction currency **Example value:** 2 **Gotcha:** Join currency.id; symbol holds the ISO code. **Table:** transaction **Column:** exchangerate **Data type:** currency **Meaning:** Rate from transaction currency to subsidiary base currency **Example value:** 1.0846 **Gotcha:** 1 when transaction and subsidiary currency match. **Table:** transaction **Column:** foreigntotal **Data type:** currency **Meaning:** Document total in transaction currency **Example value:** 12450.00 **Gotcha:** Header total; do not add it to line sums or you double count. **Table:** transaction **Column:** memo **Data type:** text **Meaning:** Header memo **Example value:** Rush order, ship complete **Gotcha:** Line memo is a separate column on transactionline. **Table:** transaction **Column:** approvalstatus **Data type:** reference **Meaning:** Approval status list value **Example value:** 2 **Gotcha:** Use BUILTIN.DF(approvalstatus) for Pending Approval, Approved or Rejected. **Table:** transaction **Column:** createddate **Data type:** date **Meaning:** When the record was created **Example value:** 2026-10-14 09:12 **Gotcha:** Datetime, unlike trandate; TRUNC() it before comparing to a date. **Table:** transaction **Column:** lastmodifieddate **Data type:** date **Meaning:** Last change timestamp **Example value:** 2026-10-15 16:40 **Gotcha:** Use for incremental extracts: lastmodifieddate > last run time. **Table:** transaction **Column:** voided **Data type:** boolean T/F **Meaning:** Whether the transaction is voided **Example value:** F **Gotcha:** Filter voided = 'F' in financial reports. **Table:** transaction **Column:** duedate **Data type:** date **Meaning:** Payment due date **Example value:** 2026-11-13 **Gotcha:** Filled on invoices and bills, empty on most other types. **Table:** transaction **Column:** employee **Data type:** reference **Meaning:** Employee field on the header **Example value:** 57 **Gotcha:** Meaning varies by type; confirm in Records Catalog for yours. **Table:** transaction **Column:** terms **Data type:** reference **Meaning:** Payment terms **Example value:** 3 **Gotcha:** Join term.id or use BUILTIN.DF(terms). **Table:** transaction **Column:** otherrefnum **Data type:** text **Meaning:** Customer PO number or vendor reference **Example value:** PO-77821 **Gotcha:** Free text; trim and upper-case before matching. **Table:** transaction **Column:** shipdate **Data type:** date **Meaning:** Header ship date **Example value:** 2026-10-20 **Gotcha:** Line-level expected ship dates can differ. **Table:** transactionline **Column:** transaction **Data type:** reference **Meaning:** Parent transaction id **Example value:** 48213 **Gotcha:** Join key to transaction.id. **Table:** transactionline **Column:** id **Data type:** integer **Meaning:** Line id within the transaction **Example value:** 3 **Gotcha:** Unique only inside one transaction; the mainline row is usually id 0. **Table:** transactionline **Column:** uniquekey **Data type:** integer **Meaning:** Line key unique across all transactions **Example value:** 915532 **Gotcha:** Use as a single-column line key in extracts. **Table:** transactionline **Column:** linesequencenumber **Data type:** integer **Meaning:** Order of the line as displayed **Example value:** 2 **Gotcha:** Not the same as id; sort by this for print order. **Table:** transactionline **Column:** mainline **Data type:** boolean T/F **Meaning:** Header summary row **Example value:** T **Gotcha:** Every transaction has one mainline = 'T' row holding header-level values. **Table:** transactionline **Column:** taxline **Data type:** boolean T/F **Meaning:** Tax line **Example value:** F **Gotcha:** Exclude with taxline = 'F' when summing item amounts. **Table:** transactionline **Column:** item **Data type:** reference **Meaning:** Item on the line **Example value:** 922 **Gotcha:** Empty on mainline and on many journal lines. **Table:** transactionline **Column:** itemtype **Data type:** text **Meaning:** Type of the item on the line **Example value:** InvtPart **Gotcha:** Saves a join to item when you only need to filter by type. **Table:** transactionline **Column:** quantity **Data type:** number **Meaning:** Line quantity in the line unit **Example value:** -25 **Gotcha:** Negative on sales order lines; test on your account and use ABS() for display. **Table:** transactionline **Column:** rate **Data type:** currency **Meaning:** Unit price **Example value:** 18.50 **Gotcha:** Can be empty for lines priced by amount only. **Table:** transactionline **Column:** netamount **Data type:** currency **Meaning:** Line amount in subsidiary base currency **Example value:** -462.50 **Gotcha:** Revenue lines carry a negative sign on sales transactions. **Table:** transactionline **Column:** foreignamount **Data type:** currency **Meaning:** Line amount in transaction currency **Example value:** -426.43 **Gotcha:** Same sign rule as netamount: negative for revenue on sales transactions. **Table:** transactionline **Column:** creditforeignamount **Data type:** currency **Meaning:** Credit side of the line in transaction currency **Example value:** 426.43 **Gotcha:** Positive number; empty when the line is a debit. **Table:** transactionline **Column:** memo **Data type:** text **Meaning:** Line memo or description **Example value:** Bracket, zinc plated **Gotcha:** Separate from the header memo. **Table:** transactionline **Column:** location **Data type:** reference **Meaning:** Inventory location of the line **Example value:** 4 **Gotcha:** Can differ from the header location. **Table:** transactionline **Column:** subsidiary **Data type:** reference **Meaning:** Subsidiary of the line **Example value:** 2 **Gotcha:** Read it here; the header subsidiary is on the mainline row. **Table:** transactionline **Column:** department **Data type:** reference **Meaning:** Department segment **Example value:** 7 **Gotcha:** Line value can override the header. **Table:** transactionline **Column:** class **Data type:** reference **Meaning:** Class segment **Example value:** 5 **Gotcha:** Joins to the classification table, not a table named class. **Table:** transactionline **Column:** entity **Data type:** reference **Meaning:** Entity on the line **Example value:** 1187 **Gotcha:** Useful on journals where each line can name a different entity. **Table:** transactionline **Column:** createdfrom **Data type:** reference **Meaning:** Source transaction this line was created from **Example value:** 48190 **Gotcha:** Points at one source; use the link tables when a line has several. **Table:** transactionline **Column:** isclosed **Data type:** boolean T/F **Meaning:** Line closed **Example value:** F **Gotcha:** Filter isclosed = 'F' for open order reports. **Table:** transactionline **Column:** quantityshiprecv **Data type:** number **Meaning:** Quantity fulfilled or received so far **Example value:** 10 **Gotcha:** Open quantity = ABS(quantity) - quantityshiprecv; check signs on a test order. **Table:** transactionline **Column:** quantitybilled **Data type:** number **Meaning:** Quantity invoiced or billed so far **Example value:** 10 **Gotcha:** Compare with quantityshiprecv to find shipped-not-billed lines. **Table:** transactionline **Column:** quantitycommitted **Data type:** number **Meaning:** Quantity committed from stock **Example value:** 15 **Gotcha:** Depends on commitment settings per line. **Table:** transactionline **Column:** isinventoryaffecting **Data type:** boolean T/F **Meaning:** Line moves inventory **Example value:** T **Gotcha:** Use with fulfillments, receipts and adjustments for stock movement reports. **Table:** transactionline **Column:** iscogs **Data type:** boolean T/F **Meaning:** Line is a COGS line **Example value:** F **Gotcha:** Fulfillments create COGS lines; exclude them when you want item lines only. **Table:** transactionline **Column:** expenseaccount **Data type:** reference **Meaning:** Expense account on the line **Example value:** 112 **Gotcha:** For GL truth use transactionaccountingline.account. **Table:** transactionline **Column:** units **Data type:** reference **Meaning:** Unit of measure used on the line **Example value:** 3 **Gotcha:** Needs Multiple Units of Measure; quantity is in this unit. **Notes:** Columns verified for the NetSuite2.com data source used by SuiteQL. Sign conventions can surprise you: run a test query on one known sales order and compare quantity, netamount and foreignamount with the screen before building reports. Availability of some columns depends on enabled features and role permissions; Setup > Records Catalog (Transaction and Transaction Line) is the final word for your account. ## Content **Intro:** A column reference for the transaction and transactionline tables in NetSuite SuiteQL, with data types, sample values and the gotchas that make totals wrong. It applies to SuiteQL in N/query, the REST suiteql endpoint and the NetSuite2.com ODBC schema. **Sections:** #### How to use it **Body:** Filter the table column to transaction or transactionline, then search for the concept you need: date, quantity, amount, status, segment. The gotcha column is the part to read before you write the WHERE clause. The two tables split the work: transaction holds one row per document with header facts such as type, date, status and period; transactionline holds every line including a mainline = 'T' summary row. Segments (subsidiary, department, class, location) are read from the line in SuiteQL. **Bullets:** - Header facts: transaction - Item lines: transactionline with mainline = 'F' AND taxline = 'F' - GL accounts: transactionaccountingline, not transactionline #### Worked example: open order quantity **Body:** SELECT t.tranid, BUILTIN.DF(t.status) AS status_label, tl.linesequencenumber, BUILTIN.DF(tl.item) AS item, ABS(tl.quantity) AS ordered, tl.quantityshiprecv AS shipped FROM transaction t JOIN transactionline tl ON tl.transaction = t.id WHERE t.type = 'SalesOrd' AND t.status IN ('B', 'D', 'E') AND tl.mainline = 'F' AND tl.taxline = 'F' AND tl.isclosed = 'F' ORDER BY t.tranid, tl.linesequencenumber. Status B, D and E are Pending Fulfillment, Partially Fulfilled and Pending Billing/Partially Fulfilled. Check one order on screen to confirm the sign of quantity in your account before subtracting shipped from ordered. #### Version differences and gotchas **Body:** On sales transactions, revenue lines come back with negative netamount and foreignamount and sales line quantity is negative, because SuiteQL exposes the accounting sign rather than the screen value. The mainline row duplicates header totals, so summing all lines of a transaction without mainline = 'F' double counts. status is stored as one letter per type, so 'B' means Pending Fulfillment on a sales order and Paid In Full on an invoice: always pair it with type. trandate is a date while createddate and lastmodifieddate are timestamps. #### How to check the result in the system **Body:** Open Setup > Records Catalog and search Transaction, then Transaction Line. The Fields tab lists every column with its type for your account, including custbody and custcol custom fields, which appear as extra columns on these same tables. Run your query for one tranid and compare each value with the transaction form and its GL Impact subtab. If a column is missing, a feature is off or the role lacks permission. **Cta:** If your planners and controllers want to ask open order and billing questions in plain English, we can put a private AI layer over NetSuite that writes and shows this SuiteQL for them. **Faq:** #### Why is quantity negative in SuiteQL transactionline? **Answer:** SuiteQL returns the stored accounting sign. On sales orders and invoices, line quantity and revenue amounts come back negative even though the form shows positive numbers. Use ABS() or multiply by -1 in the SELECT, and test on one known order first. #### What does mainline mean in transactionline? **Answer:** Each transaction has one line with mainline = 'T' that summarises the header, plus regular lines with mainline = 'F'. Filter mainline = 'F' for item detail, or mainline = 'T' for one row per transaction. Mixing them double counts totals. #### How do I get the status label instead of a letter? **Answer:** Wrap the column: BUILTIN.DF(t.status) returns the label such as Pending Fulfillment. BUILTIN.CF(t.status) returns the combined code such as SalesOrd:B, which Oracle recommends for filters on composite fields like status. #### Where are custom body and column fields in SuiteQL? **Answer:** custbody fields are columns on transaction and custcol fields are columns on transactionline, named by their script IDs. Use BUILTIN.DF() on list or record fields to get the text value. ## Search terms netsuite transaction table fields, netsuite suiteql tables, netsuite suiteql reference, netsuite suiteql query, suiteql transactionline columns, suiteql mainline taxline, netsuite transactionline foreignamount --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json