This article details** **how to configure** Automatic Calculation** in your apps. You can use the **Calculated** and **Text **field types for automatic calculation in an app. When a formula is specified in one of these fields, the value for the field is automatically calculated in the record.

**Calculated** field type:

- Calculations in the
**Calculated**field are treated as numbers. - Arithmetic operations - addition, subtraction, multiplication, division, exponentiation.
- Add to, or subtract from a date and time field.
- Calculate the number of days, hours, and minutes between two dates and times.

**Text** field type:

- Calculation results in the
**Text**field are treated as texts. - Arithmetic operations - addition, subtraction, multiplication, division, exponentiation.
- Concatenate/combine text from multiple fields in to one field.

Field types that can be used in formulas:

Text, Number, Calculated, Date and time, Date, Time, Created datetime, Updated datetime, Author, Last modified by

Configuring Automatic Calculation

To automatically calculate a **Calculated **field:

- In the app where you want to create a calculation, click the
**Gear wheel**to navigate to the**App Settings**. - Note the field codes of the fields that you wish to use in a formula of the calculation. To do this:
- Place the cursor over the on the field.
- Click
**Settings**. - Note the the value in
**Field Code**at the bottom of the Settings.

- Drag a
**Calculated**field type from the left field selection area to the form. - Place the cursor over the on this new field, and click
**Settings**. - Enter a
**Name**. - Enter a
**Formula**. For details on formulas, see**"Formulas"**below. - Select the
**display format**of the calculation result. - Define desired
**decimal places**to be displayed. - Choose an optional
**currency symbol**(i.e. $) - Click
**Save.** - Click
**Save Form.** - Click
**Update App**to apply the changes.

To automatically calculate a **Text **field:

- Follow steps 1 through 3 above.
- Drag a
**Text**field type from the left field selection area to the form. - Place the cursor over the on this field, and click
**Settings**. - Enter a
**Name**. - Select the
**Calculate automatically**check box. - Enter a
**Formula**. For details on formulas, see**"Formulas"**below. - Click
**Save.** - Click
**Save Form.** - Click
**Update App**to apply the changes.

**Calculated**and

**Text**fields. Available fields for formulas:

**Calculated**,

**Number**.

- Available arithmetic functions:
- Addition (+)
- Subtraction (-)
- Multiplication (*)
- Division (/)
- Exponentiation (^)

- To add numbers in two fields whose field codes are "NumA" and "NumB", formula =
**NumA + NumB** - To subtract 1000 from the number in the field whose field code is "NumA", and multiply the result by 2, formula =
**(NumA - 1000) * 2** - To cube the number in the field whose field code is "NumA", formula =
**NumA^3**

Representing Positive or Negative Numbers

Use unary arithmetic operators "+" or "-" to represent positive values or negative values. Available fields: **Calculated**, **Text**.

- To represent the negative number in the field whose field code is "NumA", formula =
**-NumA**.

**Calculated**.

- The following calculation can be performed:
- Calculate the number of days, hours, and minutes between two dates and times.
- Add to or subtract from a date and time.

- To calculate the difference between two date and time fields whose field codes are "DatetimeA" and "DatetimeB" respectively, formula =
**DatetimeA - DatetimeB** - To add one hour to the value of the time field whose field code is "TimeA", formula =
**TimeA + (1 * 60 * 60)**

Use the string operator "&" to combine text strings. The values in number, date, and time fields can be combined as texts. Available field: **Text**.

- To combine text from two fields whose field codes are "TextA" and "TextB" into one field, formula =
**TextA & TextB**. - To enter a text string in between fields whose field codes are "TextA" and "TextB", enclose it in double quotation marks ("), formula =
**TextA & " " & TextB**. When the value in "TextA" is "John" and "TextB" is "Smith", the result is displayed as "John Smith".

**Calculated**,

**Text**.

- To sum up values in a column of a table (in the example below, the "Approximate Hours" subtotal), whose field code is "NumA", the formula =
**SUM(NumA)**. - Note: The sub-total column Field type (ie: "Approximate Hours") must be either Calculated or Number.

**Text**.

- DATE_FORMAT(
*field code*,*date and time format*,*time zone*). The default value of "time zone" is the system time zone. - DATE_FORMAT(
*number*(*Unix timestamp*),*date and time format*,*time zone*)

Example:

- To format value in the field whose field code is "FieldA" with the format of YYYY/MM/dd hh:mm a, formula =
**DATE_FORMAT(FieldA, "YYYY/MM/dd hh:mm a", "system")** - When the value in the field whose field code is "FieldA" is " 2016‒04‒01 14:00".Then, the result is 2016/04/01 2:00 PM.

Date Formats:

Format | Description | Example (2016-04-01 12:09) |
---|---|---|

YYYY | four-digit year | 2016 |

MMMM | full name of the month | April |

MMM | three-letter abbreviation of the month | Apr |

MM | two-digit month | 04 |

M | month without zero padding | 4 |

dd | two-digit day | 01 |

d | day without zero padding | 1 |

a | AM or PM | PM |

KK | hour in the 12 hour clock (0 - 11) | 00 |

K | hour in the 12 hour clock (0 - 11) without zero padding | 0 |

hh | hour in the 12 hour clock (1 - 12) | 12 |

h | hour in the 12 hour clock (1 - 12) without zero padding | 12 |

HH | hour in the 24 hour clock (0 - 23) | 12 |

H | hour in the 24 hour clock (0 - 23) without zero padding | 12 |

kk | hour in the 24 hour clock (1 - 24) | 12 |

k | hour in the 24 hour clock (1 - 24) without zero padding | 12 |

mm | minute | 09 |

m | minute without zero padding | 9 |

ss | second | 09 |

s | second without zero padding | 9 |

Z | time zone offset without a colon | +900 |

ZZ | time zone offset with two digits | +09:00 |

ZZZ | time zone ID | Asia/Tokyo |

## Comments

Please sign in to leave a comment.