Formula Block

This article explains the 'Calculation & Logic' block (also known as 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.

Why Does the Formula Value Change After Submission?

If your formula produces a fixed calculation, the value will show the same value throughout. However, when your formula uses dynamic values (for example, generating a random number), the below behaviour becomes important to understand.

The Formula Block is recalculated every time the form is opened or reloaded. This means that the value submitted initially is not stored as a fixed/static value inside the formula field. Instead, the formula runs again whenever:

• A user fills the form in App Home
• A user opens an existing submission from the Submissions tab

Even if the result happens to be the same, the calculation is still being re-evaluated in the background.

This behaviour is important to understand when using formulas that depend on dynamic values.

Example – Random Number (OTP-style) Formula

Consider a formula using a random function (for example, using RAND() to generate a random number) that you use as an OTP-style value or a one-time random code:

• When the user fills the form, the Formula Block shows one random number in App Home.

• After submitting, if you open the same submission from the Submissions tab, the Formula Block runs again and generates a new random number, so the value you see there will be different from what the user originally saw.

This is expected behaviour because the Formula Block always recalculates; it does not store the earlier value by itself.

Workaround: Save the Formula Result as a Fixed Value

If you want to keep the original formula result unchanged, you can copy the value from the Formula Block into a normal field using an Edit Submission workflow. The normal field will then store the value as it was at the time of submission, and will not change on reload.

Below is one example using a random number:

1. In Design App, adding a Formula Block with a random formula (for example, using RAND() to generate a random number).
   Note: You can keep the Formula Block hidden from users if you don’t want them to see it directly.

2. Adding a Single Line Text field. This field will be used to store the final value.

3. In Workflows, adding an Edit Submission workflow node (in the New Submission Flow).

4. In the Edit Submission node, configuring it to update the Single Line Text field with the value from the Formula Block.

Now, when a new submission is made:

• The Formula Block will generate a random value.
• The Edit Submission workflow will run right after submission and copy that value into the Single Line Text field.
• When you open the submission later, the Formula Block may recalculate, but the Single Line Text field will still show the original random value that was stored at submission time.

You can use the same pattern for other use cases where you want to “freeze” a calculated value (for example, pricing, scores, or time-based calculations).

Using Formula Block Values in Edit Submission Workflows

The Calculations & Logic (Formula) block itself cannot be edited using an Edit Submission workflow, because its value is always driven by the formula defined in the block.

However, the calculated value from the Formula block can be used to update other editable fields using the Edit Submission node as seen in the above example.

You can use the value generated in a Formula block and copy that value into a Single Line Text, Number, or any other editable field using an Edit Submission workflow node.

This is commonly used when you want to store a fixed value that was originally calculated by a formula (such as a generated code, score, or price) so that it does not change when the submission is reopened.

‍

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()

Time in 12 hour Format
‍TEXT({time_one}, "hh:mm A")
Converts the time to show AM/PM.

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

‍