You are here

Using the Ternary Operator in Configured Fields

Perform conditional calculations on your cost data

Configured fields in Connected Cost support conditional logic during calculation, somewhat like a simple IF statement in spreadsheets. Our calculated fields use the ternary operator, following the syntax in Java. This means you can use conditions to test other field values to affect how a calculation is performed.

The condition is simply an if statement that must either be true or false. It may include other calculations, numbers or other data types, as well as AND and OR conditions.  Then based on the condition, the value_if_true or value_if_false is executed and the result is returned.

Value_if_true and value_if_false can be calculations, numbers, or other data types supported depending on the configured field type. 

The syntax is important. The condition must be followed by ‘?’, and the true and false values must be separated by ‘:’.

All 3 parts are required for the formula to calculate correctly, even if you only want a value when the condition is true (or false). In this case, you can make the value_if_false 0 or “” to reflect a 0 or blank value.

Ternary logic is derived from programming languages and uses very specific notation. General information about ternary logic can be found here.

An example of a configured field with ternary logic is shown below.

ternary example calculation


The condition part of the formula will always compare two (or more) things and must result in a true or false value. You can use existing fields from Cost in the condition, as well as numbers and text. Use the Select Field & Insert button on the toolbar to insert the fields you want to work with.

Note: You can only compare data of the same type. i.e. numbers can be compared to numbers, and text can be compared to text. A condition like: 5 > ‘Five’ will result in an error.

A simple example for a control account configured field is:

cost.estimate_at_completion > cost.approved_budget

This condition will return true if the estimate at completion is greater than the approved budget for each row.

You can have multiple conditions in a single configured field by enclosing each one in parenthesizes: () and using double ampersand && to represent and, or use double pipe || to represent or. Pipe is on most western english keyboards by pressing shift+backslash.

For example:

(cost.estimate_at_completion > cost.approved_budget) && (contract.current_amount > 20000)

This condition would return true if both the estimate at completion is greater than the approved budget and the contract current amount (for the control account) is greater than 20,000. If either of these conditions are not met, the condition will return false.

Similarly in the example:

             (cost.estimate_at_completion > cost.approved_budget) || (contract.current_amount > 20000)

The condition uses an or ‘||’ which will return true if either estimate at completion is greater than the approved budget or the contract current amount (for the control account) is greater than 20,000.

These examples all use the greater than test ‘>’ but other operators are available:

==           Equal to

!=            Not equal to

>             Greater than

>=           Greater than or equal to

<             Less than

<=           Less than or equal to

Value if True and Value if False

The value_if_true and value_if_false parts are the data or calculation that will be performed based on the result of the condition.

These can be simple calculations, numbers, or other data types depending on the Configured Field type.

An example of a calculation is simply

cost.estimate_at_completion * 1.1

Which would return a number (currency) value of the estimate at completion plus 10%.

Standard math operators can be added using the keyboard or the buttons in the toolbar.

These calculations can involve multiple fields or static values.

Both the  value_if_true and value_if_false fields are required for the ternary logic to be valid. If you only require a calculation when the condition is true, you would need to specify the false value as 0. For example:

cost.estimate_at_completion > cost.approved_budget ? cost.estimate_at_completion * 1.1 : 0

This configured field will return the Estimate at Completion plus 10% if the Estimate at Completion is greater than the budget, otherwise it will return 0.

Other Notes

When working with ternary logic, ensure you use the Validate Formula button to test your calculation is valid. Please note this only tests if the logic is programmatically valid, not whether the condition or results are what you intended.

Was this article helpful?

Thanks. A ticket has been opened with the Support Central team.