Item Lookup

Item Lookup Diagnosis

When to use this skill

Use when the user says any of:

• "my 850 failed with item lookup missing"
• "ITEM_LOOKUP_MISSING"
• "help me fix a failing PO"
• "I need to add an item mapping" / "add a partner-part-to-item lookup"
• "this 850 has an unknown SKU"
• "the connector can't find the item for X"
• "create an item lookup for ABC-123 → <NS item>"

If the user is asking about a different failure mode (a stuck 855, a packing-group issue, a field-mapping problem), do NOT load this skill — it's specific to inbound-850 item-lookup failures.

Non-native inbound types (875, etc.) do NOT resolve items through customrecord_orderful_item_lookup on the native path. Only 850, 860, 945, 944, 947 (NATIVE_SUPPORTED_INBOUND_TRX_TYPES) run the built-in item-lookup resolution this skill describes. Everything else — including the 875 (Grocery Products PO) — is processed entirely through the customer's JSONata mapping. Those mappings typically resolve items by UPC against the NetSuite item master ($lookupItems($upc, "upccode")) and do not read the item-lookup table unless the JSONata explicitly calls $lookupItemMappings. So for a failing 875/non-native transaction, editing (or adding rows to) the item-lookup table has no effect unless the mapping is wired to consult it — debug the JSONata (see writing-inbound-jsonata), not the lookup table. (The inactive-item caveat still applies either way: the connector's lookup joins filter item.isinactive != 'T', and NetSuite won't put an inactive item on a transaction — a single unresolvable line fails the whole PO.)

Prerequisites

This skill assumes the user has already run the netsuite-setup skill for the customer in question. That skill creates ~/orderful-onboarding/<customer-slug>/.env containing NS_SB_* / NS_PROD_* TBA credentials and ORDERFUL_API_KEY, plus an ENVIRONMENT=sandbox|production selector.

Before issuing any SuiteQL or Orderful API calls, confirm with the user which customer slug they're working on and load the matching .env. Use the patterns in samples/list-edi-customers.mjs (SuiteQL with TBA OAuth 1.0a signing) and skills/netsuite-setup/test-connections.mjs as references for executing requests. If no .env exists for the customer yet, route the user to /netsuite-setup first.

Inputs the skill needs

Ask up-front (do NOT proceed without these):

1. The failing transaction ID. Either:
   • The Orderful Transaction ID (UUID-shaped), or
   • The NetSuite internal ID of the customrecord_orderful_transaction row, or
   • A direct Orderful UI link (e.g. https://app.orderful.com/transactions/...)
2. The customer entity (NS internal ID or name) — sometimes implied by the transaction, sometimes the user knows it directly.

If the user has only "an 850 from <customer> is failing", ask them to find the specific failing transaction first (search customrecord_orderful_transaction by entity + status). Don't guess — multiple transactions may be failing for different reasons.

The recipe

Step 1 — Load failure context

From NetSuite, fetch the failing inbound transaction record + its error detail:

SELECT
  t.id                              AS ns_tran_id,
  t.custrecord_ord_tran_orderful_id AS orderful_tran_id,
  t.custrecord_ord_tran_status      AS status,
  t.custrecord_ord_tran_entity      AS customer_id,
  t.custrecord_ord_tran_isa_sender  AS isa_sender,
  t.custrecord_ord_tran_link        AS orderful_link,
  t.custrecord_ord_tran_error       AS error_summary
FROM customrecord_orderful_transaction t
WHERE t.id = :ns_tran_id   -- or use custrecord_ord_tran_orderful_id

Then fetch line-level error rows from customrecord_orderful_transaction_error linked to the same transaction. The error rows tell you which (qualifier, value) pair(s) failed to match.

(See reference/record-types.md §customrecord_orderful_transaction and §customrecord_orderful_transaction_error.)

Also fetch the corresponding Orderful-side transaction (raw EDI / parsed JSON) via the Orderful API:

GET https://api.orderful.com/v3/transactions/{orderful_tran_id}
Headers: orderful-api-key: ${ORDERFUL_API_KEY}, Accept: application/json

This gives you the full PO1-loop content: every (qualifier, value) pair on the failing line, the description, the unit price, physical attributes, the partner-supplied PO line number.

Step 2 — Cross-qualifier check (most common fix)

For each (qualifier, value) on the failing PO1 line, check whether the value already exists in customrecord_orderful_item_lookup for this customer under any other qualifier:

SELECT
  l.id                                          AS lookup_id,
  l.custrecord_orderful_item_qualifier          AS qualifier,
  l.custrecord_orderful_item_qualifier_value    AS value,
  l.custrecord_orderful_item_item               AS ns_item_id,
  l.custrecord_orderful_item_subsidiary         AS subsidiary_id
FROM customrecord_orderful_item_lookup l
LEFT JOIN map_customrecord_orderful_item_lookup_custrecord_orderful_item_customer cm
  ON cm.mapone = l.id
WHERE l.isinactive != 'T'
  AND UPPER(l.custrecord_orderful_item_qualifier_value) = UPPER(:value)
  AND (cm.maptwo IS NULL OR cm.maptwo = :customer_id)
ORDER BY
  CASE WHEN cm.maptwo = :customer_id THEN 0 ELSE 1 END

If you find a match for the same value under a different qualifier, the fix is almost always:

Add a new lookup record with the missing qualifier + value, pointing at the same NS item the existing lookup uses, scoped to the same customer.

Do NOT modify the existing lookup; create a new one. Don't bundle multiple changes.

Step 3 — Candidate search (when no cross-qualifier hit)

If Step 2 finds nothing, search the customer's NS item master for candidates that look like the failing PO1 line. Combine these signals (any one alone is too weak):

Build a candidate list of 1-5 items. Score each by how many signals matched.

-- Example: find items this customer has ordered before, joined with text similarity
SELECT DISTINCT
  i.id, i.itemid, i.displayname, i.description, i.baseprice
FROM item i
JOIN transactionline tl ON tl.item = i.id
JOIN transaction tx     ON tx.id   = tl.transaction
WHERE tx.entity   = :customer_id
  AND tx.recordtype = 'salesorder'
  AND (
    UPPER(i.itemid)     LIKE '%' || UPPER(:partial_sku) || '%' OR
    UPPER(i.displayname) LIKE '%' || UPPER(:description_keyword) || '%' OR
    UPPER(i.description) LIKE '%' || UPPER(:description_keyword) || '%'
  )
  AND i.isinactive = 'F'
FETCH FIRST 10 ROWS ONLY

Adjust the keywords based on the actual PO1 content.

Step 4 — Confidence scoring + propose

Score each candidate:

• High confidence (≥3 strong signals match): one clear winner. Propose creating a single lookup mapping (qualifier, value) → that NS item ID.
• Medium confidence (2 signals): show the user the top 2-3 candidates with the signals that matched. Let them pick.
• Low confidence (≤1 signal, or no candidates): escalate, do not propose. Tell the user what you searched for and what didn't match. Suggest they manually identify the right NS item.

Step 5 — Output the proposed plan

For high-confidence fixes, output a structured proposal — never the actual write. Format:

PROPOSED FIX

Create item-lookup record:
  Customer:  <customer name> (id: <customer_id>)
  Qualifier: <qualifier>
  Value:     <value>
  NS Item:   <item itemid> — <displayname> (id: <ns_item_id>)

Reasoning:
  - <signal 1: e.g. partner SKU "ABC-123" matches NS item ABC-123-EA at 100% string sim>
  - <signal 2: e.g. unit price $59.99 matches NS list price $60.00 (within ±0.02%)>
  - <signal 3: e.g. customer has ordered this NS item 12 times in the last 90 days>

Confidence: high

To apply: in NetSuite, navigate to Customization → Lists, Records, & Fields → Record
Types → Orderful Item Lookup → New, fill in the fields above, and save. Then reprocess
transaction <ns_tran_id> in NS to verify the fix.

Then stop. Wait for the user to confirm. The skill never writes to NS itself.

If the user confirms, restate the manual steps clearly. If they want to do it via the API, point them at record.create patterns but make them invoke that themselves — this skill is propose-only.

Behaviour rules

1. Never create a lookup record without explicit user approval. Always propose first; let the user execute (or have a future productized agent execute via approved plan).
2. Reject ambiguous matches. If no candidate has high confidence, escalate. Surface what you tried and why nothing matched. Don't propose a guess.
3. Never invent items. If you can't find the candidate NS item via SuiteQL, say so. Don't fabricate item IDs or names.
4. Don't bundle unrelated fixes. One failing transaction, one scoped lookup proposal. If the same 850 has multiple failing lines, propose one fix per line in separate output blocks — not a single combined diff.
5. Preserve user intent on qualifiers. If the PO sends BP=ABC-123, propose creating BP=ABC-123 → \<item\>. Don't silently propose a UP= lookup unless the cross-qualifier check (Step 2) showed that pattern, and even then, propose it as one additional lookup, not a replacement.
6. Show your reasoning. Every proposal includes the signals that led to the match. The user is approving based on your reasoning — make it inspectable.
7. Reprocessing is NOT automated. After the lookup is created, the user manually reprocesses the failed transaction in NS. This skill never triggers reprocessing.
8. No echoing secrets. API keys, NS tokens — never repeat them back to the user.
9. Customer-restrict, don't subsidiary-restrict, by default. Skip the subsidiary field on proposed lookups unless the customer specifically operates per-subsidiary. Most customers don't.
10. Confirm isinactive = F on any existing lookup you found in Step 2 before suggesting it as a "the existing item is X" reference. An inactive lookup doesn't fire at runtime.

Common gotchas

• Customer multi-select is empty. If the existing lookup has no customer restriction (maptwo IS NULL), it fires for every customer. Adding a customer-specific lookup will not override it — they coexist. If the existing global lookup points at the wrong item for this customer, you need to either inactivate the global lookup or add a customer-specific one (the connector prefers customer-specific). Tell the user.
• Trailing whitespace / hidden chars in the lookup value. If UPPER(value) = UPPER(:value) looks like it should match but doesn't, suspect non-printable characters. Tell the user to inspect the existing record's value field for whitespace.
• Wrong subsidiary restriction. If the customer's transaction is in subsidiary A but the lookup is restricted to subsidiary B, no match. Cross-check custrecord_orderful_item_subsidiary against the transaction's subsidiary.
• Inactive item. The proposed ns_item_id must be active (item.isinactive = 'F'). If it's inactive, the lookup will fire but the downstream sales-order creation will fail. Verify before proposing.
• UOM mapping does NOT fire for lookup-resolved items — check line units after the fix lands. Verified live: with a correct UOM mapping in place (e.g. CA → Master Case), 850 lines whose items matched via a lookup record still landed on the SO in the item's base unit — quantities and amounts silently wrong, nothing errors. After the user creates the lookup and reprocesses, always verify the resulting SO line units against what the PO's UOM implies; if the customer orders in cases/packs, expect to have the user patch line units + quantity + rate until the SuiteApp gap is fixed. Propose the units check as part of the fix, not as an afterthought.
• Authoring a UOM mapping record: names don't work. custrecord_orderful_uom_target rejects unit names ("Master Case", "CS") — it needs the unit's internal id, and the Target UOM Type field must be populated, or the record saves but never matches.

Reference material

• reference/record-types.md — full schema for customrecord_orderful_item_lookup, customrecord_orderful_transaction, and the related records this skill queries.