Apideck Help Center
Requests
Advice and answers from the Apideck Team
In this article
1. Overview:2. How It Works:3. Endpoint example:3. SQL Query Best Practices4. Incremental Syncing Pattern5. Resources
Home
Apideck Unify

Using pass_through parameters for NetSuite Search requests

Edited

1. Overview:

NetSuite supports custom SQL 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

2. How It Works:

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 expiration)

  • Executes your custom SQL/SuiteQL query directly

  • Returns unified response format


3. Endpoint example:

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

Important: Always URL-encode the query parameter value.

Example 1: Filter Invoices by Created Date

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

Request URL:

GET https://unify.apideck.com/accounting/invoices?pass_through[q]=SELECT * FROM transaction WHERE type = 'CustInvc' AND createdDate >= '1/15/2024' ORDER BY createdDate ASC&limit=200

URL-encoded (for actual use):

GET https://unify.apideck.com/accounting/invoices?pass_through%5Bq%5D=SELECT%20%2A%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)

Example 2: Filter Customers by Email

Search for customers with a specific email domain:

Request URL:

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

URL-encoded:

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

Example 3: Filter Bills by Vendor and Date

Get bills from a specific vendor created after a date:

Request URL:

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

URL-encoded:

GET https://unify.apideck.com/accounting/bills?pass_through%5Bq%5D=SELECT%20MAIN.%2A%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&limit=200

Example 4: Filter Purchase Orders by Date and Vendor

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, like filtering by created_at.

Request URL:

GET https://unify.apideck.com/accounting/purchase-orders?pass_through[q]=SELECT MAIN.*, 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.%2A%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

Example 5: Filter Journal Entries by Date Range

Get journal entries created within a date range:

Request URL:

GET https://unify.apideck.com/accounting/journal-entries?pass_through[q]=SELECT MAIN.* 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%20MAIN.%2A%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

Example 6: Filter Customers by Status and Last Modified

Get active customers modified in the last 30 days:

Request URL:

GET https://unify.apideck.com/accounting/customers?pass_through[q]=SELECT MAIN.* 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%20MAIN.%2A%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 the date 30 days ago in M/D/YYYY format

Example 7: Filter Projects by Date Range

Get projects created within a specific period:

Request URL:

GET https://unify.apideck.com/accounting/projects?pass_through[q]=SELECT MAIN.* 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%20MAIN.%2A%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

3. SQL Query Best Practices

  • Always Include ORDER BY for consistent results and pagination:

sql

SELECT * FROM transaction 

WHERE type = 'CustInvc' 

ORDER BY createdDate ASC

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

  • Escape Special Characters - When building queries dynamically, escape special characters:

- Single quotes: ' → ''

- Remove semicolons: ; → (remove)

- Remove SQL comments: -- → (remove)

  • Use MAIN.* for Base Tables

When joining tables, use MAIN.* to select all fields from the main table:

sql

SELECT MAIN.*, V.altname as vendor_name 

FROM transaction MAIN, vendor V 

WHERE ...

4. Incremental Syncing Pattern

Use pass_through[q] with date filtering to avoid cursor expiration:

Initial Request:

GET https://unify.apideck.com/accounting/invoices?pass_through[q]=SELECT * FROM transaction WHERE type = 'CustInvc' AND createdDate >= '1/15/2024' ORDER BY createdDate ASC&limit=200

After Processing:

1. Extract the latest created_at from the response

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

3. Use in next request:

Next Request (with updated date):

GET https://unify.apideck.com/accounting/invoices?pass_through[q]=SELECT * FROM transaction WHERE type = 'CustInvc' AND createdDate >= '2/20/2024' ORDER BY createdDate ASC&limit=200

5. Resources

- [SuiteQL Overview](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_156257770590.html) - Introduction to SuiteQL and its benefits

- [Using SuiteQL](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_156257799794.html) - How to use SuiteQL in NetSuite

- [SuiteQL Syntax and Examples](https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_156257790831.html) - 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.

Was this article helpful?

Sorry about that! Care to tell us more?

Thanks for the feedback!

There was an issue submitting your feedback
Please check your connection and try again.