There are four choices for formula types when adding a new formula field: Formula – Text, Formula – Numeric, Formula – Currency, and Formula – Date.
Formula – Text is used mostly for text methods that can test, modify, combine, or manipulate text strings in some way. (This type also works for most math functions, so it's possible to combine a math operator with a text method in some instances.) An example would be:
| If([text1].IndexOf(" ") > [text1].IndexOf("-"), [text1], [text2]) |
This formula returns text2 if a space occurs before “-“ in text1. Otherwise, text1 is returned.
The methods and operators for text formulas are listed and described in the article Formulas: Text Methods.
Formula - Numeric is generally used for performing math operations or any type of
functionality that involves numbers. (Note that this type can also be used for text methods if
the output is a number.) An example is:
[text1].Length * 2 + 3 |
This doubles the length of text1 (number of characters) and adds 3.
The methods and operators for numeric formulas are listed and described in the article Formulas: Numeric Methods.
Formula - Currency uses the same basic math operations and methods as the Formula – Numeric type, except that it is based on currency instead of real numbers. Since this formula type is used almost exclusively for financial calculations, certain functions that are logarithmic, exponential, etc. should not be used for this formula type. Numerical data can be used in the calculation for multiplication, etc., but text methods are not recommended.
The methods and operators for currency formulas are listed and described in the article Formulas: Currency Methods.
Formula – Date has a unique set of operations where you can calculate the time period between two dates (TimeSpan) or calculate a new date from a given offset (AddTime). The numeric output for TimeSpan can be used as a numeric input for other types of formulas. The date output of AddTime can be used as a date input for the TimeSpan formula. There is also a TODAY() method that returns the today’s date. This can be used in the TimeSpan or AddTime methods if you need to return a value relative to today’s date.
The methods and operators for date formulas are listed and described in the article Formulas: Date Methods.