NetSuite advanced saved searches use criteria and results in ways that are often overlooked or not understood. By ramping up these built in features, your saved searches will be more powerful and useful. This will give you a better way to analyze all sorts of aspects of your business.

Before building your saved search, you must determine the main information you are seeking. There is often more than one way to find your results, but how you reach them can vary.

For example, you may use a customer search to see how many orders your top customers have made in the last 6 months. However, a similar search could be performed using a transaction search. The starting point you choose will be even more important if you end up using the search in a workflow.

Advanced Saved Searches Criteria

Criteria is where to start when creating your advanced saved search. The more specific you can get with your saved search, the easier it will be to analyze the results. The fields available will vary depending on the search type. Many fields are also a little tricky to find. In a transaction search, to find ITEM fields you need to scroll all the way down past the transaction fields and find ITEM FIELDS… – this will then lead to the available ITEM fields.

Once you’ve selected your specific field, then you must decide what criteria to look for. This could include TRUE/FALSE for a checkbox or multiple items on a list of transaction types. You can also choose date ranges, specific values, whether a field is empty or not, and more.

I’ve often come across situations where I can’t quite use the available fields to narrow down my results the way I like. This is where custom fields can be really handy.

Example: Say you want to do a customer search for customers you’ve sent samples to. You know what customers have been sent, but that info isn’t in NetSuite. You can create a CUSTOM ENTITY FIELD. Under Customization -> Lists, Records & Fields -> Entity Fields create a new Checkbox Field and assign it to CUSTOMER.

Once you have a checkbox field created and visible on customer records, you can use this to simply mark the customers that have been sent samples. This field then becomes available in saved search criteria, providing a simple, fast way to isolate records.

TIP: Sometimes the criteria you choose will return multiple results for the same record. To avoid this, choose the field MAIN LINE and select “YES”.

Advanced Saved Searches Results

NetSuite advanced saved search results give you a lot of options that aren’t all that straightforward. In simplest terms, you can choose the fields you want displayed and arrange them in the order you want them listed. If your criteria is solid, the results will show a row for each result, sorted by whatever field you choose to sort by (also found under Results).

But this is only the tip of the saved search iceberg.

Each chosen field has several options available to it: Summary Type, Function, Formula, Custom Label, and Summary Label (plus a few others). The labels are simply a way to create headers that better describe your results; especially useful when doing a summary or if the field name is long or generic. Ex: Changing “Document No.” to sale “Sales Order #”.

The really interesting fields are Summary Type, Function, and Formula.

Summary Type

Summary Type includes two main options: GROUP and COUNT for text-based fields and SUM, MAX, MIN, or AVG for numeric fields. Date fields are used a bit differently and discussed shortly.

GROUP works great when you are trying to do a summary of customer total sales, grouped by customer name, or number of items sold, grouped on the item name. Grouping works well on many fields as a way to calculate totals. COUNT will tally the number of records under a grouped set.

Example: If you group customers by state and then do a count on the company name, you can get a total of number of customers per each state.

NOTE: NetSuite advanced saved searches are only as good as your data. In doing the above example, the results will count CA, California, and Californa as 3 different rows.

The numeric field I most commonly use is SUM. This is used when totaling sales amounts from customers, or number of items sold, etc.

Dates can also be grouped but when doing so are best coupled with one of the options under “Function”. These include Month, Quarter, or Year and let you summarize an action, like sales, during specific grouped periods of time. I’ve used this method countless times to research trends in product sales, customer orders, inventory levels, etc.

There are other options under Function, but I’ve only used the above mentioned and Round to Hundreths and Round to Tenths.

Using NetSuite Advanced Saved Search Formulas

The next major, overly complex and difficult to use section of saved searches is “Formula”. The formula field lets you perform math on multiple fields, create comparisons using CASE WHEN statements, and even stylize results among other things. Let’s take each of these in turn.

Simple math (very simple) can be done to get things like average order value or inventory used per month. The capabilities are limited here because you can’t sum up a total of grouped items here and do math on it. Instead it is as simple as this (using standard arithmetic rules):

{fieldname} || MATH FUNCTION || {otherfieldname}

CASE WHEN statements are a way to compare a field’s value and return a specific result based on that value. This opens up a lot more options and is much more useful.

Example, Using Field FORMULA (TEXT):

CASE WHEN {amount} > 275 THEN 'Order over $275' ELSE 'Order less than $275' END

All this does is check if the amount field is greater than 275 and return a simple statement if it is or is not. CASE WHEN statements can become extremely complex, especially when they are nested together to make multiple comparisons.

I’ve written an entire page on using CASE WHEN statements in NetSuite.

Other Things to Consider

With NetSuite advanced saved searches, it is important to understand what you want the search to do and focus on a specific goal. Saved searches work best when they have a specific goal laid out:

Business Process – sales, marketing, customer service, production, purchasing, etc.

Time frame – monthly sales trends, inventory usage per month, daily sales per source, etc.

Make use of the email feature. If it is important for someone to be notified when changes occur in the results of a saved search, make sure to use it. Scheduling the email as a daily reminder works well for certain tasks an employee may need to attend to regularly.

NetSuite’s ERP system is powerful and saved searches are one of the best ways to analyze the data. Have any questions or comments, contact me!