formula-user-defined-fields

Overview

Formula user-defined fields calculate values based on other basic or user-defined fields. Because they are calculated from values of other fields; values for formulas fields can be viewed in entries but cannot be set manually.

When you create a new or modify an existing formula user-defined field; you specify the formula for the user-defined field. You can write the formula using existing basic and user-defined fields. Click the Insert Field button to select fields to add to the formula. Formula user-defined fields can return alphanumeric; date; or numeric values. You specify the type of return value while creating the user-defined field. The functions available depend on the types of fields that you want to manipulate.

You can add arithmetic expressions to your formulas. You can also add functions and conditional expressions to create more complex formulas.

Conditional Expressions for Formulas

You can create conditional expressions in your formula user-defined fields. Conditional expressions use the If expression to evaluate comparison statements and return values based on the results of the statements.

Conditional expressions use the following syntax:

If (Comparison Statement; True Value; False Value)

• Comparison Statement – Uses comparison operators to compare two values. Comparison statements can use the following operators:
  • == to check that two values are equal to each other.
  • != to check that two values are not equal to each other.
  • > to check that the first value is greater than the second value.
  • < to check that the first value is less than the second value.
  • >= to check that the first value is greater than or equal to the second value.
  • <= to check that the first value is less than or equal to the second value.

Comparison statements can be combined with logical operators:

• Or – Combines two comparison statements where either one of the statements is true.
• And – Combines two comparison statements where both of the statements is true.
• Not – Negates a comparison statement.

• False Value – The value of the expression when the comparison statement is false. You can include other conditional statements to supply different answers for different values.

Examples of Conditional Expressions

The following examples illustrate complex formula user-defined fields using conditional expressions.

Example 1

If ([Creation Date] > Date(2009; 7; 9); "New"; "Old")

This example compares a date field with a constant date; using the Date function to format the date properly. The formula returns an alphanumeric value. If the date is later than the supplied date; the field has a value of “New”. If the date is earlier than the supplied date; the field has a value of “Old”.

Example 2

If(Not(Search("Canada";[Country])>0 Or Search("USA";[Country])>0 Or Search(“Mexico”;[Country])>0);"Other";"North America")

This example inspects the Country field of an Address Book entry to determine if the entry is located in North America or not. It uses the alphanumeric Search function to look for the countries “Canada”; “USA”; or “Mexico”. If none of those strings are found in the entry’s Country field; the formula evaluates to “Other”; but if one of those strings is found it outputs “North America”.

Example 3

If (Dayofweek([Creation Date]) == 1; "Sunday";

If (Dayofweek([Creation Date]) == 2; "Monday";

If (Dayofweek([Creation Date]) == 3; "Tuesday";

If (Dayofweek([Creation Date]) == 4; "Wednesday";

If (Dayofweek([Creation Date]) == 5; "Thursday";

If (Dayofweek([Creation Date]) == 6; "Friday";

If (Dayofweek([Creation Date]) == 7; "Saturday"; " ")))))))

This example uses the Dayofweek function to get the day of the week expressed as an integer value for a date field. It compares the value with integer values and outputs a string representing the day of the week in. If it does not have a value; it outputs an empty string.