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.

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

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

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

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.

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

**Advanced Options**

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.

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.

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

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

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

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}

**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")

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

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

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.**

**PICOSSINTANCOTACOSASINATANACOTACOSHASINHATANHACOTH**

**Absolute value of a number:**

ABS({number_one})

**Random number between 0 and 1:**

RAND()

**Random number in a range:**

RANDBETWEEN(10,100)**A**

What formulas are supported?

Clappia supports multiple arithmetic, logical, and string operations that are supported by Microsoft Excel.

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

If the syntax is incorrect, there will be an error message that will appear where you type it in to let you know that it is incorrect. You can check this link for the right formula <a heref ="https://www.clappia.com/help/using-formula-block"></a> or you can check online as we support Excel formulas.

How can I add a field in the Clappia app to get the user's name?

In Clappia, you can use the Calculations & Logic Block to automatically retrieve the signed-in user’s name. Within this block, type @current user name or @submitter name to access the name associated with the user’s email or phone number in the workplace. This variable pulls the user’s information directly from their Clappia profile, allowing for seamless integration of user-specific details into the app.

How do I get the formula for certain conditions?

You can check the syntax from this link: <a href ="https://www.clappia.com/help/using-formula-block"></a>

Or you can always search on your browser for the correct syntax as Clappia supports arithmetic, logical and string operations similar to Excel.

How can I do custom calculations on the form?

To perform custom calculations on the App data, you can use the 'Calculations & Logic' Block. This allows you to create Excel-like formulas using other variables in the App. To learn how to set up custom calculations, visit the Help Guide article on the <a target='_blank' href='https://www.clappia.com/help/using-formula-block/'>Formula Block</a>.,

It will answer many more questions within just 15 minutes.

Offices

India

140L, 5th Main Rd, Sector 6, HSR Layout, Bengaluru, Karnataka 560102, India

+91 96418 61031

usa

3500 S DuPont Hwy, Dover,

Kent 19901, Delaware, USA

+1 (341) 209-1116

© 2017-2024 Clapptron Technologies Private Limited. All Rights Reserved.

Our Global Office

usa

3500 S DuPont Hwy, Dover,

Kent 19901, Delaware, USA

+1 (341) 209-1116

India

140L, 5th Main Rd, Sector 6, HSR Layout, Bengaluru, Karnataka 560102, India

+91 96418 61031

© 2017-2024 Clapptron Technologies Private Limited. All Rights Reserved.