NetSuite Lookup

Finds customers, vendors, items, locations, transactions or any record in Oracle NetSuite by field value, live via SuiteQL.

Looks up records in the workspace’s own NetSuite account by matching one or more columns and returns the matching rows with their NetSuite internal ids. Display name “NetSuite Lookup”. Off by default.

The tool is generic: it works for every record NetSuite exposes to SuiteQL (customer, vendor, item, location, subsidiary, department, classification, employee, transaction, customrecord_*). Add it to an agent once per lookup you need, with a display name that says what it finds — find_customer, find_item, find_sales_order_by_po — and pin the record and columns on each configuration so the agent only supplies the values.

Pass every value from a document in one call (search_values): a purchase order with 100 lines is one call, not 100. Each call makes one live request to NetSuite per matching stage (at most five, each only over the values the earlier stages missed); only a very large batch against many search columns is split into several statements so no single SuiteQL statement grows past a fixed size.

Authentication and enablement

Configured tool. Add a NetSuite integration under Settings → Integrations with the four token-based-auth values from your NetSuite account:

• Account ID — Setup → Company → Company Information (e.g. 1234567 or 1234567_SB1 for a sandbox).
• Consumer Key / Client ID and Consumer Secret / Client Secret — from the Integration record (Setup → Integration → Manage Integrations) with Token-Based Authentication enabled. Every NetSuite account has its own integration records, so these are per workspace.
• Token ID and Token Secret — an Access Token (Setup → Users/Roles → Access Tokens) issued for that integration record, a user and a role with REST Web Services and SuiteAnalytics Workbook permissions.

Saving the integration makes a signed test call; a 401 means the token pair was not issued for the integration record whose consumer pair you entered. Bind the integration on the configured tool — the credential fields collapse into one integration picker.

Inputs

• record_type — SuiteQL table to search, e.g. customer, item, transaction, customrecord_warehouse. Letters, digits and underscores only. Usually pinned per configuration.
• search_fields — columns to match against, in priority order (max 10). A value matching an earlier column ranks higher, so ["itemid", "displayname"] prefers the SKU column over the name. Accepts a list or a comma-separated string.
• search_values — the values to look for (max 200 per call). One matches entry or one unmatched_values entry comes back per value, in order. Duplicates are searched once.
• match_mode — auto (default): exact, then contains, then keywords, each stage only for the values still unmatched. Always case-insensitive.
  • exact: the cell equals the value; then, for values containing a digit that the plain comparison missed, equals it once -, space, _, . and / are removed on both sides (LUN3002 = LUN-3002 = lun 3002). The plain comparison is one index-assisted IN list; the separator-insensitive one is a second, smaller statement that scans, so it only runs when it has something to find. Use for SKUs, PO numbers, ids.
  • contains: the value appears anywhere in the cell (names); then, for values containing a digit that this missed, the value embeds the cell’s code with the same separators removed (SOLUNA_LUN-1905 finds LUN-1905; cells shorter than 4 characters after normalising are ignored) — again a separate statement over the misses only.
  • keywords: at least 60% of the value’s words (min 2, stop words dropped, letter/digit runs split so LUN3002 reads as LUN 3002 and 48Vdc as 48 Vdc) appear across all search fields together, in any order — for description-only lines, e.g. Soluna Data Logger, Low Voltage, LUN3002 finds itemid LUN-3002 / display name Data logger LV. Numbers never trigger a search on their own and only rank variants (3 String above 1 String).
• filters — extra AND conditions as [{"field", "value"}] (max 10), e.g. [{"field": "isinactive", "value": "F"}], or [{"field": "type", "value": "SalesOrd"}] when record_type is transaction. Also accepts "isinactive=F, type=SalesOrd".
• return_columns — extra columns to include with each match (max 30). id and the search fields are always included. Only columns of that record’s SuiteQL table are valid — item has itemid, displayname, itemtype, isinactive, but not a price (prices live in the pricing table); an unknown column fails the call with NetSuite’s Unknown identifier message.
• limit — candidates kept per search value, 1–50 (default 5). The best is the match; the rest are additional_candidates.

Typical configurations

Display name	record_type	search_fields	filters	return_columns	notes
find_customer	customer	companyname, entityid	isinactive=F	subsidiary, currency
find_item_by_code	item	itemid, displayname	isinactive=F	itemtype	match_mode: contains — precise for SKUs, no keyword guessing
find_item	item	itemid, displayname, description, purchasedescription	isinactive=F	itemtype	auto — for description-only lines
find_location	location	name, fullname	isinactive=F	—
find_sales_order_by_po	transaction	otherrefnum	type=SalesOrd	tranid, trandate, status

Output

Structured content is the flat result (no envelope):

• matches[] — one per matched search value: search_value, values (object keyed by column: id, the search fields, return_columns; plain cells), matched_values (each column that supports the match → its cell), match_identifier (the NetSuite internal id — use it as the id in later steps), match_type (exact, contains or keywords), confidence_score (1.0 for exact, 0.98 for separator-insensitive exact; 0.5–0.95 for contains, a whole-word hit at least 0.8, an embedded code 0.8; 0.5–0.9 for keywords by share of words found), ambiguous (true when another candidate scored within 0.01 of the best — a tie inside one tier: two customers with the same name, several models of one product whose descriptions share every word; never across tiers, so a whole-cell hit next to a separator-insensitive hit is not a tie; decided before limit trims candidates, so limit: 1 still reports it; the match and every candidate are then capped at 0.6 and the match must not be treated as resolved), and additional_candidates[] (match, match_identifier, confidence_score, product = the full row) for a reviewer to pick from.
• unmatched_values[] — search values with no row. Not an error.
• match_count, row_count, truncated, record_type, search_fields, search_values, match_mode, return_columns, completed_at.

The markdown summary is a table of search value → matched column and cell → internal id → type → score → number of other candidates, followed by the unmatched values.

Limits and side effects

• Read-only. One SuiteQL request per stage; at most five stages in auto (exact, exact with separators removed, contains, embedded code, keywords), each only over the values still unmatched, so a document whose codes all resolve exactly costs one request. Measured on a 2,640-item master with four search columns and 200 codes that match nothing (every stage runs over every value): plain exact 0.8–1.9 s, separator-insensitive exact about 3 s, contains about 2.5 s, embedded code about 9 s — the one to size batches by on a much larger item master, since it scans and costs about 3 s per 75 values; a real 16-line purchase order runs all five stages in under 4 s. Each stage’s time is logged at debug level. Statements are capped at 300 match clauses and 60 KB, so a large batch against many columns runs as several statements per stage rather than one oversized one.
• Up to 1,000 rows per pass are read; truncated: true means more rows matched — narrow with filters or a more specific value.
• Every table and column name is validated as a plain identifier and every value is quoted; there is no free-text SQL.
• Logs record the record type, fields, mode, counts and timing — never search values or rows.

Expected errors

• NetSuite integration not configured… — no integration bound, or a credential field is empty.
• NetSuite account id "…" is not valid — the account id contains characters that cannot form a NetSuite hostname.
• validation error: … — a missing required field, an invalid identifier, an unknown match_mode, or a cap exceeded (values, fields, filters, columns, limit).
• NetSuite lookup on <record> failed: SuiteQL returned HTTP 400: … — NetSuite rejected the query, usually an unknown column or table for that record, or a role without SuiteQL access. The NetSuite detail is included; the query is not.

Configuration and validation errors, and any NetSuite 4xx, are returned as limitations (status: "limitation" in the structured content): the platform hands them straight back to the agent instead of retrying a call that cannot succeed. Network failures and NetSuite 5xx responses stay retryable.