Automatic Calculation

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. 

Fields you can Calculate Automatically:

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:

  1. In the app where you want to create a calculation, click the Gear wheel Screen_Shot_2017-07-19_at_9.43.03_AM.png to navigate to the App Settings.
  2. 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.
  3. Drag a Calculated field type from the left field selection area to the form.
  4. Place the cursor over the on this new field, and click Settings.
  5. Enter a Name.
  6. Enter a Formula. For details on formulas, see "Formulas" below.
  7. Select the display format of the calculation result. 
  8. Define desired decimal places to be displayed.
  9. Choose an optional currency symbol (i.e. $)
  10. Click Save.
  11. Click Save Form. 
  12. Click Update App to apply the changes.

To automatically calculate a Text field:

  1. Follow steps 1 through 3 above.
  2. Drag a Text field type from the left field selection area to the form.
  3. Place the cursor over the on this field, and click Settings.
  4. Enter a Name.
  5. Select the Calculate automatically check box.
  6. Enter a Formula. For details on formulas, see "Formulas" below.
  7. Click Save.
  8. Click Save Form. 
  9. Click Update App to apply the changes.
Formulas
 
Calculating Numbers
 
You can calculate formulas using numbers in both the 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. When the number in the field whose field code is "NumA" is "100", the result is "-100".
Calculating Times
 
Times must be specified in seconds. For example, one hour is specified as 3,600 seconds. Available field: 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)
Concatenating Text from Multiple Fields 
 

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. When the value in "TextA" is "John" and "TextB" is "Smith", the result is displayed as "JohnSmith".
  • 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". 
SUM function
 
You can use the SUM function to sum up numbers or dates that are located in a table. Available fields: 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.Sep-07-2017_10-33-46.gif
DATE_FORMAT
 
This function enables you to specify the format for the date used in the formula. Available field: Text.
 
The following syntaxes are available:
 
  • 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
Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.