A common challenge when using NetSuite saved searches is linking to the results of another saved search from within the results of a current saved search. This may be necessary when creating a saved search that shows an item and its various associated transactions. In this instance, the same item does not need to be shown more than once. Enabling drill through functionality allows users to quickly see all the transactions associated with that item.

Utilizing the drill through function as a solution

• Since each item should only show once, build the saved search based on the Item record.
• Update the criteria to only show Inventory Items by selecting the Type equal to Inventory.
• Go to the Results tab and show Name, Display Name, Description, Type, and Base Price.
• Create a formula (text) column and name it “Sales Transactions.”
• Create a formula (text) column and name it “Purchase Transactions.”
• Create a formula (text) column and name it “Inventory Transactions.”

Values for the formula (text) columns for Sales Transactions, Purchase Transactions and Inventory Transactions will link to separate saved searches. This will show all the transactions related to the given item.

For this to work, create the “child” saved searches. This will show the transactions related to an item in our “parent” saved search. To achieve this, create a new saved search against the Transaction. Then, set the TYPE to Invoice, Sales Orders, Cash Sale, Credit Memo (or any other transaction type that needs to be included). At a minimum, Available Filters will need to be set to the Item. In this example, Location is also included.

Examining a sample NetSuite saved search

The saved search will look like this:

To determine the structure of the URL of this child saved search, run the search and set the filters on the item and location (some of the information in this example is redacted for privacy purposes).

This is the URL for this example:

https://system.na2.netsuite.com/app/common/search/searchresults.nl?searchtype=Transaction&Transaction_LOCATION=2&Transaction_ITEM=1205

The two elements of the URL to focus on are the filters that were set, which are Transaction_LOCATION and Transaction_ITEM

In this example, the value for Location is “2,” whereas Item is “1205.” Both of these are Internal IDs. If these are changed to any other existing Internal IDs manually and the view is refreshed, the results will change. With this new understanding of how the filters can be set on a saved search through URL parameters, these URL-based filters from the saved search formula can be used in the parent item saved search.

The formula (text) for Sales Transaction is shown below. An HTML anchor tag will be used to open the saved search of the related sales transactions in a new tab.

(‘<a href=https://system.na2.netsuite.com/app/common/search/searchresults.nl?searchid=60&saverun=T&Transaction_LOCATION=ALL&Transaction_ITEM=’|| {internalid} ||’  target=”_blank”>Sales details</a>’ ) 

Transaction_ITEM is set with the INTERNALID of the itemID.

Transaction_LOCATION is set with value ALL which shows all locations.

To search for specific location, use {inventorylocation.internalid} as shown in the example below.

(‘<a href=https://system.na2.netsuite.com/app/common/search/searchresults.nl?searchid=60&saverun=T&Transaction_LOCATION=’|| {inventorylocation.internalid}||’ &Transaction_ITEM=’|| {internalid} ||’  target=”_blank”>Sales details</a>’ )  

Reviewing NetSuite saved searches and their results tabs

By clicking on the Sales Details link, users can see the results of the related child saved search showing all sales transactions related to the item. This approach can be leveraged and widely applied through NetSuite, well beyond just transactions related to an item.

NetSuite support provided by Citrin Cooperman

Citrin Cooperman’s Digital Services Practice has established an effective methodology to help maximize every dollar of enterprise resource planning (ERP) budgets and ensure that NetSuite is meeting your business’ needs. To learn more about our NetSuite support and optimization services and how they can help your business achieve its strategic objectives, reach out to your Citrin Cooperman advisor or sales@citrincooperman.com.