ReportingNo Comments

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.

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 – Stop 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.

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();
})

Transaction saved search formula pop-up screen


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.

Saved search results tab


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.

Script log saved search criteria tab


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.

Custom transaction form sublist fields tab


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 – Avoid 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 – Summarize Scheduled Emails (Almost) Always

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.

Saved search email tab


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.

Saved search criteria tab referencing system notes

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.

Saved search results tab referencing system notes grouping


Mistake #9 – Stop Migrating Manually

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).

Final thoughts

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).

WRITTEN BY

Sonny Spencer

Sonny is a seasoned NetSuite veteran, with more than 7 years experience implementing NetSuite and architecting NetSuite solutions for a wide variety of public and private companies, on a global scale. He leverages his background both as a Chartered Accountant and Certified NetSuite Administrator to design and build NetSuite solutions that solve real world problems. Sonny is an active member of the NetSuite community, participating in local NetSuite meetups, NetSuite forums and groups focused on financial system optimization.

Be the first to post a comment.

Add a comment