Formula Block

This article explains the Formula block that can be used as part of App design.

Clappia supports multiple arithmetic operations (SUM, DIFF, PRODUCT, LOG...), logical operations (IF/ELSE, AND, OR, XOR, ...), string operations (CONCATENATE, LEN, TRIM, ...) and DATE/TIME operations (TODAY, NOW, DATEDIF, FORMAT) that are supported by Microsoft Excel.

To use this block, you don't need to learn any new language or syntax. Basic know-how of Excel is sufficient to use the Formula block in a Clappia app.

‍

Help video

Used for

• Checklist Scoring based on the inputs of users.
• Invoice calculations using the unit price, quantity, tax details etc

Editing the block

Click on the block and start editing on the panel that appears on the right side.

Label

This is what appears as the name of the input block to the end-user.

Formula

This is where the calculation logic needs to be defined. The formula can refer to other fields already defined in the app by using their variable names. Just type '@' and you will see a list of all variables that can be used in the formula.

Description

This is the text that goes below the input block to help the user.

Advanced Options

Hidden

Tick this option if you don't want to show the calculation output to the end user. The variable name of this formula field will still be available for use in other formulas. This field can serve as an intermediate variable in your complex calculation.

Example

This example shows how the Total price can be calculated based on the quantity of an item purchased and the price of an individual item. Additionally, if the quantity is greater than 100, we want to give a discount of 10% on the total price.

Create two input variables - Price and Quantity, using the single-line Text block.

Create a Formula block with the following formula (You don't need to type the variable names for price and quantity. Just type '@' to see a list of all available variables)

IF ({quantity}<100, {price_per_} * {quantity}, 0.9 * {price_per_} * {quantity})

App Home

Click on App Home to open the app and verify if the formula is correct.

Input the amount in the price and quantity fields. The total is calculated automatically.

‍

All Supported Formulae

This section lists all the formulae supported by Clappia. You can also refer to this sample Clappia app to see the formulae in action - https://marketplace.clappia.com/app/ALL624329

‍

‍

Simple Arithmetic Formulae

The examples below assume that the app already has fields with variable names number_one, number_two, number_three.

Addition:
- Using the '+' operator:
{number_one}+{number_two}+{number_three}
will not work if all arguments are not present.

- Using the SUM function:
SUM({number_one},{number_two},{number_three})
will work even if some arguments are not present.

Subtraction:
- Using the '-' operator:
{number_one} - {number_two} - {number_three}
will not work if some arguments are not present.

- Using the SUM function
SUM({number_one}, -{number_two}, -{number_three})

Multiplication:
- Using the '*' operator:
{number_one} * {number_two} * {number_three}

- Using the PRODUCT function:
PRODUCT({number_one}, {number_two}, {number_three})

Division:
- Using the '/' operator:
{number_one}/{number_two}

- Using the DIVIDE function:
DIVIDE({number_one},{number_two})

Rounding-off Formulae

The examples below assume that the app already has a field with variable name number_one.

Round off number to 2 decimal places:
ROUND({number_one},2)  

ROUND off a number to nearest 10:
ROUND({number_one},-1)

ROUND off a number to nearest 100:
ROUND({number_one},-2)

Round UP a number to its nearest 10:
ROUNDUP({number_one}, -1)

Round UP a number to its nearest 100:
ROUNDUP({number_one}, -2)

Round DOWN a number to its nearest 10:
ROUNDDOWN({number_one}, -1)

Round DOWN a number to its nearest 100: ROUNDDOWN({number_one}, -2)

Round off a number to its nearest multiple of 5:
MROUND({number_one},5) --

CEILING - Round UP a number to the nearest multiple of 10:
CEILING({number_one}, 10)
Works like MROUND but always rounds UP

FLOOR - Round DOWN a number to the nearest multiple of 10:
FLOOR({number_one},10)
Works like MROUND but always rounds DOWN.

CEILING.PRECISE (for negative numbers) - Round off a number to its nearest multiple of 3:
CEILINGPRECISE({number_one},2)
Works for negative numbers also, always rounds TOWARDS zero for negative numbers

CEILING.MATH (for negative numbers) - Round off a number to its nearest multiple of 3:
CEILINGMATH({number_one},3)  
Works for negative numbers also. Rounds towards zero if 3rd argument is not passed.

CEILING.MATH (for negative numbers away from zero) - Round off a number to its nearest multiple of 3:
CEILINGMATH({number_one},3, 1)
Works for negative numbers also. Rounds away from zero.

TRUNC - Truncates the number to 2 decimal places without rounding off:
TRUNC({number_one}, 2)

INT - Integer part of a number by rounding down to the integer:
INT({number_one})

String Formulae

The examples below assume that the app already has fields with variable names string_one, string_two and string_thr.

Uppercase:
UPPER({string_one})

Lowercase:
LOWER({string_one})

String Concatenation:
- Using 'CONCATENATE' function:
CONCATENATE({string_one}, " ", {string_two}, " ", {string_thr})

- Using '&' operator:
{string_one} & " " &  {string_two} & " " & {string_thr}

IF/ELSE Formulae

Using AND:
IF(AND({number_one}>5, {number_two}>5), "Numbers one {and} two both greater than 5", "One of the numbers one {and} two is {not} greater than 5")

Using OR:
IF(OR({string_one}="1", {string_two}="1"), "At least one of Strings one {or} two is equal to 1", "Both Strings one {and} two are {not} equal to 1")

Using NOT:
IF(NOT({number_one}=1), "Number one is not equal to 1", "Number one is equal to 1")

Using NE:
IF(NE({string_one},"1"), "String one is not equal to 1", "String one is equal to 1")

Logical Formulae

AND:
AND({number_one}=1, {number_two}=1)

OR:
OR({number_one}=1, {number_two}=1)

XOR:
XOR({number_one}=1,{number_two}=1)

BITAND:
BITAND({number_one},{number_two})

BITXOR:
BITXOR({number_one},{number_two})

BITOR:
BITOR({number_one},{number_two})

Date Formulae

The examples below assume that the app already has Date fields with variable names date_one and date_two.

Current Date:
TODAY()

Creating a Date variable from Day, Month and Year variables:
DATE(1988,8,17)

Getting the Day part of a date:
DATE(1988,8,17)

Getting the Month part of a date:
MONTH({date_one})

Getting the Year part of a date:
YEAR({date_one})

Subtracting X days from a date:
{date_one}-7

Adding Y days to a date:
{date_one}+30

1st day of next month:
DATE(YEAR({date_one}), 1+MONTH({date_one}),1)

Diff of two date variables (in days):
{date_two}-{date_one}

Diff of two date variables (in months):
DATEDIF({date_two},{date_one},'m')

Diff of two date variables (in years):
DATEDIF({date_two},{date_one},'y')

End of Month:
EOMONTH({date_one})

X months before a date:
EDATE({date_one},-5)

Y months after a date:
EDATE({date_one},5)

Week Day:
WEEKDAY({date_one})

Week Number:
WEEKNUM({date_one})

Time Formulae

The examples below assume that the app already has fields with variable names time_one and time_two.

Current Time:
NOW()

Add X Hours to a Time variable:
{time_one}+8*60
Adding 8 hours* 60 minutes to a time variable.

Time Diff of two time variables:
({time_two}-{time_one})/60
The formula time_two - time_one gives output in minutes, hence dividing by 60.

Formatted time:
TEXT({time_one},"HH")
Convert a time variable with value 09:24 to 09.

Mathematical Trigonometric formulae

PI
COS
SIN
TAN
COT
ACOS
ASIN
ATAN
ACOT
ACOSH
ASINH
ATANH
ACOTH

Other Mathematical Formulae

Absolute value of a number:
ABS({number_one})

Random number between 0 and 1:
RAND()

Random number in a range:
RANDBETWEEN(10,100)A

‍