About Salto: Salto helps you and your team deploy, track, and manage your NetSuite customizations effortlessly. Learn more here.

‍

There are many things that can get overlooked when working with NetSuite saved searches. In this guide, we will walk through advanced tips to consider when building your next NetSuite saved search so you can avoid these common mistakes in the future.

An Introduction to NetSuite Saved Searches

Before we jump in and take a look at the mistakes to avoid when working with NetSuite Saved Searches, let’s first introduce them. Saved Searches in NetSuite are one of the primary reporting tools available in the system. Whether you are looking to get information on specific NetSuite transaction types or purchase price history tracking for a specific vendor, you can generally get the details that you need quickly and to a high degree of accuracy. This is because NetSuite allows you to set your own reporting criteria, your own reporting columns and your own saved search filters.

NetSuite saved search joins are predefined based upon the object you are reporting on. For example, if you are reporting on a transaction record, NetSuite has a predefined join to corresponding vendor records that might be associated with the related transaction records.

NetSuite Case Statements are available to write your own conditional formula functions where your reporting requirements are more complex. If you’re not sure of a field internal ID to reference in a NetSuite Saved Search formula, you can always refer to the NetSuite Record Browser (or Schema Browser) to match a standard field ID with its corresponding internal ID. Keep reading if you’re looking for some NetSuite Saved Search formula tricks later in this article.

Salto Tip: You can also reference to the NetSuite Connect Browser to explore the data structure available throughout NetSuite.com data source for SuiteAnalytics Connect, however this data source is being phased out and you will need to transition to NetSuite2.com data source asap to avoid potential disruptions.

Saved Searches in NetSuite offer additional functionality to support your specific reporting needs. You are able to make a Saved Search public by checking a box - doing so allows other users in the system to access the Saved Search. You can also leverage NetSuite Saved Searches to have your results group by a particular field, such as grouping an “Amount” to get the SUM of a set of transaction records. NetSuite summary saved searches are powerful, as they can be leveraged to get reportable data without extracting data to Excel and manipulating the data manually - let NetSuite do this for you!

You can also leverage NetSuite FX rates sources i.e. a direct NetSuite currency exchange rate integration to run your Saved Searches in different currencies such as transactional currency vs functional currency. If you are using NetSuite OneWorld, you can use the same NetSuite currency exchange rates to produce consolidated reports for your parent subsidiary.

Lastly, you are able to reference NetSuite Saved Searches in SuiteScript. For example if working on a SOAP request and you were looking for a NetSuite Saved Search API, you can leverage the “getSavedSearch” operation to retrieve a list of Saved Search IDs. Something similar can be achieved for REST requests via a RESTlet.

‍

‍

Why are NetSuite saved searches so powerful?

NetSuite saved searches are the backbone of NetSuite reporting, for both recurring and ad hoc reporting requirements.  While NetSuite is shifting toward SuiteAnalytics, most NetSuite users will tell you that they are more comfortable working with NetSuite saved searches for their ease of use, flexibility, and ability to get to the data they need immediately. This core NetSuite functionality allows users to:

• Define the filter criteria for their report
• Determine the columns (results) to be displayed
• Apply highlighting to specific rows
• Allow filtering directly within the results
• Restrict results to a specific subset of NetSuite users
• Email results to specific recipients either on-time or scheduled

And much more.
‍

Mistake #1 – Doing Manual Audit Sample Requests

Imagine it is year end and your company auditors have requested a sample of transactions to test and they require additional information on these records. If the sample size is 5-10, you could simply add an “Internal ID (Number)” criteria to a NetSuite saved search and manually reference those record internal IDs, assuming you have them. Now imagine that sample size is 50-100. How would you go about obtaining the additional information requested by the auditors? Some might consider gathering the data manually or try and replicate the criteria used by the auditors. Save yourself time/effort and consider the following:

One solution to consider is a NetSuite saved search with a case when “Formula (Numeric)” field that is “equal to” 1.

‍Formula Example: CASE WHEN {internalid} = ANY(‘123’,’456’,’789’,’etc’) THEN 1 ELSE 0 END

‍Now, it is not quite that simple. You will need some Excel skills to add quotes to each internal ID and concatenate into a comma separated list.

Add quotes to each internal ID: ="'"&A1&"'" replace A1 with the first cell reference in your list of Internal IDs. Drag that formula down the entire list.

Concatenate into a comma separated list: =TEXTJOIN(",",TRUE,A:A) replace A:A with the column your Internal ID in quotes resides in.

‍Suite Note – The character limit for the “Formula (Numeric)” field is 4000 characters, so you may need to break up into multiple saved search criteria by using expressions.

The great thing about taking this approach is the flexibility. Replace all references to “Internal ID” with External ID, Document Number, or any other unique reference you have been provided with.

For the script-inclined, the same comma separated list generated in Excel could be inserted into a small script to create and save a new search as follows. In this case require was used as the script was run from the debugger.

P [ require(['N/search'], function(search){

const soSearch = search.create({

type: "salesorder",

filters:

[

["internalid","anyof","123","456"],

"AND",

["mainline","is","T"]

],

columns:

[]

});

soSearch.title = "Audit Sample Request";

soSearch.id = "customsearch_audit_sample";

soSearch.save();

})

‍
‍
‍

‍

Mistake #2 – Not Knowing Your Consolidated Exchange Rates

The “Consolidated Exchange Rate” field is available in all Transaction saved searches for NetSuite One World customers. The field value is critical to every Transaction saved search and should be intentionally selected. The value will default to “Per-Account”. This is the correct value for Transaction saved searches that need to tie back to consolidated financial data, because NetSuite performs the consolidation dynamically, with reference to the “Per-Account” consolidated exchange rates.

Generally speaking, you should not need to set the “Consolidated Exchange Rate” field value to “Average”, “Current” or “Historical”. Doing so will force the saved search to use consolidated exchange rates that may differ from the underlying general ledger accounts, which is not recommended.

The other option available is “None”. This value is suitable if you wish to run a Transaction saved search that ties back to the functional currency values for each subsidiary, so do not assume that “Per-Account” is always correct for a given report request. Instead consider the appropriate value for each and every saved search.

‍Example: You create a Transaction saved search containing all vendor bills for subsidiaries located in the UK as well as Ireland. The functional currency for the UK is GBP and the functional currency for Ireland is EUR. When running the saved search and referencing the “Amount” value, it will match the functional currency for that subsidiary.

UK vendor bill for USD 200 at a spot rate of 0.5 would show an amount of GBP 100

Ireland vendor bill for GBP 100 at a spot rate of 1.5 would show an amount of EUR 150

Let’s take this example one step further and consider the same Transaction saved search except that the “Consolidated Exchange Rate” field value is instead set to “Per-Account”.

‍Example: The same example as before, but you need to know that the functional currency for the consolidated parent subsidiary is USD.

UK vendor bill for USD 200 at a spot rate of 0.5 would show an amount of GBP 100. NetSuite then converts this GBP 100 to USD at a consolidated current rate of 1.5 to show a consolidated amount of USD 150.

‍Suite Note - Even though the original transaction was entered as USD 200, the consolidated amount, in the same currency, can (and very likely will) differ.

Ireland vendor bill for GBP 100 at a spot rate of 1.5 would show an amount of EUR 150. NetSuite then converts this EUR 150 to USD at a consolidated current rate of 1.2 to show a consolidated amount of USD 180.

‍Suite Note – What can really throw people off is when running a Transaction saved search that includes line level information. The header level account will generally be measured at the “consolidated current rate”, whereas the line level account will generally be measured at the “consolidated average rate” if posted directly to an income statement account. These saved search results will produce transaction amounts where debits do not equal credits. This may seem inaccurate at first glance but is quite the opposite. The difference between the debits and credits forms part of the cumulative translation adjustment (CTA) account.

‍
‍

‍

Mistake #3 – Not Utilizing Reminders/Dashboards

An often-overlooked feature within NetSuite saved search functionality is the ability to create simple, concise reminders. It is easy to get lost in all of the data at your disposal, so consider setting up saved searches that will sit on your NetSuite dashboard as a single number. A single number that can answer multiple questions in the blink of an eye. Stop relying on scheduled saved search results that run once per day and start relying upon NetSuite reminders that can be refreshed within a matter of seconds. Here are some use cases to consider:

‍Example: Create a “Workflow” saved search that captures all workflow changes in the last 24-36 hours by setting the “Date Modified” to “On or after” Yesterday in the criteria. Then check the “Available for Reminders” check box and set as a reminder on your NetSuite dashboard. Workflow modifications can have detrimental impacts if not thoroughly tested. Seeing the number of workflow changes in the last 24-36 hours change from 0 to 1 (or higher) will give a NetSuite Admin the desired insight. Quickly.

Similar functionality could be achieved by leveraging an hourly email schedule that only triggers if the saved search has results. Unfortunately, after the first helpful notification, the NetSuite Admin will continue to receive email notifications until the schedule has run its course. This will just create noise for the NetSuite Admin and should be avoided in scenarios like this.

This logic can be applied to any NetSuite record, so if you have a particular NetSuite record that you are required to monitor more closely than others e.g. chart of accounts, items, etc. then consider creating a similar saved search/reminder for those records as well.

‍Example: A “Server Script Log” saved search can support both NetSuite Admins and NetSuite Developers in quickly identifying script errors that need to be investigated/resolved in a timely manner, especially when configured on dashboards. When configured appropriately, most suitescript errors will provide a description of the error that can then be acted upon. Having the number of errors captured in a dashboard reminder is helpful but coupling those with the associated error details is even more valuable.

Some of these errors will directly impact end users and prompt them with a suitescript error message, which they may or may not report to the NetSuite Admin (hence the need to track separately). Other errors do not directly impact the user in the NetSuite UI, but nonetheless have other implications to be addressed. This is a great example of giving the NetSuite Admin the tools necessary to resolve issues before they directly impact the end user.

You should consider server script logs with “Type” of Error, Emergency or System. Pulling Debug/Audit logs will be helpful in resolving suitescript issues but including them would only make it more challenging to identify true script errors that require investigation.
‍

‍

Example: A “Login Audit Trail” can be invaluable when considering potential security threats to your NetSuite environment. Consider creating a saved search that captures all failed login attempts in the last 24-36 hours by setting the “Security Challenge” to “is Failure” in the criteria. Date criteria as described above. Not only will this give a NetSuite Admin quick insight into a potential security threat, but (more likely) identify users that have forgotten their password and require a password reset. Better yet, this reminder will help swiftly identify system user accounts that are failing to authenticate with NetSuite, resulting in integration points breaking.

‍Suite Note – You cannot utilize a saved search with summary results as a dashboard reminder. The reminder will always show 0 results even when the corresponding saved search has more than 0 results.
‍

Mistake #4 – Not Restricting Item Dropdowns

‍

Many businesses will have multiple streams of revenue, for example a software company may offer subscription services as well as professional services to implement their software. In NetSuite, this will typically be represented in the form of separate items for sale and separate transaction forms e.g. subscription services sales order form and a professional services sales order form. Separate forms are helpful for tracking fields relevant to each revenue stream, but how do you circumvent users entering a subscription item on a professional services form and vice versa?

Did you know that you can restrict which items for sale appear in the drop-down list on a transaction, based upon the results of an Item saved search? In other words, you could restrict the subscription services sales order form to only display subscription items for sale in the item drop down. The same would apply to professional services.

This can be achieved by first creating an Item saved search (effectively used as a saved search sublist), with criteria that limits the search results to the population required on the transaction form.

‍Suite Note – This Item saved search must have the “Public” check box checked to be available for selection on the custom transaction form.

Once the saved search has been created, navigate to the custom transaction form.

Navigate: Customization – Forms – Transaction Forms – Click “Edit” on form – “Sublist Fields” – “Item Filter” – Select Saved Search – Save.
‍

Mistake #5 - Not Using Hyperlinks

‍

Including hyperlinks in a saved search can help end users drill down into NetSuite records quickly and efficiently. They are especially helpful when used in combination with a summary saved search. NetSuite saved searches that have results summarized require a user to drill down within the results to get to the underlying records. This is particularly painful where a user needs to go back and forth between the summarized results and the drill down results.

To avoid this challenge altogether, consider adding a hyperlink field to the saved search results that will display in the summarized results, thus allowing the user to access the underlying records without having to first drill down.

‍Example: Consider a Transaction saved search that includes both main line and line level data for sales orders. The saved search results have been grouped by Internal ID to output a single line per sales order. As a result of the grouping, the user must drill down into each line before they can access the underlying sales order record, unless a hyperlink formula is added to the saved search results.

Formula (Text) Example:

‍'<ahref="https://ACCOUNTID.app.netsuite.com/app/accounting/transactions/salesord.nl?id='||{internalid}||'&submitter=Submit">VIEW RECORD</a>'

ACCOUNTID – Replace with the account id for the NetSuite environment you are working in.

‍VIEW RECORD – Replace with the text you wish the user to see in the saved search results

‍accounting/transactions/salesord.nl?id= - Replace with URL string for the record type you are working with. This is the URL string for sales orders but will vary by record.

Now when a user clicks on VIEW RECORD, they will be routed to the underlying sales order record as opposed to a drill down of the sales order line level details.

‍

‍

Mistake #6 – Hardcoding In SuiteScript

‍

As a NetSuite Developer, it is often easier to hardcode a NetSuite saved search lookup, think search.create(), vs referencing a saved search id in a script parameter.

There are benefits to referencing the saved search within a script parameter. For example, if you have a scheduled script/map reduce script that needs to be executed based upon a specific transaction date or accounting period, these values could be set within the saved search criteria and easily modified prior to executing the script. This would be preferable to modifying hardcoded values within the script file itself. In this, leveraging a saved search/script parameter is a more flexible development approach. Not only that, but change management is less burdensome given no script modifications are required.

Consider the same use case, but this time your script execution criteria requires modification. Instead of modifying the script code you can simply update the criteria in the saved search to capture the new requirements. Again, this highlights the flexibility of using a saved search/script parameter vs hardcoded search.

‍Suite Note – There will of course be valid use cases that require hardcoding the criteria in the script directly. You will need to consider your business case and determine the appropriate route.

Another hardcoding practice to avoid is referencing record internal IDs to access resources within a script. The internal ID of a record in one NetSuite environment will not necessarily match the internal ID of the same record in another environment. This adds another layer of complexity when considering a suitescript production migration, as any internal ID references will need to be updated before the script can be deployed in the target environment.

‍

Mistake #7 – Not Summarizing Scheduled Emails

‍

For most use cases that require emailing saved search results to specific recipients, it is critical that the “Summarize Scheduled Emails” check box is checked. When checked, the recipient will receive a single email with the summarized saved search results. When unchecked, the recipient will receive an email for every saved search result i.e. 1000 saved search results = 1000 emails, 10,000 saved search results = 10,000 emails, etc.

‍Suite Note – Once the schedule has been triggered there is no way to stop it and the emails will run their course.

Mistake #8 – Not Grouping System Notes

‍

NetSuite system notes can be very helpful for identifying specific record creation/modification scenarios. When working with NetSuite system note fields within NetSuite saved searches, you should consider using the NetSuite saved search summary criteria functionality to reduce the volume of data to sift through to get to the data points required. Often users will pull all system notes for a given record or set of records and manually identify the results that meet their requirements. This becomes challenging when records have 100+ system notes, but you are focused on a specific record modification.

‍Example: Locate all journal entry transactions that have been approved, then subsequently unapproved in the UI.
‍

‍

This set of criteria will produce the correct underlying data set, but system notes will be duplicated many times over. To resolve for this issue, you can leverage the “Summary Type” dropdown options in the “Results” tab by grouping transactions and system note fields. Then locate the maximum or minimum values/dates to achieve a single row for each record.
‍

‍

Mistake #9 – Migrating Manually

‍
‍

‍

Mistake to Avoid #10 – Forgetting to leverage summary criteria

When establishing the criteria for your NetSuite Saved Search you are able to provide standard criteria or summary criteria. One of the advantages of summary criteria is that you can filter the results in advance vs summarizing after the fact. This can be valuable especially where the same summarization is required on a consistent basis.

For example, if you needed to report on your most valuable customers based upon total sales that exceed a certain threshold, you can achieve this with summary criteria. Any customers with total sales below the threshold will not be displayed. The alternative would be to extract the raw data and summarize in Excel using a pivot table or a SUMIF formula, which is not ideal especially when the summarized results can come directly from the system.

‍

Mistake to Avoid #11 – Not understanding how to limit results

NetSuite Saved Searches allow you to limit the results in multiple ways. The most obvious being that you can filter the results to specific records that you need, but you can also make the Saved Search results dynamic based upon the user that runs it. If you wanted each user to see the records that they created without creating multiple saved searches you could simply add criteria that sets the “Created By” field to “- Mine -”. If you’re working with specific Sales Teams you can achieve something similar by referencing “- My Team -” instead.

If granting users access to “Run Unrestricted” i.e. they can access Saved Search results they would not normally have permission to see, you are able to limit the results they see by populating the “Max Results” field. If you define a Max Results limit, and the number of search results exceeds this limit, a "more..." notation displays after the last row of results.

‍

Mistake to Avoid #12 – Producing duplicate results

Depending upon your NetSuite Saved Search criteria and results, you may end up with duplicate results. It could be as simple as referencing both the header and line level information on transaction records that results in double counting totals. It could also be the introduction of column that refers to system notes resulting in multiple results for the same record. Whatever the cause, it is important that you understand the potential for duplication in your Saved Search and fully test/validate the results before relying upon them.

‍

‍

Final thoughts

There are additional saved search mistakes we haven't listed here, such as not using NetSuite CASE statements more consistently. NetSuite CASE statements are similar to an IF statement used in Excel (used heavily). Use CASE statements to quickly filter to the saved search results you need. Also, NetSuite saved search drop down menus can be challenging to navigate. Instead of scrolling through long lists of fields to find what you need, try leveraging the NetSuite records browser to identify if specific fields are accessible and their corresponding IDs.

Saved searches are particularly painful to migrate between different NetSuite environments. This is primarily due to the dependent records linked to saved searches. As a result, many companies will opt to migrate saved searches manually from their sandbox environment to their production environment. This not only adds to the length of time to perform migrations, but increases the risk of human error, especially with more complex saved searches.

Instead, consider leveraging a tool, such as Salto, that will manage the migration process for your saved searches seamlessly. Doing so reduces time, risk and ultimately frees up the NetSuite Admin to focus on value-add initiatives.

If you are not ready to leverage a separate tool for your migration process, you should still consider a solution that clearly identifies all dependencies associated with the saved searches you are planning to migrate. This can save on future headaches, especially if you plan to migrate saved searches via SuiteBundler or SuiteCloud Development Framework (SDF).