Connect SuiteQL to Prowork

​

SuiteQL vs. Saved Searches

• Certain transaction types aren’t available via Saved Search, including cash sales, cash refunds, credit memos, and card charges and refunds. SuiteQL is the only way to pull these through the API.
• Standard NetSuite reports aren’t Saved Searches. AR aging, income statements, and P&L-style reports can’t be pulled as Saved Searches, but you can reproduce them with SuiteQL.
• Custom joins, readable field values, and large datasets. If you need joins Saved Search doesn’t expose, custom field values resolved to readable names instead of internal IDs, or more rows than a Saved Search can return in one response, SuiteQL handles it.

​

Writing your query

​

Available data

• Transactions: sales orders, purchase orders, invoices, vendor bills, credit memos, cash sales, cash refunds, item fulfillments, item receipts, journal entries, and return authorizations. Filter by date, status, type, subsidiary, or any other field.
• Transaction lines: line-level details for any transaction including items, quantities, amounts, GL accounts, and custom segments.
• Customers and leads: account details, billing addresses, payment terms, sales rep assignments, transaction history, and overdue balances.
• Vendors: supplier records including contact details, payment terms, currency, and associated purchase history.
• Items and inventory: product catalog with SKUs, pricing, inventory levels by location and bin, reorder points, preferred vendors, matrix items, item groups, and assembly components.
• Employees: employee records with roles, permissions, department, subsidiary, and login history.
• Accounts (Chart of Accounts): GL account definitions, types, subsidiary mappings, and balances.
• Subsidiaries, locations, and bins: multi-entity and multi-location structure for filtering and grouping cross-subsidiary reports.
• Support cases: open and closed case details including assignee, status, and customer.
• Custom records, fields, and segments: any custom objects configured in your NetSuite instance.

​

Common use cases

• Pull transaction types Saved Search can’t return: Query cash sales, cash refunds, credit memos, and card charges and refunds to power finance reconciliations against payment processor or card transaction data.
• Replicate standard NetSuite reports: Build AR aging, AP aging, income statement, and P&L-style reports as SuiteQL queries, then drop the output into a spreadsheet, drive folder, or chat summary.
• Pull high-volume transaction-line data: When a month of sales order lines or GL transactions exceeds Saved Search row limits, SuiteQL returns the full set by line, so finance can post revenue and costs without missing rows.
• Resolve internal IDs to readable names on custom fields: Pulling Data via Saved Search already resolves IDs for standard fields via joined fields. For custom list and custom record fields where Saved Search returns the internal ID, SuiteQL can join to the underlying table and return the display name directly.
• Inventory reporting by lot, bin, or location: Pull inventory balances joined with item descriptions, UPCs, pack sizes, and lot/expiration dates to reconcile against 3PL counts.
• Purchase order and fulfillment analysis: Query open POs with line-level detail, expected ship dates, receiving status, and custom fields to track supplier performance and inbound inventory.
• Assembly items and BOM queries: Pull parent items with their component children, costs, and quantities for manufacturing and kit management workflows.
• Push to your warehouse: Send the raw query output into a data warehouse so analytics teams can join NetSuite with the rest of the business.

​

Tips for using Prowork with SuiteQL

• Filter and aggregate in SuiteQL first. Do filtering, aggregation, and joins in the SQL query before it reaches Prowork. This reduces data volume, speeds up your flow, and avoids hitting API limits.
• Use the Prowork chat to write queries. If you’re new to SuiteQL, describe what you want in the Prowork chat on the left of your canvas and let it draft the query for you.
• Format your query as a single line. The Pull from SuiteQL step requires the entire query on one line. Flatten multi-line queries before pasting.
• Use BUILTIN.RELATIVE_RANGES for rolling windows. Instead of calculating dates in Prowork, use NetSuite’s built-in function (e.g., BUILTIN.RELATIVE_RANGES('DAGO90','START') for “90 days ago”) inside the query.
• Pull incrementally. Use WHERE lastmodifieddate >= SYSDATE - 1 or similar to pull only recently updated records instead of reprocessing all history on every run.
• Match cadence to use case. Nightly for financial reconciliation after transactions close, daily for ops dashboards, hourly for time-sensitive inventory or order monitoring.
• Document your queries with cards. Add a card on the canvas explaining what the query does, what business logic is applied, and which teams rely on the output.
• Test in sandbox first. Validate queries against your sandbox account (using the -sb1 Account ID suffix) before pointing the flow at production.

​

Sample query to copy

SELECT t.trandate AS TranDate, t.memo AS Memo, t.tranid AS TranID, t.type AS TranType, tl.ForeignAmount AS TotalAmount, a.acctnumber AS AccountNumber, a.acctname AS AccountName, v.entityid AS VendorName, s.name AS Subsidiary, d.name AS DepartmentName FROM transaction t LEFT JOIN transactionLine tl ON tl.transaction = t.id LEFT JOIN department d ON d.id = tl.department LEFT JOIN account a ON a.id = tl.account LEFT JOIN entity v ON v.id = t.entity LEFT JOIN subsidiary s ON s.id = t.subsidiary WHERE t.trandate >= BUILTIN.RELATIVE_RANGES('DAGO90','START') AND t.type IN ('CardChrg', 'CardRfnd', 'CustCred');

​

FAQ

​

When should I use SuiteQL instead of a Saved Search?

​

Can I push data back into NetSuite with this step?

​

Why is my query failing with a syntax error?

​

How do I connect to a NetSuite sandbox?