Formula user-defined fields calculate values based on other basic or user-defined fields. You can write formulas consisting of arithmetic expressions, alphanumeric, date and numeric functions, as well as conditional expressions.
When you work with Contact List entries, Accounts, or client service cases, you can specify that user-defined fields are mandatory if a rule is matched. Rules for mandatory user-defined fields are similar to the formulas in formula user-defined fields. A mandatory rule consists of a comparison statement. It may compare a field with a value or with another field. Or it may compare formulas using the same syntax as formula user-defined fields. Comparison statements can contain any of the elements included in formulas, including alphanumeric, date, and numeric functions.
Comparison statements in formula and mandatory rules
Rules for mandatory user-defined fields consist of comparison statements. Formula user-defined fields may also include comparison statements if the formulas include conditional expressions, using “If” statements.
You can create comparison statements with the following symbols.
- == to check that two values are equal to each other.
For example, [Category] == “Partner” verifies that the Category field is set to “Partner”.
- != to check that two values are not equal to each other.
For example, [Category] != “” verifies that the Category field is not empty (i.e. it has a value).
- to check that the first value is greater than the second value.
For example, [Annual Sales] > 20000 verifies that the Annual Sales user-defined field has a value greater than 20,000.
- < to check that the first value is less than the second value.
For example, [Contract Expiry Date] < [Last Service Date] verifies that the Contract Expiry Date field is earlier than the Last Service Date field.
- >= 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 are often combined with logical operators to add multiple statements together.
You can compare any values including alphanumeric, date, and numeric values. Alphanumeric values are compared by checking the alphabetical order of the string. When you compare values from table fields, they are treated as alphanumeric values.
Comparison statements are case sensitive. If you want to ignore the case of fields, include the Lowercase or Uppercase function in the statement. For example, [City] == "Vancouver" is true only if the value of the City field is set to "Vancouver". However, Lowercase([City]) == "vancouver" is true if the value of the City field is set to "Vancouver", "vancouver", or "VANCOUVER".
To compare a table field that contains multiple values, the comparison statement must include the same text that’s displayed in the Maximizer interface, including all values for the field. For example, the statement [Category] == "Partner" will be true only if the only value set in the Category field is "Partner". If the Category field includes the "Distributor" value as well, the rule would validate with the following statement: Category == "Distributor, Partner".
Logical operators
You can use the following logical operators to build more complex comparison statements in formula user-defined fields and in rules for mandatory fields.
- Or – Combines two comparison statements where either one of the statements is true.
For example, [Category] == “Partner” OR [Category] == “Distributor, Partner” is true if the Category field has either the “Partner” value alone or has both the "Partner" and the “Distributor” values.
- And – Combines two comparison statements where both of the statements is true.
For example, [Category] == “Partner” AND [Annual Sales] > 20000 is true if the Category field has the “Partner” value and if the Annual Sales field is greater than 20,000.
- Not – Negates a comparison statement.
For example, NOT ([Category] == “Partner” AND [Annual Sales] > 20000) is true in all cases where the previous example is false.
You cannot insert other formula user-defined fields into the formula.
Formula user-defined fields calculate values based on other basic or user-defined fields. You can write formulas consisting of arithmetic expressions, alphanumeric, date and numeric functions; as well as conditional expressions.
When you work with Contact List entries, Accounts, or Client Service cases, you can specify that user-defined fields are mandatory if a rule is matched. Rules for mandatory user-defined fields are similar to the formulas in formula user-defined fields.
A mandatory rule consists of a comparison statement. It may compare a field with a value or with another field. Or it may compare formulas using the same syntax as formula user-defined fields. Comparison statements can contain any of the elements included in formulas, including alphanumeric, date, and numeric functions.