Define Calculations

This article helps you understand how to define mathematical calculation in your form design using Asite Form Designer.

Updated this week

Mathematical calculations can be defined in Asite AppBuilder Designer when using the 'Calculated Value' controls only within the form design.

Go to the properties panel of 'Calculated Value' control and click on icon corresponding to the 'Value' property. It will open a popup containing two tabs - 'System Field' and 'Calculated Field'.


Click here to learn about 'System Field' function.


Click on the second tab 'Calculated Field' which enables defining mathematical calculation.

Now, you have an option to choose any of the method for calculation - a) Basic Calculation (Using 'Calculator' Option) or b) Advance Query (Using 'Edit' link).

Please note here that calculation can be applied on values of numeric type only, hence a 'Textbox' field must allow integer or decimal values only for calculations to be applied on that particular field. Supported calculations include addition, subtraction, multiplication, sum and division.

To understand how to apply calculation in an form design, lets take an example of below purchase order form containing product details, quantity and rate, along-with a specific tax rate for each product and a discount that is applicable on the grand total value basis the product total and the tax total.

Below are the steps to design such an form:

1. Add a 'Repeating Table' control having field name as 'Repeating-Table' and containing 8 columns. Also, select 'Include Footer' checkbox in the properties to add an extra row as a footer.

2. In the 1st column, add a 'Textbox' field for user to enter serial number - having label as 'Serial Number' and field name as 'Serial_Number'.

3. In the 2nd column, add a 'Textbox' field for user to enter product description - having label as 'Product Description' and field name as 'Product_Description'.

4. In the 3rd column, add a 'Textbox' field for user to enter product quantity - having label as 'Quantity' and field name as 'Quantity'.

5. In the 4th column, add a 'Textbox' field for user to enter product rate - having label as 'Rate ($)' and field name as 'Rate', allowing only 'Integer' values.

6. In the 5th column, we will multiply the 'Rate' and 'Quantity' integer values that will be input by the user and display the resulting value.
To do this, add a 'Calculated Value' field named 'Basic_Price' and label as 'Basic Price ($)'. Here, click on icon and go to 'Calculated Field' tab in the next popup screen.


Under the 'Calculations' option, go to the 'Select or Type' option. Here, select the field name 'Rate' and click 'Add Field'.


Note: If there is a long list of fields, you can type in the initial characters to find the required field name easily.

Click on asterisk (*) to apply multiplication, then select the field name 'Quantity' and click 'Add Field'. The calculation formula will appear as follows:

Alternately, you can use the 'Advanced Query' option by clicking on 'Edit' link showing at top right which opens the query editor as shown below:

Here, you can view as well as edit the query directly in below format:

{Rate} * {Quantity}

Below is an understanding of the query in parts - to help you get an idea on how such a query can be edited if required:
{Rate} | A variable value of the user input for field name 'Rate'
* | An operator to apply multiplication
{Quantity} | A variable value of the user input for field name 'Quantity'

Click 'Ok' to continue.

7. In the table footer and under the cell below 'Basic Price ($)' in 5th column, we will calculate the sum of all values of 'Basic Price ($)' depending on the number of products entered, their price and quantity.
To do this, add a new 'Calculated Value' field named 'Total_Basic_Price' and label as 'Total Basic Price ($)'. Here, click on the icon and go to 'Calculated Field' tab in the next popup screen.


Under the 'Calculations' option, select the field name 'Basic_Price' then click 'Add Sum'.


Note: If there is a long list of fields, you can type in the initial characters to find the required field name easily.

Alternately, you can use the 'Advanced Query' option by clicking on 'Edit' link showing at top right and enter your query directly as shown below:

sum({Basic_Price})

Below is an understanding of the query in parts - to help you get an idea on how such a query can be edited if required:
sum | An operator to calculate sum value
() | Round bracket is a container to define sum of all available values
{Basic_Price} | A variable value of the user input for field name 'Basic_Price'

Click 'Ok' to continue.

8. In the 6th column, add a 'Textbox' field for user to enter tax percentage - having label as 'Tax (%)' and field name as 'Tax_Percentage', allowing only integer values.

9. In the 7th column, we will calculate tax amount based on the corresponding values of 'Tax (%)' and 'Basic Price ($)'.

To do this, add a 'Calculated Value' field having label as 'Tax ($)' and field name as 'Tax_Amount'. Here, click on the icon and go to 'Calculated Field' tab in the next popup screen.

Under the 'Calculations' option, multiply the 'Basic_Price' with 'Tax_Percentage' then divide it by 100 as shown below:

Alternately, you can use the 'Advanced Query' option by clicking on 'Edit' link showing at top right and enter your query directly as shown below:

{Basic_Price} * {Tax_Percentage} / 100

Below is an understanding of the query in parts - to help you get an idea on how such a query can be edited if required:
{Basic_Price} | A variable value of the user input for field name 'Basic_Price'
* | An operator to apply multiplication
{Tax_Percentage} | A variable value of the user input for field name 'Tax_Percentage'
/ | An operator to apply division
100 | A numeric value

Click 'Ok' to continue.

10. In the 8th column, we will calculate the product total based on the corresponding values of 'Basic Price ($)' and 'Tax ($)'.

To do this, add a 'Calculated Value' field having label as 'Product Total ($)' and field name as 'Product_Total'. Here, click on icon and go to 'Calculated Field' tab in the next popup screen.

Next, calculate the 'Product Total ($)' by adding the 'Basic_Price' and 'Tax_Amount' as shown below:

Alternately, you can use the 'Advanced Query' option by clicking on 'Edit' link showing at top right and enter your query directly as shown below:

{Basic_Price} + {Tax_Amount}

Below is an understanding of the query in parts - to help you get an idea on how such a query can be edited if required:
{Basic_Price} | A variable value of the user input for field name 'Basic_Price'
+ | An operator to apply addition
{Tax_Amount} | A variable value of the user input for field name 'Tax_Amount'

Click 'Ok' to continue. The form design should now look like below:

11. In the table footer and under the cell below 'Tax ($)' in 7th column, we will calculate the sum of all values of 'Tax ($)' depending on the number of products entered, their price, quantity and tax percentage.

To do this, add a new 'Calculated Value' field named 'Total Tax ($)' and label as 'Total_Tax'. Here, click on icon and go to 'Calculated Field' tab in the next popup screen.

Under the 'Calculations' option, select the field name 'Tax_Amount' then click 'Add Sum'.

Note: If there is a long list of fields, you can type in the initial characters to find the required field name easily.

Alternately, you can use the 'Advanced Query' option by clicking on 'Edit' link showing at top right and enter your query directly as shown below:


sum({Tax_Amount})


Below is an understanding of the query in parts - to help you get an idea on how such a query can be edited if required:
sum | An operator to calculate sum value
() | Round bracket is a container to define sum of all available values
{Tax_Amount} | A variable value of the user input for field name 'Tax_Amount'


Click 'Ok' to continue.

12. In the table footer and under the cell below 'Product_Total' in 8th column, we will calculate the sum of all values of 'Product Total ($)'.

To do this, add a new 'Calculated Value' field named 'Grand_Total' and label as 'Grand Total ($)'. Here, click on icon and go to 'Calculated Field' tab in the next popup screen.

Under the 'Calculations' option, select the field name 'Tax_Amount' then click 'Add Sum'.

Note: If there is a long list of fields, you can type in the initial characters to find the required field name easily.


Alternately, you can use the 'Advanced Query' option by clicking on 'Edit' link showing at top right and enter your query directly as shown below:

sum({Product_Total})


Below is an understanding of the query in parts - to help you get an idea on how such a query can be edited if required:
sum | An operator to calculate sum value
() | Round bracket is a container to define sum of all available values
{Product_Total} | A variable value of the user input for field name 'Product_Total'


Click 'Ok' to continue. The form design should now look like below:

Note - Calculation queries as used within the fields will be available to view from the design template as shown above as well as it can be previewed for each individual field from the properties panel for the respective 'Calculated Value' field.

13. Now, add a separate field below the repeating table with label 'Discount (5%)' and field name 'Discount' to calculate discount on this purchase order.

To do this, click on icon and go to 'Calculated Field' tab in the next popup screen.

To calculate the 'Discount (5%)', multiply the 'Grand_Total' with '5' (i.e. discount percentage) then divide it by 100 as shown below:

Alternately, you can use the 'Advanced Query' option by clicking on 'Edit' link showing at top right and enter your query directly as shown below:

{Grand_Total} * 5 / 100

Below is an understanding of the query in parts - to help you get an idea on how such a query can be edited if required:
{Grand_Total} | A variable value of the user input for field name 'Grand_Total'
* | An operator to apply multiplication
5 | A numeric value
/ | An operator to apply division
100 | A numeric value

Click 'Ok' to continue.

14. Next, add another field below the repeating table with label 'Net Value ($)' and field name 'New_Value' to calculate the net value of this purchase order.

To do this, click on icon and go to 'Calculated Field' tab in the next popup screen.

To calculate the 'Net Value ($)', subtract 'Discount' from 'Grand_Total' as shown below:

Alternately, you can use the 'Advanced Query' option by clicking on 'Edit' link showing at top right and enter your query directly as shown below:

{Grand_Total} - {Discount}

Below is an understanding of the query in parts - to help you get an idea on how such a query can be edited if required:
{Grand_Total} | A variable value of the user input for field name 'Grand_Total'
- | An operator to apply subtraction
{Discount} | A variable value of the user input for field name 'Discount'

Click 'Ok' to continue. The final form design should now look like below:

Note - Calculation queries as used within the fields will be available to view from the design template as shown above as well as it can be previewed for each individual field from the properties panel for the respective 'Calculated Value' field.

15. You can now save / publish the form as required. Click here for detailed help.



Did this answer your question?