How to Use Conditional Formulas in the Calculated Field
The article on the Calculated field provides an overview of the values you can use in the Calculated field of JetFormBuilder. In that article, we gave examples of basic mathematical operations like addition, subtraction, multiplication, and division. However, the Calculated feature is not limited to these arithmetic operations – it supports more complex calculation operations that will help you build conditions within the Calculated field’s formula.
- How Do Conditional Formulas Work?
- Using Ternary Operators in the Calculated Field
- Combining Several Ternary Operators in One Formula
How Do Conditional Formulas Work?
condition ? exprIfTrue : exprIfFalse
The primary condition is always followed by a question mark “?”, while the expressions are separated with a colon “:”.
In this way, you can set one value for the Calculated field if a specific condition returns true and another value if the condition returns false. Below, we will discuss how you can benefit from ternary operators and will look into some usage examples.
Using Ternary Operators in the Calculated Field
Using complex calculations, you can make the value of the Calculated field conditional. The most common case is to adjust the output of the Calculated field to the value the user enters into a different field in the form.
Let’s consider the following example. On a tour booking website, a filling form lets the users sign up for an additional paid excursion. The final price for a tour depends on the number of guests. The total price remains static if the number of people taking the tour is up to three. If the number of people exceeds three, the price increases by a set amount per every new person. This rule of price dependency can be built with ternary operators.
Let’s move to the form’s settings in the Block Editor. The first added block to the form is the Select field which has the name “number_of_visitors”. For the Select field, we added a set of options through the Manual Input source type. The list of options consists of seven items, each being a number from 1 to 7.
In the Select field, the user can choose the number of visitors joining the tour. The final value of the Calculated field will depend on this number.
The second added block in the Gutenberg editor is the Calculated field. Inside the input box of the field, we inserted this formula:
%FIELD::number_of_visitors% <= 3 ? 50 : ( %FIELD::number_of_visitors% - 3 ) * 20 + 50
As a result, the user will pay $50 if the number of guests is less or equal to 3; if the number is greater than 3, the user will pay an additional $20 for each new guest. The formula looks complex, but let’s look at each part.
“%FIELD::number_of_visitors% <= 3” – This is the main condition. It defines which formula from those that come next will be executed. This part tells: “If the field with the name number_of_visitors is less or equal to the value of 3, then…”
“? 50” – This value will be set as the final value of the Calculated field if the previous condition returns true. We can continue the rule we articulated earlier in the following way: “If the field with the name number_of_visitors is less or equal to the value of 3, then the final price is 50”.
“: ( %FIELD::number_of_visitors% – 3 ) * 20 + 50” – This is the formula that will be used to calculate the final value if the previous condition returns false. The formula calculates the price the user will pay for the tour if the number of chosen guests is higher than 3.
Take the number of visitors the user specified in the Select field and subtract 3 – this way we get the number of additional guests; then, multiply this number by 20 (“20”, in our case, is the price for each new guest); finally, add 50 to the total price, which is the price for the first three guests.
Now, the rule can be articulated like this: “If the field with the name number_of_visitors is less or equal to the value of 3, then the final price is 50; otherwise (if the value in the field number_of_visitors is greater than 3), the price will be calculated from the formula of the third part of condition”.
Each part of the condition can use formulas, static numbers, form field values, and meta field values.
Below is the final result on the front end. The price for the tour is $50 if the user selects three guests:
Then, the price for the tour increases by an additional $20 for each new guest:
Combining Several Ternary Operators in One Formula
In the Calculated field, there is a possibility to combine several conditions to calculate the final value. Assume that the price for a certain product depends on the product’s quantity. In this case, we need to integrate several conditions into one formula.
Let’s consider the following case. The price for products on an e-commerce website should be calculated by the following rules:
$1 for 250 units.
$0.75 for 250 – 500 units.
$0.50 for 500+ units.
The main task is to combine several conditions: “The price for the product is $1 if the number of units is less or equal to 250; the price is $0.75 if the number of units is greater than 250 but less or equal to 500; the price is $0.50 if the number of units is greater than 500”.
To build this type of form, add two fields to the block editor – a Calculated field that will contain the formula and a Number field that will be responsible for quantity input. Let’s name the Number field “quantity”.
That means the price in the Calculated field depends on the value of a Number field which has the name “quantity”. Finally, the solution is the following:
( ( %FIELD::quantity% <= 250 ? 1 : 0 ) + ( %FIELD::quantity% > 250 && %FIELD::quantity% <= 500 ? 0.75 : 0 ) + ( %FIELD::quantity% > 500 ? 0.50 : 0 ) ) * %FIELD::quantity%
There are several conditions in the above formula.
“%FIELD::quantity% <= 250 ? 1 : 0” – If the “quantity” field contains a value less or equal to 250, then the final value is 1; if not, the condition returns 0.
“%FIELD::quantity% > 250 && %FIELD::quantity% <= 500 ? 0.75 : 0” – If the quantity field contains a value higher 250 OR less or equal to 500, then the final value is 0.75; if this condition is not met, it returns 0.
“%FIELD::quantity% > 500 ? 0.50 : 0” – If the quantity field contains a value greater than 500, the final value is 0.50; if this condition is not met, it returns 0.
Finally, all these three rules are summed. Thus two of the three rules return 0, and only one rule returns the needed value. Then, this value is multiplied by the quantity number – “* %FIELD::quantity%”
This example describes a possible way to combine several conditions in one calculated formula. For more examples, check our tutorial on Customizing Tax Rates with the help of ternary operators.