Formula Field

Formula field values are calculated from a formula and are automatically updated when any fields used in the formula expression change value. You write formula field expressions in JavaScript. Platform executes them to compute a dynamic output that can be displayed on the screen, and can be used by other components.

Formula fields can be displayed in views, see Views, but cannot be used for sorting, totaling, and filtering of records in Views. Use an expression field instead to sort, total or filter. Formula fields execute immediately when you create them and upon any update to the values to which they refer, while expression fields only execute after an update operation.

The New Field screen for a formula field includes:

• Field Properties

  • Field Label — (mandatory) is displayed on the left from the field on view, edit, and new record pages.

  • View Header — (optional) is used in headers of views and reports. If not specified, the Field Label is used.

  • View Width — (optional) is used to set width (in pixels or %) of columns in views and reports. If not specified, the browser calculates the width of columns automatically.

  • Hide this field's label on UI pages — An optional checkbox for hiding the display label of the field on UI Pages.

  • Return Type — The return type, which can be Decimal, Currency, Integer, String, Boolean, Date or Date/Time.

    • The currency format (only for fields with a Currency return type).

    • The number of decimal places (only for fields with Decimal return type).

  • Formula Editor — Defines the JavaScript expression. You can use merge fields from the current object record and related records to return a dynamically computed value. This value can be numeric or strings. Strings can consist of plain text, rich HTML, or JavaScript code that will be executed on the client's browser. For more information about formulas, including examples, see Formulas.

   

• Advanced Field Properties — For more information, see Advanced field properties.

• Integration Name — The integration name is used to reference this field via merge fields or with Platform APIs.

• Field-Level Help — The Field-Level Help box allows you to define help text for the field that will appear in a popup when the user clicks the help icon next to the field.

Keep the following in mind when working with formula fields:

• To define loops, you can use the LOOP_BEGIN and LOOP_END tokens, as described in Iterating through records. However, this technique should only be used for a small number of related records. Looping through a large set of records using these tokens can degrade performance. It is better to use a pre-defined function or the Platform Query API to obtain the required record. See the list of methods available in the Query API.

• All merge tokens are replaced with the exact contents of the corresponding field's value before the formula logic is executed. For example, a text field included by a token {!myTextField} with a value of Hello World will be used as is without the quotes around it. Always remember to use quotes around any value where JavaScript requires them.
• To use dates:
  • In a formula: create a JavaScript Date instance by providing the merge token with quotes around it as the parameter:

    var myDate = new Date("{!myDateField}");

  • As the return type of a formula: return the full value of the JavaScript getTime() method. For example:

    return myDate.getTime(); 

• When using images and shared images in formulas, wrap them in quotes to avoid errors. For example, the following formula returns a different starred image (representing a star rating from 1 to 5) based on a picklist value. In this example, the tokens {!star0} through {!star5} refer to hosted images and {!rating} refers to a picklist:

  if ({!rating}==106469)
   return "{!star1}";
  else if ({!rating}==106470)
   return "{!star2}";
  else if ({!rating}==106471)
   return "{!star3}";
  else if ({!rating}==106472)
   return "{!star4}";
  else if ({!rating}==106473)
   return "{!star5}";
  else
   return "{!star0}";