1. SuiteAnalytics
  2. Search
  3. Formulas in Searches
  4. Using a Formula in Search Results

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)

    • {quantity} || 'x ' || {item}

    • CASE WHEN {duedate} < CURRENT_DATE THEN 'Overdue' ELSE 'On Time' END

    Formula(HTML)

    • '<a href="/app/common/entity/entity.nl?id=' || {‌entity.id} || '" target="_blank">' || {entity} || '</a>'

    • NVL2({approvalstatus}, '<span style="color:green;"> Approved</span>', '<span style="color:red;"> Not Approved</span>')

    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 improve the security of saved searches, results for Formula(Text) fields are displayed as plain text only. To prevent issues with your searches, you should replace your Formula(Text) fields with the Formula (HTML) field. For more information about displaying HTML code in your search results, see Evaluating Code in Saved Searches Using Formula(HTML) Fields.

  2. In the Formula field, define your formula and click Done, or click the Set Formula button to open a popup window 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

General Notices