Conditional calculations

You can use the ternary operator in calculated fields if you need to base your desired calculation off of another field value. Conditional calculations only work in numeric calculations and can not be used to compare text fields.

Syntax

Condition ? True : False

True/False can be individual calculations, or can be standalone values. Condition should be a less than, greater than, or equal to comparison, or a combination of these comparisons.

• The || symbol separates the 2 conditions.
• The : symbol separates the True and False results.
• The (0) returns the number 0. You could replace this with a shortcode if you wanted a dynamic result instead like this ([245]).

Examples

Calculate flat rate below a certain price

One way to use a conditional calculation is by calculating a flat rate below a certain price, or a percent rate above that price. Consider the example below:

([15] > 500) ? ([15] * .02) : (10)

This calculation is entirely valid, and is saying IF field ID 15 is greater than 500, calculate the value of field ID 15 multiplied by .02, or 2%. ELSE add a flat value of 10.

Extending this example we can create multiple conditions:

([15] > 500 || [15] < 50) ? ([15] * .02) : (10)

This says, if field ID 15 is greater than 500 or less than 50, a percentage rate will also be charged, rather than a flat rate of 10. Conditions can also be connected && (and).

Calculate total cost per number of units

One way to use conditional calculations is by calculating the total cost depending on the number of items. For example, you have the following ranges:

• 0 - 500 items at $4.00 per unit
• 501 - 1000 items at $2.00 per unit
• 1000+ items at $1.00 per unit

You can use a conditional calculation to set this up.

1. Add a number field called Items, where you can enter the number of items. Set its default value to 1.
2. Add another number field and call it Total. Add the following conditional in the Default Value (Calculation) box and select the Format calculation as currency checkbox.

   ([90] > 1000) ? (1 * [90]) : ([90] > 500) ? (2 * [90]) : (4 * [90])

   Replace 90 with the ID of the Items number field.

3. This means:
   • IF field ID=90 is greater than 1000, calculate the total value by multiplying field ID=90 by $1 price per unit.
   • ELSE IF field ID=90 is greater than 500, calculate the total value by multiplying field ID=90 by $2 price per unit.
   • ELSE calculate the total value by multiplying field ID=90 by $4 price per unit.