Using Formula Values as Available Filters in NetSuite Saved Searches

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

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

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.

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.

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.

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.