br: en wp: en isocode: us real: post: 4256
15Sep, 2016
8Comments

Understanding and using the Calculation field

Automate your calculations with the Calculation element.

Presentation of the Calculation field.

Insert a calculation field and access its features: Go to the element options by clicking on the pencil.

Insert a calculation field and click on properties

Several options are available to you under the Properties tab:

  • Define how many digits after the decimal point will be included in your results.

configure calculation field properties

  • Define the type of results in your calculation: number or time.

Define the desired calculation format

  • By checking the Editable entry box, you can change the results of your calculation. For example, if you want to round the results.

Editable data entry option

Under the Calculation tab, you will define the fields to calculate.

Define the calculation method as well as the corresponding fields

The Calculation tool has several functions.

Simple calculation

Consider the following example: You want the price for an order to be automatically calculated.

In the back-office.

Example: Start by inserting an Input field and go to its properties:
Rename it according to the needs of your form and choose the type that you need. In our example, the Entry field will be called “Price” and will be a “decimal number” type.

Input field options for simple calculation

  • Insert as many Input fields as necessary to make your calculation and set the properties of these fields.
  • Then insert your Calculation field and go into its options.
  • Add the “Price” field in the ‘formula of calculation’.

Add the price field

Note : To define what multiplication is, you must enter ” * ” on the keyboard. Calculation signs are performed manually on the keyboard.

  • Multiply with another field, like “Quantity” as shown here.

Add a field to multiply withOnce completed, confirm and save your form.

On the mobile.

Example of calculation diplayed on a mobile device

Sum of a table

In the back-office.

Consider the example of an employee time sheet.

  • Create your time sheet form: A table with a list of participants and a numeric entry field where you specify the number of hours worked.

/!\ ATTENTION /!\ Specify clearly the “numeric” type.

Create a time sheet form

 

  • Add an external Calculation field to the table.

Add a calculation field

 

  • Define the field: According to the example, the field will make the sum of the “Hours worked” column.

Set up the calculation field

  • Confirm and save your form.

On the mobile.

example of an employee time sheet on the mobile application

Use the calculation field as a 'Reference' field

This feature enables it to work from the outset with a Referential list, but we would prefer to use the Reference element, which was developed expressly for this feature.

For those who already work with Referential lists and Calculation fields, this feature still works..

Idea: With the Referential and Calculation elements, you have the option to create a proper purchase order form. In order to obtain an identical document to your current purchase orders, you can create a Word template. Refer to the “purchase order” example, which is located in our forms library.

The uniq Number

The Uniq Number allows you to create a random line. As an example, we will use the Uniq number to create the P.O number of your Purchase Order.

Configuration.

  • Add a Calculation field and go in its option, ‘calculation’ tab.
  • In the drop-down menu, select ‘Uniq number’.

the uniq number

 

/!\ Don’t forget to confirm your settings and save your form.

On the mobile device.

the uniq number

the ItemRefNum

The ItemRefNum makes data reading easier. It is similar to the Reference field. It reads information contained in a given column. It is usually linked to an NFC Tag or an Input Field.

Let us imagine that you manage flats with your Inventory of fixture forms. Every flat has their own NFC tag. Inside, you can read the Flat number, landlord’s name, and his phone number.
To make the information reading easier, you linked the ItemRefNum to the NFC tag field.

Configuration.

  • Select the Calculation field and go in its option. You rename it « Flat number ».
  • On the Calculation tab, add the ItemRefNum function.
  • In its brackets, before the comma, add the NFC Tag (from the drop-down menu). Following the comma, add the column number that our Calculation field should read. In our example, we add 1.

the ItemRefNum

 

Note: The Modifiable entry option makes the field editable during user entry. By default, the Calculation field is not editable.

  • Repeat the operation for each of the columns that are present in your NFC tag. In our example, as we have three columns, we add two Calculation field and configure its features.

Repeat the operation 2

Repeat the operation 3

 

  • Do not forget to save your form!

On your mobile:

  • Open the Kizeo Forms app and select your Inventory of fixture forms.
  • Click on the NFC tag field.

Click on the NFC tag field.

  • Place your device close to the NFC Tag.
  • The information contained in the NFC Tag will appear in the field and in your Calculation fields: Flat number, Landlord and Phone number.

Remember: Reading will be by column!

The information contained in the NFC Tag will appear in the field and in your Calculation fields

To discover how to configure your NCF tag, do not hesitate to check our tutorial!

Conditional operators

You can condition your calculations through different operators:

List of operators

Arithmetic operators:

  • + : addition and concatenation of text
  • – : substraction
  • / : division
  • * : multiplication
  • % : modulo or remainder of Euclidean division

Comparison operators:

  • < : lesser than
  • <= : Less than or equal to
  • > : greater than
  • >= : greater than or equal to
  • == : equality
  • ! = : inequality
  • === : strict equality
  • !== : strict inequality

Logical operators:

  • && : logical operator AND
  • || : logical operator OR
  • ! : negation
  • ? : ternary operator. It is written in the form: ((condition)? Value_if_true: value_if_false)

Bitwise operators:

  • & : AND “bitwise”
  • | : OR “bitwise”

Examples:

Perform a calculation according to a checkbox

In order to perform a different calculation according to a checkbox, you can use the following formula:

  • Check box? my calculation or my text if my box is checked: my calculation or my text if my box is not checked.

In the example below, we want to apply a 5% discount if our box is checked.

Calculation based on checkbox field

For this, we test whether our apply 5% discount? box is checked or not. The deduction will be applied if the box is checked.

Calculation for checkbox

Give a value in a calculation if an element is selected in a list

In order to give a value in our calculation when an element is selected in a list, we use the following formula:

  • List == value code A? Value if true: Value if false

In the example below, we applied it to our calculation to offer VAT if New Customer is selected.

At first, we assign a code to our list, of the form code: list. This code will of course not be visible on the mobile device.

Define Label for a list

We then test if our new client has been selected.

Calculation for a list

Warning: if you add text, remember to remove the + which is generated automatically.

Perform operations with conditions - example: average

In the example below, we want to achieve a grade average of 0 to 10 and not to consider an item that is not applicable.

Average Calculation
In our Slider elements, we select the type Text and we add our different notes, for the Not applicable, we give it a code, -1:

0
1
2
3
4
5
6
7
8
9
10
-1:Not applicable

In our Calculation element, we test whether the non-applicable element is entered or not. For this we use the following formulas:

  • my_slider == -1?0:my_slider <- so, if Not applicable is selected, its value in the calculation will be equal to 0, otherwise the chosen value will be taken into account. This operation will allow us to know the selected note.
  • my_slider == -1?0:1<- thus, if Not applicable is selected, the value taken into account during the computation will be equal to 0; otherwise, it will be equal to 1. This formula will be used for the division to know how many items have been entered.

By repeating the first calculation for each slider element and then dividing by the second, we get the calculation below:

Formula for average based on Slider

Display a result if a field is equal to another field

In order to display a result when a field is equal to another field, we use the following formula:

  • Element A == element B? Value if identical: Value if different

In the example below, we applied it to two input fields to check if the email addresses are identical.

calculation to identify identical elements

Warning: if you add text, remember to remove the + which is generated automatically.

Using two conditions - example: display a result if a field is greater than, less than, or equal to another field

If you want to combine several conditions, you can use the following formula:

  • Our first condition? Value if true: Our second condition? Value if true: Value if false

If the first condition is false, we test the second, etc., until we reach the “Value if false”

In the example below, we test our A value and B value to find out if A is greater than, less than or equal to B. We use the following formulas:

  • Element A <element B? Value if true: Value if false
  • Element A> element B? Value if true: Value if false

Combined, we get the operation below:

Calculation based on various conditions

Warning: if you add text, remember to remove the + which is generated automatically.

OR Function (||)

To perform a calculation if a box is checked or if an item is entered, you can use this formula:

  • Our first condition || Our second condition? Value if true: Value if false

At first, we will test the first condition, if it turns out wrong, we will test the second, so on.

In the example below, we test our input field. If it contains 0, 2, 4, 6, 8 or 10, then Even will appear, otherwise it will be Odd.

calculation OR function

Warning: if you add text, remember to remove the + which is generated automatically.

Note: we could also have used the percentage (%) to perform this operation. The Percentage allows to display the rest during a division. Thus, if the number is even, the division will not have any remainder and the calculation will be equal to 0.

Odd or even calculation based on %

AND Function (&&)

To perform a calculation if a box is checked and an item is entered, you can use this formula:

  • Our first condition && Our second condition? Value if true: Value if false

In the example below, we wish:

  • Make a reduction of 10 € if the box New customer is checked
  • Add a 10% or 20% VAT if the respective boxes are checked.

Calculation of VAT
Thus, we test respectively:

  • If the box New customer is checked
  • If the box New customer and VAT 10% are ticked
  • If the box New customer and VAT 20% are ticked
  • If the 10% VAT box is checked
  • If the VAT 20% box is checked,

Calculation AND

Warning: if you add text, remember to remove the + which is generated automatically.

Do not hesitate to comment on this article!
All your questions, comments and suggestions are most welcome!


There Are 8 Comments

  • Riccardo

    03 Apr, 2019

    Dear Adeline Lucarelli,

    Thanks for you kind answer. I understand that the software uses the trailing zero the same way spreadsheets use apostrophe.

    The zeroes are in some cells by default. The users are more than informed that they have to delete the zero before writing the next value: it is them who reported that the action of “deleting nothing” (deleting the zeroes) is redundant and annoying.

    Alternatively, is there an option so that, when I click on a cell, all the contents get selected, and whatever I write overwrites the older content? This way, the trailing zeroes would get automatically overwritten by the new numbers.

    Thank you in advance.

    • Adeline Lucarelli

      04 Apr, 2019

      Good morning Mr Felletti,

      I understand that the removal of 0 can be annoying for your users, but unfortunately we do not have a field that automatically crush the 0 before a number.
      I remain at your disposal if you need.
      Sincerely

  • Riccardo

    01 Apr, 2019

    We have a problem with trailing zeroes. For some reason, the software is unable to understand that (for example) “042” and “42” are the same number; “042” is treated as a text string.

    EXAMPLE: if in three cells we have “25”, “025” and “05”, the result we get is “2 502 505” instead of the correct 55.

    Deleting the zero before typing the data in dozens of cells in a row is an annoying waste of time: we would really need to find the other solution.

    • Adeline Lucarelli

      01 Apr, 2019

      Hi,
      This operation is voluntary.
      We have customers using the calculation field to make phone numbers for example, so we need the 0 to be kept and numbers to be cumulated.
      We understand your need but it would be necessary that your users do not use the 0 before a number. To inform them, you can open your field and go in the “advanced” tab to use the following Regular expressions input mask: ^[1-9][0-9]*$
      I remain at your disposal if you need.
      Sincerely

  • Mark Strickland

    05 Jan, 2019

    Hi,
    I am trying to export a calculation field in an excel report (i.e ##calculation##) by nothing appears. Is there a way to export calculations?
    TIA

    • Sarah Marivaux

      07 Jan, 2019

      Dear Mark.

      With the correct tag, your calculation field should appears.
      Could you send the name of your form to support[@]kizeo.com in order to have a look on your template?

      Best regards,

  • Bram Jansen

    31 Jul, 2018

    Hi,

    I’m trying to calculate the next date my items need to re-inspected. The format i’d like to use is (Date field)+ 365 (days) or 12 (months.)
    Is there a way to make this happen?

    • Sarah Marivaux

      31 Jul, 2018

      Hi,
      Unfortunately, it is not currently possible to calculate a date.
      The only possibility is to use your Date & Time field and the Default date option (In a year time).

      Best regards,

Post a comment

Your email address will not be published. Required fields are marked *