NetSuite SuiteQL Queries

Overview

VersionForge extracts data from NetSuite using SuiteQL, a SQL-like query language that runs against NetSuite's analytics data source. SuiteQL provides direct access to transaction lines, account records, subsidiary hierarchies, and other financial objects without requiring Saved Searches or RESTlets.

Every extraction call hits the SuiteQL REST endpoint at https://{accountId}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql, sending a POST request with a SQL query body and receiving paginated JSON results.

Default Query Templates

VersionForge ships with built-in query templates for each supported object type. These templates handle field selection, joins, filtering, and pagination automatically.

Dimension Queries

Dimension queries extract reference data -- accounts, subsidiaries, departments, locations, currencies, and accounting periods. These are typically small result sets that VersionForge snapshots in full on each sync.

-- Account dimension query
SELECT id, acctnumber, acctname, accttype, generalrate,
       parent, description, isinactive, lastmodifieddate
FROM account
ORDER BY id
LIMIT 1000 OFFSET 0

-- Subsidiary hierarchy query
SELECT id, name, parent, currency, iselimination,
       isinactive, country, lastmodifieddate
FROM subsidiary
ORDER BY id
LIMIT 1000 OFFSET 0

The full set of dimension object types includes: account, subsidiary, department, location, currency, and accounting-period.

Transaction Query

The transaction query is the primary high-volume extraction target. It joins the transaction header table with transactionline to produce one row per GL line:

SELECT t.id, t.tranid, t.trandate, t.type, t.status,
       t.postingperiod, t.currency, t.exchangerate,
       t.lastmodifieddate,
       tl.id AS line_id, tl.account, tl.debitamount,
       tl.creditamount, tl.amount, tl.subsidiary,
       tl.department, tl.location, tl.class, tl.memo
FROM transaction t
JOIN transactionline tl ON t.id = tl.transaction
WHERE tl.subsidiary = '3'
  AND t.lastmodifieddate > '2026-01-01'
ORDER BY t.id, tl.id
LIMIT 1000 OFFSET 0

Pagination and Rate Limiting

SuiteQL results are paginated with a default page size of 1,000 rows. VersionForge automatically handles pagination by incrementing the OFFSET value until the API response indicates no more results (hasMore: false).

The SuiteQL REST API response includes these pagination fields:

{
  "items": [...],
  "count": 1000,
  "hasMore": true,
  "offset": 0,
  "totalResults": 45230
}

VersionForge respects NetSuite's REST API rate limits. If the API returns HTTP 429, the connector waits for the period indicated by the Retry-After header before resuming.

Custom Query Overrides

For advanced use cases, you can override the default query template for any object type. Custom queries must return the same column names that VersionForge expects for that object type.

Define custom queries in your sync profile configuration:

{
  "objectType": "transaction",
  "customQuery": "SELECT t.id, t.tranid, t.trandate, t.type, t.status, t.postingperiod, t.currency, t.exchangerate, t.lastmodifieddate, tl.id AS line_id, tl.account, tl.debitamount, tl.creditamount, tl.amount, tl.subsidiary, tl.department, tl.location, tl.class, tl.memo FROM transaction t JOIN transactionline tl ON t.id = tl.transaction WHERE t.type IN ('Journal', 'VendBill') AND tl.subsidiary = '3' ORDER BY t.id, tl.id"
}

Saved Search Fallback

If your NetSuite account does not have SuiteQL enabled, or you need to extract data from a custom record type not covered by the built-in SuiteQL templates, VersionForge can fall back to Saved Searches via the RESTlet API. This path is slower and requires deploying a custom RESTlet script in your NetSuite account. Contact your implementation team for the RESTlet bundle.

Completeness Validation

For transaction extractions, VersionForge runs a parallel count query to verify that the number of extracted rows matches the expected total:

SELECT COUNT(*) AS cnt
FROM transaction t
JOIN transactionline tl ON t.id = tl.transaction
WHERE tl.subsidiary = '3'

If the extracted row count deviates from the count query result, the extraction is flagged with a completeness warning. This catches issues like mid-extraction data changes or pagination drift.