NetSuiteFree referencePDF, Excel, Word exportNetSuiteAll NetSuite tools

NetSuite SuiteQL Transaction and TransactionLine Columns Reference

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.

Download:

Table	Column	Data type	Meaning	Example value	Gotcha
transaction	id	integer	Internal ID of the transaction	48213	Same number space across all transaction types.
transaction	tranid	text	Document number users see	SO10432	Not unique across types; an invoice and an order can share a number.
transaction	trandisplayname	text	Type plus number as shown in lists	Sales Order #SO10432	For display only; do not parse it.
transaction	type	text	Transaction type code	SalesOrd	Codes are case sensitive: CustInvc, PurchOrd, VendBill, ItemShip.
transaction	recordtype	text	Record type id as used by SuiteScript	salesorder	Lowercase script id, different from type.
transaction	trandate	date	Transaction date	2026-10-14	Compare with TO_DATE('2026-10-01', 'YYYY-MM-DD'), not a bare string.
transaction	entity	reference	Customer, vendor or other entity on the header	1187	Points at different entity types per transaction type; use BUILTIN.DF(entity) for the name.
transaction	status	text	Status letter for this transaction type	B	Bare letter. BUILTIN.DF(status) gives the label, BUILTIN.CF(status) gives 'SalesOrd:B'.
transaction	postingperiod	reference	Accounting period	301	Join accountingperiod.id; empty on non-posting types such as sales orders.
transaction	posting	boolean T/F	Whether the transaction posts to the GL	T	Sales orders and estimates are 'F'.
transaction	currency	reference	Transaction currency	2	Join currency.id; symbol holds the ISO code.
transaction	exchangerate	currency	Rate from transaction currency to subsidiary base currency	1.0846	1 when transaction and subsidiary currency match.
transaction	foreigntotal	currency	Document total in transaction currency	12450.00	Header total; do not add it to line sums or you double count.
transaction	memo	text	Header memo	Rush order, ship complete	Line memo is a separate column on transactionline.
transaction	approvalstatus	reference	Approval status list value	2	Use BUILTIN.DF(approvalstatus) for Pending Approval, Approved or Rejected.
transaction	createddate	date	When the record was created	2026-10-14 09:12	Datetime, unlike trandate; TRUNC() it before comparing to a date.
transaction	lastmodifieddate	date	Last change timestamp	2026-10-15 16:40	Use for incremental extracts: lastmodifieddate > last run time.
transaction	voided	boolean T/F	Whether the transaction is voided	F	Filter voided = 'F' in financial reports.
transaction	duedate	date	Payment due date	2026-11-13	Filled on invoices and bills, empty on most other types.
transaction	employee	reference	Employee field on the header	57	Meaning varies by type; confirm in Records Catalog for yours.
transaction	terms	reference	Payment terms	3	Join term.id or use BUILTIN.DF(terms).
transaction	otherrefnum	text	Customer PO number or vendor reference	PO-77821	Free text; trim and upper-case before matching.
transaction	shipdate	date	Header ship date	2026-10-20	Line-level expected ship dates can differ.
transactionline	transaction	reference	Parent transaction id	48213	Join key to transaction.id.
transactionline	id	integer	Line id within the transaction	3	Unique only inside one transaction; the mainline row is usually id 0.
transactionline	uniquekey	integer	Line key unique across all transactions	915532	Use as a single-column line key in extracts.
transactionline	linesequencenumber	integer	Order of the line as displayed	2	Not the same as id; sort by this for print order.
transactionline	mainline	boolean T/F	Header summary row	T	Every transaction has one mainline = 'T' row holding header-level values.
transactionline	taxline	boolean T/F	Tax line	F	Exclude with taxline = 'F' when summing item amounts.
transactionline	item	reference	Item on the line	922	Empty on mainline and on many journal lines.
transactionline	itemtype	text	Type of the item on the line	InvtPart	Saves a join to item when you only need to filter by type.
transactionline	quantity	number	Line quantity in the line unit	-25	Negative on sales order lines; test on your account and use ABS() for display.
transactionline	rate	currency	Unit price	18.50	Can be empty for lines priced by amount only.
transactionline	netamount	currency	Line amount in subsidiary base currency	-462.50	Revenue lines carry a negative sign on sales transactions.
transactionline	foreignamount	currency	Line amount in transaction currency	-426.43	Same sign rule as netamount: negative for revenue on sales transactions.
transactionline	creditforeignamount	currency	Credit side of the line in transaction currency	426.43	Positive number; empty when the line is a debit.
transactionline	memo	text	Line memo or description	Bracket, zinc plated	Separate from the header memo.
transactionline	location	reference	Inventory location of the line	4	Can differ from the header location.
transactionline	subsidiary	reference	Subsidiary of the line	2	Read it here; the header subsidiary is on the mainline row.
transactionline	department	reference	Department segment	7	Line value can override the header.
transactionline	class	reference	Class segment	5	Joins to the classification table, not a table named class.
transactionline	entity	reference	Entity on the line	1187	Useful on journals where each line can name a different entity.
transactionline	createdfrom	reference	Source transaction this line was created from	48190	Points at one source; use the link tables when a line has several.
transactionline	isclosed	boolean T/F	Line closed	F	Filter isclosed = 'F' for open order reports.
transactionline	quantityshiprecv	number	Quantity fulfilled or received so far	10	Open quantity = ABS(quantity) - quantityshiprecv; check signs on a test order.
transactionline	quantitybilled	number	Quantity invoiced or billed so far	10	Compare with quantityshiprecv to find shipped-not-billed lines.
transactionline	quantitycommitted	number	Quantity committed from stock	15	Depends on commitment settings per line.
transactionline	isinventoryaffecting	boolean T/F	Line moves inventory	T	Use with fulfillments, receipts and adjustments for stock movement reports.
transactionline	iscogs	boolean T/F	Line is a COGS line	F	Fulfillments create COGS lines; exclude them when you want item lines only.
transactionline	expenseaccount	reference	Expense account on the line	112	For GL truth use transactionaccountingline.account.
transactionline	units	reference	Unit of measure used on the line	3	Needs Multiple Units of Measure; quantity is in this unit.

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.

Totals not matching the screen?

Send us the SuiteQL and one example transaction and we will point out the sign, mainline or join issue.

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

Was this tool useful?

How to use it

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.

Header facts: transaction
Item lines: transactionline with mainline = 'F' AND taxline = 'F'
GL accounts: transactionaccountingline, not transactionline

Worked example: open order quantity

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

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

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.

Frequently Asked Questions

Why is quantity negative in SuiteQL transactionline?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools