ANS-0148 · SAVED SEARCHES & FORMULAS
NetSuite Saved Search: Counting Unique Transactions with Main Line False
This guide provides a formula and configuration for accurately counting distinct transactions in NetSuite saved searches, especially when dealing with non-mainline items.
Short answer
To count unique transactions in a NetSuite saved search, employ a formula using COUNT(DISTINCT({internalid})) combined with a CASE WHEN statement for specific criteria. Ensure the formula field's Summary Type is set to 'Count' for accurate results, particularly when mainline is false.
Scenario
Users often encounter challenges when attempting to count unique transactions within NetSuite saved searches, particularly when the 'Main Line' criterion is set to 'False'. This scenario typically arises when a search needs to aggregate data based on individual transaction lines while still ensuring each transaction is counted only once, preventing inflated totals.
Solution
To accurately count unique transactions in a NetSuite saved search, configure the formula field as follows: First, add a new 'Formula (Numeric)' field to your saved search results. In the 'Formula' field, input the following expression to identify and count distinct transaction internal IDs based on your criteria:
COUNT(DISTINCT(case when TRUNC({trandate},'MM') TRUNC(ADD_MONTHS({today}, -13),'MM') then {internalid} else null end))Next, for this formula field, set the 'Summary Type' to 'Count'. This configuration ensures that the saved search correctly aggregates and counts each unique transaction identified by the formula, providing an accurate total even when multiple lines exist for a single transaction.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support