Formulas in Searches
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 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. See Using a Custom Formula Field in a Search.
For detailed information about how to define formulas in custom fields, see Creating Formula Fields.
-
As part of search criteria. The results returned are based on the calculated value of the formula field. See Using a Formula in Search Criteria.
-
As a results column field. The displayed values are based on the calculated value of the formula field. See Using a Formula in Search Results.
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. For more information about the new Formula (HTML) field available in your account, see Evaluating Code in Saved Searches Using Formula (HTML) 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.
|
Variable |
Synonym |
Description |
|
{today} |
{now} |
Returns current date according to user’s time zone, date, and time formatting. |
|
{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. |
For example, on a Case Search form, you can:
-
Define the formula {today}-{startdate} to return a case's number of days open as a new column in search results.
-
Define the formula ({today}-{startdate}) > 3 as a search filter to return only cases open for greater than 3 days.
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 popup windows 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.