Last month, we gave you a complete introduction to NetSuite saved searches, one of the most valuable tools available in NetSuite. The basics will take you a long way, but there’s so much more to learn about saved searches in order to make them an even more powerful resource for your organization. 

So this time we’re taking you deeper into more advanced techniques for your NetSuite saved searches. Let’s get started. 

Saved Search Formulas

In taking your NetSuite saved searches to the next level, you will likely start looking at the formula fields. These can be used to limit the search results for more precision, or perform operations on the results such as converting dates to strings. 

Creating a filter starts with selecting the saved search you want to work on and clicking Edit. Next, under the Criteria tab, add a filter by opening the drop-down menus and choosing the formula option you want: Date, Numeric, or Text. Add the formula in the FORMULA box and select Set. You can preview the results to make sure your formula is working as intended before you save and run the search.

You’ll need to use NetSuite’s formula syntax when you enter the formula. NetSuite uses Oracle SQL syntax, which means you’ll want to have some experience writing SQL before diving in. The basic formula syntax includes operators, functions, and variables and looks like this: 

{expression} {operator} {expression}

Expressions can be any valid Netsuite field, function, or literal value. Operators are what perform the mathematical, logical, or string operations on the expressions. And finally, variables are used to represent the results of the expressions. They can also store temporary values for use in subsequent calculations. 

A few extra hints on formulas: 

• If you want both positive and negative values to return when using Quantity, you’ll need to set your Filter field to Formula (Numeric) and use {Quantity} in your Formula field. Using Quantity in the filter field will automatically filter out negative results and only yield positive values in your search results.
• Don’t use script tags in Formula (Text) fields. NetSuite is set up to avoid cross-scripting vulnerabilities by not displaying script output for results for Formula (Text) fields that contain script tags. So just don’t bother. 
• Formula expressions have a 1000-character limit. If you exceed that limit, your search preview may still work, but running the actual search will result in invalid expression errors. It is best to stay under the limit to ensure you get results! 
• Date formulas can be filtered based on whether the date occurs before or after a specified date. Numeric formulas can be filtered based on whether the resulting value is greater than or equal to the specified criteria. So you’ve got options! 

Special Tips and Tricks for Building the Right Search

There are plenty of different ways to build saved searches to get exactly what you need. Use these tips and tricks to make sure you get what you’re looking for! 

“Main Line,” “Shipping Line,” and “Tax Line”
It’s easier than you think to define what information you’re looking at in a saved search view. If you only want to see the main section, set the Main Line criteria to “True.” Only want to see line items of the invoice? Then set to “False.” Or you can display both at once. This concept is similar for Tax and Shipping Lines, which may come in handy for working on sales orders where saved search results might be related to taxes or shipping fees. 

Calling A Field You Can’t Find
When building your saved search, you might not be able to find the right field in your list. If that’s the case, you can use NetSuite saved search formulas to call the field by its ID. First, bookmark this link to the schema browser, which lists all field IDs. Once you’ve located the field ID you need, insert it in brackets under Formula (text). Now your search will return exactly what you’re looking for.

Use NS_CONCAT to Optimize Your View
Combine results into a cleaner view with the help of NS_CONCAT. If you’ve got a screen where all the columns except for one—the items—are the same, you can condense the view and combine all the items into the same line by using the formula REPLACE(NS_CONCAT({item}), ’,’, ’’)

Highlight Critical Results
Immediately call out what’s most important in a search by going to the subtab Highlighting. Enter your condition, then select the icon next to the condition field to define both your filter and your criteria. Now when you run your saved search, results that line up with those guidelines will automatically be highlighted.

Joining Two Saved Searches in NetSuite

Sometimes when drilling into data with saved searches, it helps to join results from multiple saved search types. You can do this simply while building or editing a saved search by going to the Results tab and selecting a field with an ellipse ( … ) in the Field drop-down menu. 

There will be times that the drill-down you want isn’t possible with the above method, but all is not lost. In those cases, you can try the following: 

• Build two different saved searches with different accessibility and link them using a formula
• In the Results tab of your first saved search, use a Formula (Text) field and the URL of the second saved search.
• Replace the necessary fields in the URL by using the CONCAT function and double pipe operator ( || ). 

One Final Tip: Automating Saved Search Results

Then there’s perhaps the best tip of all for working with saved searches, and that’s integrating NetSuite directly with Excel to automatically pull saved search results from one, two, or even multiple saved searches into one dynamic dashboard. You can create pivot tables, use macros and formulas—all within the familiar environment of Excel. Save the workbook and refresh it on demand with a single click, or set up an automatic data refresh so reports are updated on a regular basis. 

What advanced knowledge or techniques do you need for this? None, as a matter of fact. You can do it all with ExtendInsights for NetSuite. This integration app gives you everything you need to manage and analyze NetSuite data in real time, right in Excel. Whether it’s using Excel formulas and complex models, or running one or more saved searches simultaneously in Excel with a single click, ExtendInsights can handle it. 

Sound too good to be true? It’s not. But don’t just take our word for it. Try ExtendInsights FREE for two weeks, no credit card required, and see what you think. It just might be exactly what you’re looking for.