Using a Formula in Search Results

To use a formula as a search result:

  1. On the Results subtab of an advanced or saved search page, in the Field column, select the required Formula type.

    The following options are available (they are listed in alphabetical order, intermingled with field names in the Field column dropdown list):

    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})

    As when you define formulas for search criteria, the type of formula you choose should correspond to the type of calculation and validation required.

    Important:

    To avoid cross-scripting (XSS) vulnerabilities, search column results with formulas that contain <script> tags do not display script output. Do not use <script> tags in your search formulas. To avoid any HTML output from being rendered, you can check the Disable HTML in Search Formula (Text) box at Setup > Company > General Preferences. For more information, see Restricting HTML for Search Formulas.

  2. In the Formula field, define your formula and click Done, or click the Set Formula button to open a popup that can help you correctly enter the formula expression.

    Note:

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

    • Select from the Function dropdown list to include a SQL function in the formula expression.

    • Select a field name from the Field dropdown list to include its ID in the formula expression.

    • There is a 1000 character limit per formula expression. You may be able to preview a search with a formula containing more characters, but when you run the search invalid expression errors occur.

  3. Click Submit to view your search results.

For example, to add a search results column that displays the total number of days a case has been open, enter the formula {today}-{startdate}.

Related Topics

Formulas in Search
Using a Custom Formula Field in a Search
Using a Formula in Search Criteria
Using Formula Tags in Search Results
Restricting HTML for Search Formulas
Search Formula Examples and Tips
SQL Expressions

General Notices