Calculation

In some widgets, formulas can be used to generate the value of the widget, so that you can use dynamic values instead of a constant default value.

Applicable Widgets

Input Box
Date & Time
Label

How to Use

The calculation tab can be opened by clicking on Widget Property Settings .

In the calculation tab, you will see the ‘Default formula’ area where you can customize your formula.

You can click on any blank area under ‘Default Formula’ to add an operator. An operator is an element that forms part of the formula. There are 5 types of operators. Click the button of the type of operator you want to use.

Type

Text

Definition

Any text, number or symbol hardcoded by App designer

Type

Date

Definition

Any text, number or symbol hardcoded by App designer

Type

Variable

Definition

System-defined variable

Type

Variable

Definition

Built-in functions, used together with other operators

Type

Variable

Definition

The value of a widget in the current page

Using a Date in DateTime Picker

For a Date & Time widget, there is one additional option in the calculation tab for DateTime data type.

A Date & Time widget has 3 data types, including Date, Time and DateTime. The DateTime data type has a timezone specifying the actual point of time in global matters, while others don’t.

When using a no-timezone Date type in the calculation formula of a has-timezone DateTime, this option provides extra control on the formula.

If a user with timezone GMT+8 and the calculation of a DateTime widget is a Date type with value 01/01/2025

Options

✘ Compute Date Picker as 12:00 AM in current user's time zone when using in the calculation below.

Displayed for user

01/01/2025 08:00 AM

Database value in GMT+0

01/01/2025 12:00 AM

Options

✔ Compute Date Picker as 12:00 AM in current user's time zone when using in the calculation below.

Displayed for user

01/01/2025 12:00 AM

Database value in GMT+0

31/12/2024 04:00 PM

Text Operator

After you click the ‘Text’ button, type your text, number or symbol in the input box and click apply.

A text operator is displayed in black color.

Date Operator

After you click the ‘Date’ button, choose a date from the calendar and specify the time. By clicking the ‘Clock’ button on the left of the ‘Confirm’ button, the calendar shows you the current date and time. After finishing, click ‘Confirm’.

A date operator is displayed in orange color.

The format of a date operator is in a DateTime data type. If it is used on an Input Box String, it will be displayed as a timestamp in milliseconds. If you want to display the year/month/day/time of the date operator in a string, use it together with a function operator.

Variable Operator

After you click the ‘Variable’ button, choose an operator from the list. You can search for an operator by typing in the input box.

A variable operator is displayed in red color.

Here are the definition and example of variable operators:

Operator

Now()

Definition

The current date and time during Run Time, in the format of DateTime data type.

Example

Formula: MONTH(Now())

Display Value: The current month in number during Run Time

Operator

Empty()

Definition

Make the field become empty during Run Time.

Example

Formula: Empty()

Display Value:

Operator

NewLine()

Definition

Break the content into a new line.

Example

Formula: A + NewLine() + B

Display Value:

Operator

TimeZone()

Definition

The current user timezone during Run Time.

Example

Formula: TimeZone()

Display Value: 28800 (GMT +08:00)

Operator

Environment ID()

Definition

The current Environment ID during Runtime.

Example

Formula: Environment ID()

Display Value: developing (In Developing Environment)

Function Operator

After you click the ‘Function’ button, choose an operator from the list. You can search for an operator by typing in the input box.

The function operator is displayed in green color.

Here are the definition and example of function operators:

+

Definition

Plus (for integer/ decimal data type) or

And (for string data type)

(For string data type, values of the operator(s) will be concatenated

Example

Formula: 12+6

Display value: 126 (in string data type) / 18 (in integer data type) / 18.00 (in decimal data type)

-

Definition

Minus

Example

Formula: 18-6

Display Value: 12 (in string/integer data type) / 12.00 (in decimal data type)

*

Definition

Multiply by

Example

Formula: 2*5

Display Value: 10 (in string/integer data type) / 10.00 (in decimal data type)

/

Definition

Divide by

Example

Formula: 10/5

Display Value: 2 (in string/integer data type) / 2.00 (in decimal data type)

,

Definition

Comma

(It must be used together with other operators)

(

Definition

Open bracket

(It is used to separate operators clearly and operators in a bracket will be prioritized)

Example

Formula: (9-5)*3

Display Value: 12 (in string/integer data type) / 12.00 (in decimal data type)

)

Definition

Close bracket

(It is used to separate operators clearly and operators in a bracket will be prioritized)

Example

Formula: (9-5)*3

Display Value: 12 (in string/integer data type) / 12.00 (in decimal data type)

MOD

Definition

Divide by and show the remainder

Example

Formula: 19MOD5

Display Value: 4 (in string/integer data type) / 4.00 (in decimal data type)

CEILING

Definition

Round up to the nearest integer

Example

Formula: CEILING(99.1)

Display Value: 100 (in string/integer data type) / 100.00 (in decimal data type)

FLOOR

Definition

Round down to the nearest integer

Example

Formula: FLOOR(99.9)

Display Value: 99 (in string/integer data type) / 99.00 (in decimal data type)

ABS

Definition

Take the absolute value

Example

Formula: ABS(-1)

Display Value: 1 (in string/integer data type) / 1.00 (in decimal data type)

ROUND

Definition

Round off to the nearest integer

Example

Formula: ROUND(2.5)

Display Value: 3 (in string/integer data type) / 3.00 (in decimal data type)

RAND

Definition

Randomly generate a number between 2 numbers, all inclusive

Example

Formula: RAND(1,10)

Display Value: 3 (in string/integer data type) / 3.00 (in decimal data type)

LEN

Definition

Count the number of characters, including space, of an Input Box with String type

Example

Formula: LEN(Dragonce)

Display Value: 8 (in string/integer data type) / 8.00 (in decimal data type)

AddYear

Definition

Add a number of years to a Date value

Example

Formula: AddYear(06/12/2025 03:00 PM,3)

Display Value: 06/12/2025 03:00 PM (in DateTime data type)

AddMonth

Definition

Add a number of months to a Date value

Example

Formula: AddMonth(06/12/2025 03:00 PM,1)

Display Value: 06/01/2026 03:00 PM (in DateTime data type)

AddDay

Definition

Add a number of day to a Date value

Example

Formula: AddDay(06/12/2025 03:00 PM,1)

Display Value: 07/12/2025 03:00 PM (in DateTime data type)

AddHour

Definition

Add a number of hour to a DateTime value, result displayed in DateTime data type

Example

Formula: AddHour(06/12/2025 03:00 PM,1)

Display Value: 06/12/2025 04:00 PM (in DateTime data type)

AddMinute

Definition

Add a number of minute to a DateTime value, result displayed in DateTime data type

Example

Formula: AddMinute(06/12/2025 03:00 PM,1)

Display Value: 06/12/2025 03:01 PM (in DateTime data type)

AddSecond

Definition

Add a number of second to a DateTime value, result displayed in DateTime data type

Example

Formula: AddSecond(06/12/2025 03:00 PM,60)

Display Value: 06/12/2025 03:01 PM (in DateTime data type)

EOMONTH

Definition

The end day of a month of a Date/DateTime value

Example

Formula: EOMONTH(06/12/2025)

Display Value: 31/12/2025 (in Date data type)

SOMONTH

Definition

The start day of the month of a Date/DateTime value

Example

Formula: SOMONTH(06/12/2025)

Display Value: 1/12/2025 (in Date data type)

YEAR

Definition

The year of a Date/DateTime value

Example

Formula: YEAR(06/07/2025 03:00 PM)

Display Value: 2025 (in string/integer data type) / 2025.00 (in decimal data type)

MONTH

Definition

The month of a Date/DateTime value

Example

Formula: MONTH(13/07/2025 03:00 PM)

Display Value: 7 (in string/integer data type) / 7.00 (in decimal data type)

DAY

Definition

The day of a Date/DateTime value

Example

Formula: DAY(13/07/2025 03:00 PM)

Display Value: 13 (in string/integer data type) / 13.00 (in decimal data type)

HOUR

Definition

The hour of a Date/DateTime value in 24-hour time system

Example

Formula: HOUR(06/07/2025 03:00 PM)

Display Value: 15 (in string/integer data type) / 15.00 (in decimal data type)

MINUTE

Definition

The minute of a Date/DateTime value

Example

Formula: MINUTE(06/07/2025 03:00 PM)

Display Value: 0 (in string/integer data type) / 0.00 (in decimal data type)

SECOND

Definition

The second of a Date/DateTime value

Example

Formula: SECOND(Now())

Display Value: The second of the current time when the widget is first loaded

WEEKDAY

Definition

The number of the day of a Date/DateTime value in a week

(Ranging from 1 to 7, starting from Monday)

Example

Formula: WEEKDAY(01/01/2025 03:00 PM)

Display Value: 3 (in string/integer data type) / 3.00 (in decimal data type) (Meaning Wednesday)

WEEKNUM

Definition

The number of the week of a Date/DateTime value in a year

Example

Formula: WEEKNUM(01/01/2025 03:00 PM)

Display Value: 1 (in string/integer data type) / 1.00 (in decimal data type) (Meaning this date is in the 1st week in year 2025)

NumOfSat

Definition

The number of Saturday between two Date/DateTime values, all inclusive

(The first parameter must be the earlier date)

Example

Formula: NumOfSat(Date1,Date2)

Display Value: The number of Saturday in between the date in ‘Date1’ and the date in ‘Date2’ (in string/integer/decimal data type)

NumOfSat

Definition

The number of Sunday between two Date/DateTime values, all inclusive

(The first parameter must be the earlier date)

Example

Formula: NumOfSun(Date1,Date2)

Display Value: The number of Sunday in between the date in ‘Date1’ and the date in ‘Date2’ (in string/integer/decimal data type)

SecToDay

Definition

Convert milliseconds into number of days

Example

Formula: SecToDay(86400000)

Display Value: 1 (in string/integer data type) / 1.00 (in decimal data type)

DTtoDate

Definition

Convert a DateTime value to Date value only

Example

Formula: DTtoDate(06/07/2025 03:00 PM)

Display Value: 06/07/2025 (in Date data type)

DTtoTime

Definition

Convert a DateTime value to Time value only

Example

Formula: DTtoTime(06/07/2025 03:00 PM)

Display Value: 03:00 PM (in Time data type)

CountStaffPicker

Definition

The number of user or group chosen in a ‘User & Group’ widget

(The number of users in a group will not be counted)

Example

Formula: CountStaffPicker(Staff)

Display Value: The number of user or group chosen in the ‘User & Group’ widget (in string/integer/decimal data type)

FillLenWithZero

Definition

Make up or truncate a number to a certain number of digit

Example

Formula: FillLenWithZero(5,3)

Display Value: 005 (in string data type)

Formula: FillLenWithZero(54321,3)

Display Value: 321 (in string data type)

toMon

Definition

Convert 1-12 to the short form of the respective month

Example

Formula: toMon(Month(01/01/2025 03:00 PM))

Display Value: Jan

toMonth

Definition

Convert 1-12 to the full form of the respective month

Example

Formula: toMonth(Month(01/01/2025 03:00 PM))

Display Value: January

toUpper

Definition

Convert a string value to UPPERCASE

Example

Formula: toUpper(example)

Display Value: EXAMPLE

toLower

Definition

Convert a string value to lowercase

Example

Formula: toLower (EXAMPLE)

Display Value: example

SubString

Definition

Extract certain numbers of characters from a string

Example

Formula: SubString(Example,3)

Display Value: Exa

Formula: SubString(Example,-3)

Display Value: ple

Left

Definition

Extract a positive number of characters from the left side of a string

Example

Formula: Left(Example,3)

Display Value: Exa

Right

Definition

Extract a positive number of characters from the right side of a string

Example

Formula: Right(Example,3)

Display Value: ple

Find

Definition

Find the position of the first variable that exactly matches in the second variable, counting from 1 and return -1 if not found

Example

Formula: Find(Example,AnExample)

Display Value: 3

Formula: Find(example,AnExample)

Display Value: -1

IndexOf

Definition

Find the position of the first variable that exactly matches in the second variable, counting from 0 and return -1 if not found

Example

Formula: IndexOf(Example,AnExample)

Display Value: 2

Formula: IndexOf(example,AnExample)

Display Value: -1

Trim

Definition

Remove the leading and trailing whitespace

Example

Formula: Trim( a b )

Display Value: a b

GetUserName

Definition

Return the User Name of a selected user configured in Admin Panel

Example

Formula: GetUserName(Staff)

Display Value: Alice Chow

GetUserLogin

Definition

Return the login email of a selected user

Example

Formula: GetUserLogin(Staff)

Display Value: alicechow@company.com

GetUserContactEmail

Definition

Return the contact email of a selected user

Example

Formula: GetUserContactEmail(Staff)

Display Value: alicechow@company.com

GetUserDirectLine

Definition

Return the direct line of a selected user

Example

Formula: GetUserDirectLine(Staff)

Display Value: 31234567

GetUserMobilePhone

Definition

Return the mobile phone of a selected user

Example

Formula: GetUserMobilePhone(Staff)

Display Value: 61234567

GetUserDescription

Definition

Return the user description of a selected user configured in Admin Panel

Example

Formula: GetUserDescription(Staff)

Display Value: Sales Department

GetUserTitle

Definition

Return the user title of a selected user configured in Admin Panel

Example

Formula: GetUserTitle(Staff)

Display Value: Manager

GetUserGrade

Definition

Return the user grade of a selected user configured in Admin Panel

Example

Formula: GetUserGrade(Staff)

Display Value: G7

GetUserStatus

Definition

Return the user status of a selected user

Example

Formula: GetUserStatus(Staff)

Display Value: ACTIVE (when it is an active account)/ SUSPENDED (when it is a suspended account)

GetUserID

Definition

Return the user ID of a selected user configured in Admin Panel

Example

Formula: GetUserID(Staff)

Display Value: S018

GetRawID

Definition

Return the raw user ID of a selected user provided by the system

Example

Formula: GetRawID(Staff)

Display Value: u1234567890

Widget Operator

After you click the ‘Widget’ button, choose a widget from the list. You can search for a widget by typing in the input box.

The function operator is displayed in blue.

Widget

(Form Name) unique key

Definition

The distinct record ID of a form

Example Formula

(Form Name) unique key

Display Value

1204

Widget

Table Name (count)

Definition

The number of records on a table, included for “Form Table”, “Reference Table”, “SubForm”,

“SubForm Table”

Example Formula

Table Name (count)

Display Value

Widget

Widget Name

Definition

Available to select for “Input Box”,

“Date & Time”,

“User & Group”

Available to locate in

“Form”,

“Page”,

“Value Stock”

Page updated

Report abuse