# Item Lookup > Diagnose and propose fixes for ITEM_LOOKUP_MISSING errors on inbound 850 purchase orders. Combines NetSuite item search, Orderful transaction context, and customer-specific matching conventions to propose a single, scoped lookup record for the contractor to approve. Use when the user mentions a failing 850, a missing item lookup, ITEM_LOOKUP_MISSING, or asks to map a partner part number to a NetSuite item. - Skill: `orderful/item-lookup` (Agent Skill) - Install (CLI): `npx skillmds@latest add orderful/item-lookup` - Raw SKILL.md: https://api.skillmd.com/api/skills/orderful/item-lookup/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: Orderful (https://skillmd.com/u/orderful) - Updated: 2026-09-22 - Page: https://skillmd.com/skills/orderful/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 → \" 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`](../writing-inbound-jsonata/SKILL.md)), 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//.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 \ 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: ```sql 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](../../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**: ```sql 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): | Signal | Source | Use as | |---|---|---| | Description / partner SKU strings | `item.itemid`, `item.displayname`, `item.description` (also custom alias fields if present) | Fuzzy text match against PO1 description | | Unit price | NS `item.baseprice`, customer-specific item rates | ±20% match against PO1 `unitPrice` | | Physical attributes | NS item dimensions, weight, pack | Match PO1 `itemPhysicalDetails` if present | | Recent purchase history | NS sales orders / 850s for this customer | "items this customer has ordered before" — strong prior | Build a candidate list of 1-5 items. Score each by how many signals matched. ```sql -- 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: (id: ) Qualifier: Value: NS Item: — (id: ) Reasoning: - - - 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 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 → \`. 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`](../../reference/record-types.md) — full schema for `customrecord_orderful_item_lookup`, `customrecord_orderful_transaction`, and the related records this skill queries.