NetSuite Tips for Formulas in Saved Search

Formulas in NetSuite saved search are highly valuable as they enable users to create complex custom search criteria that are impossible to achieve with standard search fields. By using search formulas, you can manipulate the data in real-time and dynamically filter specific conditions and calculations. Instead of reviewing large amounts of data, save time and increase efficiency by automating search processes by quickly narrowing results.

NetSuite search formulas can be used to:
• Combine multiple search fields into one
• Apply calculations to search fields
• Utilize conditional logic to include or exclude results based on specific conditions
• Create ranges based on dates and date fields in NetSuite

How to Use a Custom Formula Field in a Search

Formulas in search
Define formulas for Advanced searches and Saved searches to be used in the following ways:

As part of a custom field that is defined to use a formula to set its value. These custom fields are available to be used as search criteria or in results columns like any other custom field.
As part of search criteria. The results returned are based on the calculated value of the formula field.
As a results column field. The displayed values are based on the calculated value of the formula field.

Updates to Formula Text Fields

Available Variables & Synonyms for NetSuite Search Results:

Variable	Synonym	Details
{today}	{now}	Returns current date based on users time formatting, date and time zone.
{me}	{user}, {user.id}	Returns current user ID.
{userrole}	{userrole.id}	Returns current role ID.
{user.department}	{userdepartment.id}	Returns current user’s department ID.
{user.location}	{userlocation.id}	Returns current user’s location ID.
{user.subsidiary}	{usersubsidiary.id}	Returns current user’s subsidiary ID.
{user.class}	{userclass.id}	Returns current user’s class ID
{usercurrency}	{usercurrency.id}	Returns current user’s currency.

If a custom formula field is returning fixed values instead of updating it with new data entered into the system, it could be because the field was configured to store the calculated value at the time the record was created. The "Store Value" checkbox in the custom field definition controls this behavior. To determine whether the value should be recalculated dynamically with each search, consult your system administrator.

How to use a Formula in NetSuite Search Criteria

1. On an Advanced or Saved search page, go to the Criteria subtab. Click on the Filter column and choose a Formula type.

Formula (Date)
Formula (Numeric)
Formula (Text)

The optimal formula format hinges on the intended calculation, as the method of verification differs based on the type of formula. For instance, expressions should be in formula (Text) vs numeric calculations being Formula (Numeric).

2. Define your formula in the Formula popup window, define your formula, and click Done.

Filter date formulas by specifying whether the date occurs before or after a certain date. Filter numeric formulas by checking if the resulting value is greater than or equal to the given criteria. There is a 1000-character limit per formula which will result in an invalid expression.

Search records are dynamically filtered based on the calculated value of the formula when the search is conducted. If you are looking for top-rated customer service and on-demand NetSuite help, Protelo can help. Protelo differentiators include direct access to their expert NetSuite consulting team, a fast-responding LiveChat for information, interactive NetSuite User Group, and a team that is fully focused on our client’s business success.

3. Click Submit to view your search results.

To avoid any HTML output from being rendered in Formula (Text) fields, you can check the Disable HTML in Search Formula (Text) box at Setup > Company > Preferences > General Preferences (Administrator). Check the Disable HTML in Search Formula (Text) box and click save.

How to Define, Customize, and Access a Saved Search

How to use a Formula in NetSuite Search Results:

To use a formula in a NetSuite saved search result, you can follow these steps:

Go to the NetSuite saved search page and create a new search or open an existing one.
Click on the "Formula (Numeric)" or "Formula (Text)" option in the "Results" section of the search.
Enter the formula you want to use to calculate a new field value based on the existing search fields. Assign a label to the new formula field and click done.
Click on the "Add" button to add the formula field to the search results. Click the Set Formula button to enable a popup that will assist in the correct entry of the formula expression.
Save the search by clicking Submit.

After completing these steps, your saved search will include a new column displaying the results of the formula calculation. When you run the saved search, the results will include the values of the existing search fields and the newly calculated field referring to the formula you entered.

The following formula types are available in NetSuite:

Formula Type	Example
Formula(Currency)	TO_NUMBER({amount})
Formula(Date)	TO_DATE('11/16/2020','MM/DD/YYYY')
Formula(Date/Time)	{now}
Formula(Numeric)	{field_id}
Formula(Percent)	% of total 30/100
Formula(Text)	TO_CHAR({metataghtml})
Formula(HTML)	TO_CHAR({metataghtml})

How to Merge Multiple NetSuite Search Fields With a Formula

Head to the NetSuite search page > create a new search or open an existing one.
In the "Criteria" section of the search, select the "Formula (Numeric)" option.
Enter a formula that combines the values of the search fields you want to merge.
Assign a label to the formula field and click on the "Add" button to add the formula field to the search criteria. Save the search.

Your NetSuite search will now have a new formula field that merges the values of multiple search fields into a single one. When you run the search, the results will include records that match the merged field value based on your other search criteria and filters.

How to Add a Formula Tag in Search Results:

Tags function as virtual fields that don't affect search but personalize the results according to the user's preferences and situation. Unlike formulas, tags retrieve outcomes based on the search user's context.

To add a formula tag in search results:

On the Saved Search page, go to the Results tab.
Add a Formula (Text) field with your preferred formula tag.
Click the Set Formula icon in the Formula column for the Formula (Text) results field.
Click the Set button to add the currency code to the saved search. The formula tag is added to the saved search.

Tag	Description
TODAY or NOW	Current time in the appropriate time zone of the logged user.
ME or USER or USER.ID	An internal key (ID) of the logged user.
USERROLE or USERROLE.ID	An internal key (ID) of the role of the logged user.
USER.DEPARTMENT or USERDEPARTMENT.ID	An internal key (ID) of the department of the logged user.
USER.LOCATION or USERLOCATION.ID	An internal key (ID) of the physical location of the logged user.
USER.SUBSIDIARY or USERSUBSIDIARY.ID	An internal key (ID) of the subsidiary of the logged user.
USER.CLASS or USERCLASS.ID	An internal key (ID) of the class of the logged user.
USER.CURRENCY or USERCURRENCY	A base currency for the subsidiary of the logged user.

What SQL Functions are Supported in NetSuite Search Formulas & Custom Formula Fields?

• Analytic and Aggregate Functions
• Numeric Functions
• Character Functions Returning Character Values
• Character Functions Returning Number Values
• Datetime Functions
• Case
• Sysdate
• NULL-Related Functions
• Decode

Updates to NetSuite Saved Search Using Formula HTML Fields

Formula (Text) fields that contain HTML code will only display results as plain text to enhance saved search security. Formula (HTML) fields can only be used as results fields to users with the appropriate permissions. If you would like to enable specific users to have access, go to the permissions tab and enable the Role in the Setup subtab.

Need NetSuite Customization Assistance? Get On-Demand NetSuite Support Today.

Protelo's certified NetSuite professionals are here to help you get the most out of NetSuite! Our team can provide expert guidance and support on-demand without support tickets or annual contracts. For more information on our NetSuite expert services or a customized NetSuite quote, contact our team today!

With decades of business-process expertise and extensive product knowledge under our belt, our consultants provide a single source of expertise to implement, customize, integrate, and refine your back-end operations.

Get NetSuite Services Today Contact Protelo

Follow us on Social for NetSuite tips, tricks and ERP fun:

YouTube, LinkedIn, Twitter, Pinterest, Instagram, and Facebook

NetSuite Blog