ANS-0122 · SAVED SEARCHES & FORMULAS
Why is a NetSuite Saved Search Not Returning Expected Join Results?
Understand how NetSuite's predefined join logic, acting as either inner or left joins, impacts saved search results when related records are absent.
Short answer
NetSuite saved searches apply predefined join logic, acting as either an effective INNER JOIN or LEFT JOIN based on the data model and search configuration. An effective INNER JOIN excludes primary records without matching related data, leading to no results if no matches exist. An effective LEFT JOIN, however, includes all primary records, even if related data is missing.
Scenario
A user configured a NetSuite saved search to display contacts along with their campaign responses. However, the search yielded no results, even though contacts existed in the system. This occurred because none of the contacts had any associated campaign responses, leading to an unexpected empty result set.
Solution
When a NetSuite saved search joins records such as Contacts and Campaign Responses, the system's predefined join logic often acts as an effective INNER JOIN. This behavior means that only primary records (contacts) that have corresponding related records (campaign responses) will be included in the results. Consequently, if no contacts have any associated campaign responses, the search will return no results. Conversely, when joining other record types, such as Contacts and Transactions, NetSuite's predefined join logic may act as an effective LEFT JOIN. In this scenario, all primary records (contacts) are returned, regardless of whether they have any associated transactions. The specific join behavior, whether acting as an effective INNER JOIN or LEFT JOIN, is determined by NetSuite's underlying data model and the precise configuration of the saved search, rather than being random.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support