Table of Content
Still need help?
Request support
Request Support
Help
 / 
 / 
Data Processing Blocks
 / 

Formula Block

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

Formula Block

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.

Formula Block
Formula Block

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.

Formula Block

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.

Formula 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})

Formula Block

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.

Formula Block
Formula Block

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

Formula Block
FAQs
How will I know if the formula I have used is incorrect?

If the formula syntax is incorrect, an error message will appear where you enter the formula. You can cross-check the syntax by looking up standard Excel formulas online, as Clappia supports similar syntax.

How can I automatically display a user’s name in the app?

A logged-in user need not type their name manually. Add a Calculations & Logic Block and choose the variable that fetches the name of the person currently logged in (this is often called the submitter name). Clappia automatically pulls this information from their account details, so the name appears without any input. In the formula box, type in '@' followed by 'submittername', it will reflect as {$submitterName}.

How do I reference other fields in my formula?

Type @ in the formula box, and you’ll see a list of all available fields. Select the field you want, and Clappia will insert its variable name (e.g., {price} or {quantity}).

Can I add multiple conditions in a single formula?

Yes. You can nest IF statements or other logical operators like AND, OR, etc.Example:IF(AND({status}="Yes",{amount}>1000),"Approved","Review").

How do I get the formula for certain conditions?

For conditional logic, Clappia supports arithmetic, logical, and string operations similar to Excel. You can look up standard Excel formulas online to find the correct syntax.

How can I do custom calculations on the form?

Add the Calculations & Logic Block and create formulas using other field's variables in your app. For example, {quantity} * {price} will calculate a total cost automatically (if you have a field to input quantity and price and is named as such). To pull other field values, type '@' followed by the field name.

Can I use text along with numbers in formulas?

Yes. You can combine text and numbers by using the & operator.Example: "Total: " & {quantity} * {price} will display something like Total: 500.

Try our free plan
It will answer many more questions within just 15 minutes.