Formula Expressions
From AgileApps Support Wiki
Contents |
About Formula Expressions
Formula expressions give you the ability to calculate dynamic values and apply dynamically-generated criteria to a search.
Formula expressions are used in these areas of the platform:
- Computed Fields in Reports
- Default Field Values
- Formula Fields
- Layout Rule#Settings
- Macro Visibility Criteria
- Rule Execution Criteria
- Rule Actions to add or update a record
- Validation Criteria
Building a Formula Expression
A formula expression is essentially equivalent to a spreadsheet formula. Formula expressions are built from fields, operators, functions, and values.
Typically, formula expressions are built using the buttons and drop down lists provided in the Formula Builder, but they need not be. A fully-formed expression can simply be typed or pasted into the builder's expression-box text area.
Values
- A number, date, or string you enter into the expression:
- A string or date needs to be enclosed in single quotes. For example: 'ABC Company'.
- You specify an empty string using two single quotes with nothing between them: ''
Fields
- The name of an object Field, a Computed Field, or a Referential Field (to reference a field in a related object).
Functions
- One of the built-in Formula Functions. Functions can nest to arbitrary depth.
- For example, to create a conditional expression, start by selecting the logical IF function from the drop down (which gives you a template), or else type in the conditional expression in a format like this:
- IF (test, value-if-true, value-if-false)
Note:
Unlike Filter Expressions, and/or logic in a Formula Expression is added using the functions AND(boolean_expression_1, boolean_expression_2) and OR(boolean_expression_1, boolean_expression_2).- Learn more: Formula Functions#Logical Functions
Operators
- Available operators are:
Operator Name Description Examples != Not Equal Evaluates if two numbers/strings/boolean values are not equal in value, and returns TRUE if the numbers/strings/boolean values are not equal Is the listed price not equal to $100? - Listed_Price != $100
&& Logical AND Compares two Logical Boolean expressions, and returns TRUE if both conditions are TRUE Is the total amount greater than $10,000 and the requested delivery date in the future? - total_amount > 10000 && delivery_date > TODAY (NOW())
> Greater Than Evaluates if a number is greater than another number, and returns TRUE if the condition is met Are sales greater than the quota of $10,000/month? - Quota> 10000
>= Greater Than or Equal To Evaluates if a number is greater than or equal to another number, and returns TRUE if the condition is met Are sales greater than or equal to the quota of $10,000/month? - Quota>= 10000
< Less Than Evaluates if a number is less than another number, and returns TRUE if the condition is met Are sales less than the quota of $10,000/month? - Quota< 10000
<= Less Than or Equal To Evaluates if a number is less than or equal to another number, and returns TRUE if the condition is met Are sales less than or equal to the quota of $10,000/month? - Quota<= 10000
( ) Parentheses or Brackets Creates groups of expressions Evaluates the expressions between the open and closed brackets before evaluating the parts of the expression outside of the brackets
Multiply the price by the number of units, then apply a discount to the resulting value - (Number_of_Units * Price) - Discount
* Multiply Multiplies two numbers Multiply the total amount due by a discount amount - Total_Due * Discount_%
+ Plus or Concatenate Has two functions:
- Calculates the sum of two numbers
- Joins multiple text strings into one text string
- Returns the sum of the tax and shipping:
- (SubTotal * Tax) + Shipping
- Creates a value formatted for Sorting (by last name)
- LASTNAME + ', ' + FIRSTNAME
- Minus Calculates the difference between two numbers Calculate the number of units yet to be delivered - Quantity_Ordered - Quantity_Received
/ Divide Divides a number by another number Divide a yearly sales quota by 12 to find a monthly quota amount - Quota/ 12
= Equal Evaluates if two numbers are equal in value, and returns TRUE if the condition is met Is the price equal to $100? - Price=$100
|| Logical OR Compares two Logical Boolean expressions, and returns: - TRUE if either of the conditions are TRUE
Is the total amount is greater than $10,000, or is the requested delivery date in the future? - total_amount > 10000 || delivery_date > TODAY (NOW())
Examples
Here are some examples of typical Formula Expressions.
Simple Formula Expressions
In a reservations application, there are fields for Creation Date, Check In Date, and Check Out Date. A formula field can calculate the number of days between the Creation Date and Check In Date. To create such a formula field, use the date fields and the DATECOMP formula function. DATECOMP will give a positive result when the later date is the first operand. It will give a negative result if the later date is the second operand.
Formula Expression | Description |
---|---|
DATECOMP(check_in_date, creation_date) | Compare the future Check In Date and the reservation Creation Date, and return the difference in the number of days |
DATECOMP(TODAY(), check_in_date) | To build a complex expression, nest the expressions. This example compares Today's Date to the Check In Date and returns the difference in the number of days. (TODAY returns the current date.) |
IF((DATECOMP(check_in_date, TODAY())<2), 'Rush', 'Standard') | A more complex example uses the logical formula function IF to return one of two text results: 'Rush' if the Check In Date is less than two days from today, and 'Standard' if the Check In Date is two days or more away. |
Complex Formula Expressions
Examples of complex Expressions.
Formula Expression | Description |
---|---|
age = (datesub (now(), DOB)) / 365.25 | calculates age, based on date of birth (DOB) |
discount = (pre_discount_amount * (2.5 /100)) + total_amount | calculates a discounted price |
IF(probability = 1, ROUND(amount * 0.02, 2), 0) | calculates the 2% commission amount of an opportunity that has a probability of 100%; all other opportunities have a commission value of zero. |
SUBSTRING(phone, 2, 4) + SUBSTRING(phone, 7, 9) + SUBSTRING(phone, 11, 14) | removes the parentheses, spaces and dashes from a telephone number in the US format (xxx) xxx-xxxx |
SUBSTRING(phone, 1, 3) + SUBSTRING(phone, 5, 7) + SUBSTRING(phone, 9, 12) | removes the dashes from a telephone number in the US format xxx-xxx-xxxx |
IF((STARTSWITH(phone,'(')), (SUBSTRING(phone, 2, 4) + SUBSTRING(phone, 7,9) + SUBSTRING(phone, 11,14) ), (SUBSTRING(phone, 1,3) + SUBSTRING(phone, 5, 7) + SUBSTRING(phone, 9, 12)) ) | checks a telephone number to see if it starts with an open parenthesis and if so removes the parentheses, dashes and spaces; otherwise, the expression removes the dashes from the telephone number |
IF(AND(IF(DATESUB(TODAY(), payment_due_date)>0, true,false), payment_status ='UNPAID'), 'PAYMENT OVERDUE', null) | determines if the payment due date is past and the payment status is "UNPAID"; if true, it returns "PAYMENT OVERDUE" and null otherwise |
IF(OR(category!='IT', AND (category='IT', amount <3000)),true, false) | checks for a department (IT department) and amount less than $3000; returns TRUE if the department is not "IT"; also returns TRUE if the department is "IT" and the amount is less than $3000 |
IF(AND(ISNEW(),IF(DATESUB(TODAY(), close_date)>0, true, false)), true, false) | checks new opportunities, returning TRUE if it has a close date that is in the future; returns FALSE if close date is in the past |
IF(OR(priority='High', status='New'), ROUND(DATESUB(NOW(), created_date)), 0) | returns the number of days a case has been open if the status is new or the priority is high; returns zero otherwise |