https://www.sikich.com

Using Formula Values as Available Filters in NetSuite Saved Searches

Bob Huguelet

|

Jan 16 2019

|

3 min read

Saved Searches are a powerful tool to solve problems in NetSuite. You can add standard and custom fields as criteria and results to perform basic searches for customers, transactions, vendors, and just about any other type of record. If you want to add more complex criteria to your search, you can use Formulas to make your Saved Searches even more flexible. In this post, we will focus on another way to use Formulas—as Available Criteria in Saved Searches. Meaning, you can change your Formula criteria on the fly. Most searches can be accomplished with standard Available Filters, but this is a great trick to have in your back pocket.

Note: This is an advanced technique. If you are still learning about formulas and saved searches, please refer to NetSuite Help Center or other blog posts.

So the trick here is to add the same Formula type to the Available Filter tab on your Saved Search as on your Criteria tab. In the example below, I have added a Formula (Text) Criteria to only show Vendors where the {entityid} starts with “a.”

NetSuite saved search formulas

NetSuite saved search formulas

Now when I run the search, I can modify the Formula (Text) directly from the Available Filters section of the saved search.

NetSuite saved search formulas

NetSuite saved search formulas

The Saved Search will treat the value you enter in the Formula (Text) Available Filter as if it had been entered on the formula definition screen in the Criteria field.

NetSuite saved search formulas

This is a simple example to demonstrate the principle. The above could also be accomplished by simply using Vendor Name instead of a formula. It only makes sense to use this if you need to change the value for a more complicated formula.

For example, let’s say I wanted to search two fields at once for the same value. Perhaps my users sometimes put the Purchase Order number in the memo field on the vendor bill, and sometimes in the transaction number field. I can concatenate those two fields with a formula, then search the result of that formula for a value that I enter on the fly.

To do this, I use the CONCAT() formula and enter in a dummy value on the Criteria tab.

NetSuite saved search formulas

Once I save and run the search, I can change the Formula (Text) Available Filter to the PO number I want. Here I can see that someone entered the PO1134 in the memo field.

NetSuite saved search formulas

That’s all there is to it, once you are familiar with how this works you may find it useful or even essential in certain circumstances.

A couple of additional notes:

You can use two different Formula Types as Available Filters, for example Formula (Text) and Formula (Date).
If you add two Criteria with the same Formula Type, the Available Filter formula will apply to both
You cannot add the same Formula Type as an Available Filter twice.
You cannot use Formula (Numeric) as Available Filter.
If you’re feeling frisky, you can also pass formula values directly to the available filter field via the URL, potentially creating some interesting linked searches: https://system.netsuite.com/app/common/search/searchresults.nl?searchtype=Vendor&Entity_FORMULATEXT=b&searchid=xxxx

If you would like more information about using formulas values as available filters in saved searches or other information related to NetSuite, please contact us at any time! You can also learn about more great tips for NetSuite on our YouTube playlist or our other blog posts.

This publication contains general information only and Sikich is not, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or any other professional advice or services. This publication is not a substitute for such professional advice or services, nor should you use it as a basis for any decision, action or omission that may affect you or your business. Before making any decision, taking any action or omitting an action that may affect you or your business, you should consult a qualified professional advisor. In addition, this publication may contain certain content generated by an artificial intelligence (AI) language model. You acknowledge that Sikich shall not be responsible for any loss sustained by you or any person who relies on this publication.

About the Author

Bob Huguelet

Bob is a Consultant on Sikich’s NetSuite team. He has used NetSuite in industry for over three years. His expertise lies in designing optimal processes to meet customers’ business needs. Bob has extensive experience in real-world operations, distribution and integrations, training team leaders and end-users, and working with team-members and third parties to complete a project.