ANS-0128 · SAVED SEARCHES & FORMULAS
Retrieving Vendor-Specific Item Codes on NetSuite Purchase Order Lines
This guide explains how to configure a NetSuite saved search to display the vendor-specific item code for purchase order line items, especially when an item has multiple associated vendors.
Short answer
To display vendor-specific item codes on purchase order line items in a NetSuite saved search, group results by line number and PO number. Use a MAX aggregation with a CASE WHEN formula to conditionally show the item's vendor code when the vendor matches, ensuring accurate vendor-specific data for each line.
Scenario
Users often need to retrieve the specific vendor's item code associated with each line item on a Purchase Order within a NetSuite saved search. This can be challenging when an item might have multiple vendors, and the goal is to display the code relevant to the vendor on the current purchase order. The requirement is to ensure that the correct vendor's item code is shown for each PO line.
Solution
To achieve this, configure a NetSuite saved search to group by line item and use a conditional aggregation formula. First, group the saved search results by line item using the 'Line Number' and 'PO Number' (Transaction Number) fields. Next, add a new column with a 'Max' aggregation. The formula for this column should conditionally display the item's vendor code based on a match with the transaction's vendor. The formula to use for this column is:n
Max of CASE WHEN {item.othervendor} {vendor.entityid} THEN {item.vendorcode} ELSE NULL ENDPlease note: The field reference {item.othervendor} for accessing other vendor information on an item line may vary depending on your NetSuite configuration or how vendor relationships are defined on the item record. You may need to adjust this specific field reference to correctly match the item's associated vendor with {vendor.entityid} in your environment. The Max aggregation ensures that only the item.vendorcode corresponding to the matching vendor is displayed; otherwise, the column will remain blank for that line.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support