Creating Formula Fields

In addition to defining a custom field that's populated with dynamic data as described in Dynamic Defaults and Dynamic Hyperlinks, you can define fields to be dynamically calculated based on the values returned in the dynamic fields.

To define formula fields, click the Validation & Defaulting subtab of the custom field. Check the Formula box. In the Formula field, use NetSuite Tags to define the dynamically defaulted fields to be used in the calculation and use SQL Expressions to define the formula.

Validation & Defaulting subtab with Formula box checked.

To dynamically recalculate a formula, clear the Store Value box, and if needed, use the NULLIF function in your formula to prevent division by zero.

Important:

The Formula box must be checked for the field to be processed as a formula. Also, as with any defaulted field, the Store Value box must be cleared to dynamically recalculate the value each time the field is viewed. Additionally, when a record is loaded, custom formula fields are calculated. However, if you change fields used in the formula definition while the record is loaded, the formula field won't recalculate until you reload the record.

Note:

If the formula field doesn't have the Store Value box checked, the field isn't available in search results, including lists based on saved searches. If you have a use case for running a saved search on a formula field where the value isn't stored, there's an alternative solution available. For more information, see Running a Saved Search on a Formula Field Where the Value is Not Stored (SuiteAnswers ID: 1017388).

For more information about storing values, see Setting the Store Value Field.

You might get these inline errors during validation:

ERROR: Field Not Found - returned when either a custom field or search formula isn't recognized.

If you get a Field Not Found error, ensure that the user or role has the necessary access level in the Default Level for Search/Reporting list and permissions table. You can find these settings on the Access subtab of the custom field record.
ERROR: Invalid Formula - returned when there's a syntax or data type error in the custom formula field.

When custom formula fields appear in search results, they show the dynamically calculated value at the time of the search. You can also define search criteria as formula fields without using a custom formula field. For more information, see Formulas in Searches.

Warning:

If a field on a record is referred to by a formula custom field, you can't edit the referenced field with inline editing.

Note:

Knowledge of SQL will help you to fully leverage the flexibility and power of SQL functions to define complex formulas, but the Formula popup window can help you to correctly define formula expressions. The popup includes a Function list that lets you select SQL functions to be included in expressions, and Filter or Field lists that let you to select field names and have their IDs included in expressions. For more details, refer to SQL Expressions. Also, you can refer to the SuiteScript Reference Guide for tables of NetSuite field IDs.

For additional information, see the following topics:

Related Topics