NetSuite: Using pass_through parameters for Search requests

NetSuite supports custom SuiteQL queries through the pass_through[q] parameter, allowing you to build advanced queries with full control over filtering, sorting, and data selection. This is particularly useful for:

• Custom date filtering (e.g., filtering by created_at when not natively supported by Apideck)

• Complex filtering beyond standard filter parameters

• Avoiding cursor expiration (uses offset-based pagination instead of searchId-based)

• Joining related data in a single query

• Custom field selection for optimized queries

When you include pass_through[q] in your request, the NetSuite connector:

• Routes the request to NetSuite's REST API (instead of SOAP)

• Uses offset-based pagination (no 15-minute cursor expiration)

• Executes your custom SuiteQL query directly

• Returns a unified response format

Do not use SELECT * on the transaction table or other large tables.

NetSuite's transaction table has hundreds of virtual columns. Using SELECT * forces a full column evaluation on every row regardless of your WHERE filters or row limits - this will cause timeouts on accounts with significant data volume.

Always select explicit column names instead:

-- ❌ Will time out on large accounts
SELECT * FROM transaction WHERE type = 'CustInvc' AND createdDate >= '1/15/2024'

-- ✅ Fast — select only the columns you need
SELECT id, tranid, entity, trandate, duedate, status, currency, foreigntotal, memo
FROM transaction WHERE type = 'CustInvc' AND createdDate >= '1/15/2024'

When joining tables, you can use MAIN.* to select all fields from the main (non-transaction) table only - this is safer than a bare SELECT * but should still be avoided on the transaction table:

-- ✅ Acceptable for smaller tables like customer, vendor
SELECT MAIN.* FROM customer MAIN WHERE isinactive = 'F'

-- ❌ Avoid MAIN.* when MAIN is the transaction table
SELECT MAIN.* FROM transaction MAIN WHERE ...

GET https://unify.apideck.com/accounting/{resource}?pass_through[q]=<your-query>&limit=200

Important: Always URL-encode the query parameter value.

Filter invoices created after a specific date to enable incremental syncing.

Request URL:

GET https://unify.apideck.com/accounting/invoices?pass_through[q]=SELECT id, tranid, entity, trandate, duedate, status, currency, foreigntotal, memo FROM transaction WHERE type = 'CustInvc' AND createdDate >= '1/15/2024' ORDER BY createdDate ASC&limit=200

URL-encoded:

GET https://unify.apideck.com/accounting/invoices?pass_through%5Bq%5D=SELECT%20id%2C%20tranid%2C%20entity%2C%20trandate%2C%20duedate%2C%20status%2C%20currency%2C%20foreigntotal%2C%20memo%20FROM%20transaction%20WHERE%20type%20%3D%20%27CustInvc%27%20AND%20createdDate%20%3E%3D%20%271%2F15%2F2024%27%20ORDER%20BY%20createdDate%20ASC&limit=200

Date Format: Use NetSuite format M/D/YYYY (e.g., 1/15/2024, not ISO format like 2024-01-15)

Search for customers with a specific email domain.

Request URL:

GET https://unify.apideck.com/accounting/customers?pass_through[q]=SELECT id, entityid, email, companyname, phone FROM customer MAIN WHERE email LIKE '%@example.com'&limit=200

URL-encoded:

GET https://unify.apideck.com/accounting/customers?pass_through%5Bq%5D=SELECT%20id%2C%20entityid%2C%20email%2C%20companyname%2C%20phone%20FROM%20customer%20MAIN%20WHERE%20email%20LIKE%20%27%25%40example.com%27&limit=200

Get bills from a specific vendor created after a date.

Request URL:

GET https://unify.apideck.com/accounting/bills?pass_through[q]=SELECT MAIN.id, MAIN.tranid, MAIN.entity, MAIN.trandate, MAIN.duedate, MAIN.status, MAIN.currency, MAIN.foreigntotal, V.altname as vendor_name FROM transaction MAIN, vendor V WHERE recordtype='vendorbill' AND MAIN.entity = V.id AND createdDate >= '1/1/2024' ORDER BY createdDate ASC&limit=200

URL-encoded:

GET https://unify.apideck.com/accounting/bills?pass_through%5Bq%5D=SELECT%20MAIN.id%2C%20MAIN.tranid%2C%20MAIN.entity%2C%20MAIN.trandate%2C%20MAIN.duedate%2C%20MAIN.status%2C%20MAIN.currency%2C%20MAIN.foreigntotal%2C%20V.altname%20as%20vendor_name%20FROM%20transaction%20MAIN%2C%20vendor%20V%20WHERE%20recordtype%3D%27vendorbill%27%20AND%20MAIN.entity%20%3D%20V.id%20AND%20createdDate%20%3E%3D%20%271%2F1%2F2024%27%20ORDER%20BY%20createdDate%20ASC&limit=200

Use case: Purchase orders support filter[updated_since] and sort[created_at] / sort[updated_at] via standard parameters. Use pass_through[q] when you need filtering that isn't supported natively, like filtering by created_at.

Request URL:

GET https://unify.apideck.com/accounting/purchase-orders?pass_through[q]=SELECT MAIN.id, MAIN.tranid, MAIN.entity, MAIN.trandate, MAIN.status, MAIN.currency, MAIN.foreigntotal, V.altname as vendor_name FROM transaction MAIN, vendor V WHERE recordtype='purchaseorder' AND MAIN.entity = V.id AND createdDate >= '1/15/2024' ORDER BY createdDate ASC&limit=200

URL-encoded:

GET https://unify.apideck.com/accounting/purchase-orders?pass_through%5Bq%5D=SELECT%20MAIN.id%2C%20MAIN.tranid%2C%20MAIN.entity%2C%20MAIN.trandate%2C%20MAIN.status%2C%20MAIN.currency%2C%20MAIN.foreigntotal%2C%20V.altname%20as%20vendor_name%20FROM%20transaction%20MAIN%2C%20vendor%20V%20WHERE%20recordtype%3D%27purchaseorder%27%20AND%20MAIN.entity%20%3D%20V.id%20AND%20createdDate%20%3E%3D%20%271%2F15%2F2024%27%20ORDER%20BY%20createdDate%20ASC&limit=200

Get journal entries created within a date range.

Request URL:

GET https://unify.apideck.com/accounting/journal-entries?pass_through[q]=SELECT id, tranid, trandate, memo, currency FROM transaction MAIN WHERE recordtype='journalentry' AND createdDate >= '1/1/2024' AND createdDate <= '12/31/2024' ORDER BY createdDate ASC&limit=200

URL-encoded:

GET https://unify.apideck.com/accounting/journal-entries?pass_through%5Bq%5D=SELECT%20id%2C%20tranid%2C%20trandate%2C%20memo%2C%20currency%20FROM%20transaction%20MAIN%20WHERE%20recordtype%3D%27journalentry%27%20AND%20createdDate%20%3E%3D%20%271%2F1%2F2024%27%20AND%20createdDate%20%3C%3D%20%2712%2F31%2F2024%27%20ORDER%20BY%20createdDate%20ASC&limit=200

Get active customers modified after a specific date.

Request URL:

GET https://unify.apideck.com/accounting/customers?pass_through[q]=SELECT id, entityid, email, companyname, phone, lastmodifieddate FROM customer MAIN WHERE isinactive = 'F' AND lastmodifieddate >= '1/1/2024'&limit=200

URL-encoded:

GET https://unify.apideck.com/accounting/customers?pass_through%5Bq%5D=SELECT%20id%2C%20entityid%2C%20email%2C%20companyname%2C%20phone%2C%20lastmodifieddate%20FROM%20customer%20MAIN%20WHERE%20isinactive%20%3D%20%27F%27%20AND%20lastmodifieddate%20%3E%3D%20%271%2F1%2F2024%27&limit=200

Note: Replace '1/1/2024' with your target date in M/D/YYYY format.

Get projects created within a specific period.

Request URL:

GET https://unify.apideck.com/accounting/projects?pass_through[q]=SELECT id, entityid, datecreated, status FROM job MAIN WHERE datecreated >= '1/1/2024' AND datecreated <= '3/31/2024' ORDER BY datecreated ASC&limit=200

URL-encoded:

GET https://unify.apideck.com/accounting/projects?pass_through%5Bq%5D=SELECT%20id%2C%20entityid%2C%20datecreated%2C%20status%20FROM%20job%20MAIN%20WHERE%20datecreated%20%3E%3D%20%271%2F1%2F2024%27%20AND%20datecreated%20%3C%3D%20%273%2F31%2F2024%27%20ORDER%20BY%20datecreated%20ASC&limit=200

NetSuite's transaction table has hundreds of virtual columns. SELECT * forces a full column evaluation on every row regardless of WHERE filters or row limits — this causes timeouts on accounts with significant data volume. Always use explicit column names.

Without ORDER BY, page results are non-deterministic and records may be duplicated or skipped across pages.

SELECT id, tranid, trandate, status
FROM transaction
WHERE type = 'CustInvc'
ORDER BY createdDate ASC

NetSuite requires dates in M/D/YYYY format. Do not use ISO format.

-- ✅ Correct
createdDate >= '1/15/2024'

-- ❌ Wrong — will not work
createdDate >= '2024-01-15'

When joining tables, prefix columns with MAIN. (or the relevant alias) to avoid ambiguity and only pull fields from the intended table.

SELECT MAIN.id, MAIN.trandate, V.altname as vendor_name
FROM transaction MAIN, vendor V
WHERE MAIN.entity = V.id AND recordtype = 'vendorbill'

Use pass_through[q] with date filtering to build reliable incremental syncs and avoid cursor expiration.

Initial request:

GET https://unify.apideck.com/accounting/invoices?pass_through[q]=SELECT id, tranid, entity, trandate, duedate, status, currency, foreigntotal, memo FROM transaction WHERE type = 'CustInvc' AND createdDate >= '1/15/2024' ORDER BY createdDate ASC&limit=200

After processing each page:

1. Extract the latest created_at from the response

2. Convert to NetSuite date format (M/D/YYYY)

3. Use as the createdDate >= value in the next sync run

Next sync request:

GET https://unify.apideck.com/accounting/invoices?pass_through[q]=SELECT id, tranid, entity, trandate, duedate, status, currency, foreigntotal, memo FROM transaction WHERE type = 'CustInvc' AND createdDate >= '2/20/2024' ORDER BY createdDate ASC&limit=200

• Introduction to SuiteQL and its benefits

• How to use SuiteQL in NetSuite

• Syntax guidelines and practical examples

Note: This feature is specific to NetSuite. Other connectors may have different pass_through implementations or may not support it at all.