Formula fields allow you to perform calculations based on other field values. These calculations can be simple mathematical operations or complex string manipulations and logical evaluations.

​
Data Types and Formulas

Before starting to write formulas, understanding data types is crucial for ensuring your formulas work correctly. Different data types support different operations and functions. Common data types include:

  • Numbers: Integers or decimals, supporting arithmetic operations.
  • Text: String values, supporting concatenation and partial text operations.
  • Dates: Specifically formatted dates, supporting date calculations.
  • Boolean: True (TRUE) or false (FALSE), used for logical operations.

​
Referencing Fields

In formulas, you can reference other field values by their field names. Make sure to use the correct field names and note that field names are case-sensitive.

​
Formula Writing Practices

When writing formulas, you’ll use operators, functions, and field references. Here are some practical formula writing practices:

​
Using Operators

  • Addition (+): Used for calculating sums of numbers or concatenating strings.
  • Subtraction (-): Used for calculating differences between numbers.
  • Multiplication (*): Used for calculating products of numbers.
  • Division (/): Used for calculating quotients of numbers.
  • Modulo (%): Used for calculating remainders of numbers.

​
Applying Functions

Functions are predefined formulas that perform specific operations. For example, the SUM function can calculate the total of multiple values, while the LEFT function can extract a certain number of characters from a text field.

​
Handling Text

When working with text, you might need to use the following operations:

  • Concatenation: Use the & operator or CONCATENATE function to join two or more text strings.
  • Substrings: Use LEFT, RIGHT, or MID functions to extract parts of strings.

​
Logical Evaluation

Use the IF function for logical evaluation to return different values based on conditions. The format is:

IF(condition, value_if_true, value_if_false)

​
Complex Expressions

You may need to write complex expressions involving multiple operations and functions. In such cases, ensure you use proper parentheses to control the order of operations.

​
Formatting and Interactive Display

Formula output results can also participate in formatting and interactive display configurations, allowing output content to meet custom display requirements. Since formula outputs are dynamic, the available formatting and interactive display configurations are determined by the inferred result of the specific expression.

The field value type of a formula is statically inferred when entering the formula, without waiting for actual calculation with referenced values. Therefore, you can always select formatting and interactive display configurations immediately after entering the formula.

​
Debugging and Optimization

Once a formula is written correctly, it should execute without errors and return expected results. If a formula isn’t working as expected, you may need to debug it.

​
Debugging Tips

  • Check Data Types: Confirm that your operations and functions use the correct data types.
  • Verify Field References: Ensure referenced field names are correct.
  • Step-by-Step Testing: Break down complex formulas into smaller parts and test each part separately.

​
Performance Considerations

  • Avoid Redundant Calculations: If the same calculation is used in multiple places, consider storing its result in a separate field.
  • Limit Complex Functions: Some complex functions, such as those involving large data operations, may impact performance.
Previous
Formula Grammar
Next