Formulas in Search


You can use SuiteAnalytics Workbook to query your Netsuite data using the analytics data source, which offers advanced query capabilities. For more information about SuiteAnalytics Workbook and how you can recreate your existing saved searches to workbooks, see the following topics:

For advanced searches and saved searches, you can define formulas to be used in the following ways:


As of the 2023.2 release, results for Formula (Text) fields will only be displayed as plain text to improve the security of saved searches. To prevent issues with your searches, you should replace your Formula (Text) fields with the new Formula (HTML) field and check the Disable HTML in Search Formula (Text) box at Setup > Company > General Preferences. For more information about the new Formula (HTML) field available in your account, see Evaluating Code in Saved Searches Using Formula (HTML) Fields.

For more information about the Disable HTML in Search Formula (Text) preference, see Restricting HTML for Formula (Text) Fields.

Formula values are dynamically calculated at the time a search is performed. Formula definitions can include NetSuite field IDs for which field values are substituted, SQL functions, and mathematical operators.


Field values referenced in formula definitions may be translated based on a user’s language preference. For example, if a formula is created for a saved search using an English field value and the search is run by a user with a different language setting, the results of the search may vary. Consequently, you should be using internal field IDs when constructing formula definitions. This will ensure the intended results are displayed, regardless of a user’s language settings.


You cannot combine aggregate and non-aggregate SQL functions in the same formula definition. For more information about SQL functions, see SQL Expressions.

The following table shows the variables and corresponding synonyms available for your use in NetSuite.






Returns current date according to user’s time zone, date, and time formatting.


{user}, {‌}

Returns current user ID.



Returns current role ID.



Returns current user’s department ID.



Returns current user’s location ID.



Returns current user’s subsidiary ID.



Returns current user’s class ID.



Returns current user’s currency.

For example, on a Case Search form, you can:

For other examples and hints for using formulas in search, see Search Formula Examples and Tips.

HTML escaping is performed as needed on search results returned by formulas.


Knowledge of SQL will help you to fully leverage the flexibility and power of SQL functions to define complex formulas, but the Formula popup windows can help you to correctly define formula expressions. These popups include a Function dropdown list that lets you select SQL functions to be included in expressions, and Filter or Field dropdown lists that let you select field names and have their IDs included in expressions.

For more details, refer to SQL Expressions . For tables of NetSuite field IDs, you can also refer to SuiteScript Supported Records


You cannot use encrypted fields such as credit card numbers and social security numbers in formulas. Use of these fields causes invalid expression errors, because SQL cannot decrypt their encrypted values.

Related Topics

Running Searches
Searchable Record Types
Defining a Simple Search
Defining an Advanced Search
Exporting Search Results
Persisting Search Results
Emailing Search Results
Permissions for Searches
Popup Searches
Personalizing a Search Form
Mass Changes or Updates

General Notices