Complex Expressions
From LongJump Support Wiki
Revision as of 20:12, 10 February 2010 by imported>Evelyn (added DOB calc)
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 |