ANS-0045 · SAVED SEARCHES & FORMULAS
How to Consolidate Invoice, Sales Order, PO, and Payment Data in NetSuite Saved Search?
Learn to build a NetSuite transaction saved search that displays related Invoice, Sales Order, Purchase Order, and Payment details on a single line.
Short answer
To display Invoice, Sales Order, Purchase Order, and Payment details on one line in a NetSuite transaction saved search, group results by Invoice number. Utilize the 'Paying Transaction' field for payments, 'Applied To Transaction' for sales orders, and a specific formula for purchase orders, linking transactions effectively.
Scenario
Users often need to view comprehensive transaction data, including Invoices, their associated Sales Orders, related Purchase Orders, and Payments, consolidated onto a single line within a NetSuite saved search. This requirement arises when tracking the full lifecycle of a transaction from order to payment.
Solution
A transaction saved search can be configured to group the results by the Invoice number, consolidating related transaction details. From the Invoice, the Paying Transaction field is used to retrieve the Payment number. The Applied To Transaction field is used to retrieve the Sales Order number. To obtain the Purchase Order number, the following formula is utilized:case when {type} 'Invoice' and {appliedtotransaction.applyinglinktype} 'Special Order' then {appliedtotransaction.applyingtransaction} else null endThis formula specifically checks for a Transaction Type of 'Invoice' and an 'Applied To Transaction' with an 'Applying Link Type' of 'Special Order' (on the Sales Order). It then retrieves the 'Applying Transaction' (Purchase Order) from the 'Applied To Transaction' (Sales Order) on the Invoice. This configuration results in a saved search line displaying the Invoice Number, Sales Order Number, Purchase Order Number, and Payment Number.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support