NetSuiteFree generatorPDF, Excel, Word exportNetSuite

NetSuite SuiteQL Transaction Query Generator

SuiteQL lets you query NetSuite with SQL-style syntax from the SuiteQL workbook, REST or SuiteScript N/query and N/suiteql modules. This generator builds the header query, the line-level query and the REST call so you do not have to remember the date and status syntax.

Fill in

Transaction type code

The value stored in transaction.type, not the record id.

From date

To date

Row limit

SuiteQL returns at most 5,000 rows per page through REST.

Account ID (REST host)

Generated code

Download:

Transaction headers

SELECT
  t.id,
  t.tranid,
  t.trandate,
  t.type,
  BUILTIN.DF(t.status) AS status_name,
  BUILTIN.DF(t.entity) AS entity_name
FROM
  transaction t
WHERE
  t.type = 'WorkOrd'
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
  AND t.trandate <= TO_DATE('2026-12-31', 'YYYY-MM-DD')
ORDER BY
  t.trandate DESC
FETCH FIRST 500 ROWS ONLY

Transaction lines with item

SELECT
  t.tranid,
  tl.id AS line_id,
  BUILTIN.DF(tl.item) AS item_name,
  tl.quantity,
  tl.memo
FROM
  transaction t
  INNER JOIN transactionline tl ON tl.transaction = t.id
WHERE
  t.type = 'WorkOrd'
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
  AND t.trandate <= TO_DATE('2026-12-31', 'YYYY-MM-DD')
  AND tl.mainline = 'F'
  AND tl.item IS NOT NULL
ORDER BY
  t.tranid, tl.id
FETCH FIRST 500 ROWS ONLY

Run it over REST

-- POST https://1234567.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=0
-- Header: Prefer: transient
-- Header: Content-Type: application/json
-- Header: Authorization: OAuth 2.0 bearer token or OAuth 1.0 TBA signature
-- Body: {"q": "<paste one of the queries above on a single line>"}
-- The response contains items, hasMore, offset and count. Loop while hasMore is true.

Check the type code in your account: transaction.type holds the short code (for example WorkOrd), which differs from the record id (workorder). Column availability on transactionline can vary by account features, so run a small row limit first. SuiteQL is read-only.

Want NetSuite answers without writing the query?

Ask for a short review of how a read-only natural language layer over NetSuite could work for your reporting team.

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

How to use the generator

Choose the transaction type code, set the date range and row limit, then copy the block you need. Paste a query into the SuiteQL tool in the Analytics area, or send it through the REST endpoint shown in the third block. The account ID field only fills the REST host name so you can copy the line straight into Postman or a script.

Use the header query for counts, status rollups and exception lists
Use the line query when you need items and quantities
Start with a row limit of 50 to confirm the columns exist
Sandbox account IDs use a suffix such as -sb1 in the host name

Why BUILTIN.DF and TO_DATE appear

Fields such as status, entity and item hold internal ids or codes. BUILTIN.DF returns the display value, which is what a human expects to read. Dates are compared with TO_DATE and an explicit format so the query does not depend on the user date preference. Without these two habits, a SuiteQL result often looks like a column of unexplained numbers and the same query returns different results for different users.

Header versus line queries

The transaction table holds one row per document. The transactionline table holds one row per line plus a main line row where mainline is T. Filtering mainline = 'F' removes that header row so quantities are not double counted. For work orders the lines hold components and the assembly item, so always check the item and quantity meaning before summing.

Common mistakes

People often filter on the wrong type value, forget the pagination limit, or expect SuiteQL to write data. It does not. Another frequent surprise is that role permissions apply: a query run by a restricted role returns only what that role may see, so test with the same role the integration will use.

Filtering t.type with the record id instead of the short code
Ignoring hasMore and loading only the first page
Summing transactionline rows without excluding the main line
Testing as administrator and deploying under a limited role

Frequently Asked Questions

Is SuiteQL the same as saved search?

No. Saved searches use the search engine and its own filter syntax, while SuiteQL uses SQL-style statements over the analytics data source. SuiteQL supports joins and aggregations more naturally, but record and field coverage differs, so some searches cannot be copied across one to one.

Can SuiteQL update records?

No. SuiteQL is read-only. To change data you use the REST record API, SuiteScript N/record or CSV import. Use SuiteQL to find the records, then update them through a controlled path that logs the change.

Why does my query return fewer rows than expected?

Common causes are the row limit, REST pagination, the date filter, or the role permissions of the user or token running the query. Run the same statement in the workbook as an administrator to compare counts and isolate the cause.

Does this tool send my data anywhere?

No. The query is assembled in your browser from the values you type. Nothing is executed against NetSuite by this page, and you run the generated text in your own account.

If your team wants to ask these questions in plain English and see the generated query for review, ERPray can sit on top of NetSuite read-only and respect your roles.

Talk to an Expert Explore More Free Tools

Related Tools

NetSuite

More NetSuite tools

See all

Back to All Tools