SuiteQL Gotchas

SuiteQL has some quirks that differ from standard SQL. Here's what to watch out for.

REST-Only Fields

Not all fields from the REST metadata catalog are queryable via SuiteQL. Fields like balance, total, consolBalance, and other computed/summary fields exist in the catalog but will return a 400 Bad Request if used in a SuiteQL query.

-- This will FAIL with "Unknown identifier 'balance'"
SELECT id, balance FROM customer

-- Use the transaction table instead
SELECT entity, SUM(foreigntotal) AS balance
FROM transaction
WHERE type = 'CustInvc'
GROUP BY entity

Boolean Fields

NetSuite stores booleans as 'T' (true) and 'F' (false) strings in SuiteQL. SuitePortal handles this automatically:

// This works — SuitePortal converts false to 'F'
where: { isinactive: false }
// → WHERE isinactive = 'F'

// Raw SuiteQL equivalent
"SELECT id FROM customer WHERE isinactive = 'F'"

Transaction Types

Transactions (sales orders, invoices, etc.) live in a single transaction table. Use the type field to filter:

SELECT id, tranid, entity FROM transaction WHERE type = 'SalesOrd'

Field Name Casing

SuiteQL is case-insensitive for field names. Both companyName and companyname work. However, the response always returns lowercase field names regardless of what you use in your query.

NULL Handling

SuiteQL follows standard SQL null semantics:

-- This will NOT match rows where email is NULL
SELECT id FROM customer WHERE email != 'test@example.com'

-- To include NULLs, be explicit
SELECT id FROM customer
WHERE email != 'test@example.com' OR email IS NULL

Date Formats

SuiteQL returns dates as strings. The format depends on your NetSuite account's date format settings. Common formats:

• 1/15/2025 (US format)
• 2025-01-15 (ISO format)

When filtering by date in SuiteQL:

SELECT id, trandate FROM transaction
WHERE trandate >= '1/1/2025' AND trandate <= '1/31/2025'

Query Size Limits

• Maximum 1000 rows per SuiteQL request (use pagination for more)
• Query string length has a practical limit — extremely long IN clauses may fail
• Concurrent requests are rate-limited by NetSuite (SuitePortal handles this with built-in rate limiting)

The links Field

SuiteQL responses include a links array on every row (used for HATEOAS navigation). SuitePortal returns these as-is. You can strip them in your code:

const customers = await ns.customer.findMany({ ... });
const clean = customers.map(({ links, ...rest }) => rest);