Go to Quabbly
All Collections
Field Features
Formula Field Type

Formula Field Type

How can I use the Formula Field Type?
Jamiu Ozigi avatar
Written by Jamiu Ozigi. Updated over a week ago

What is the Formula Field Type?

Formula Field type allows you to perform various operations like arithmetic, conditionals, count, average, and many others on your board. Formula field allows you to make calculations like on excel software but in a better way.

With formula field type you can pull data from other fields and make calculations to build any information you need.

The formula type can perform string functions, Arithmetic functions, and date-processing functions so it acts on money fields, number fields, short text, dropdown, Radio buttons, date, time, and formula fields.

However, you cannot use formula field type to draw data from field types like Files, Link, Locations.

Formula field type only links to values in the cell inputs and not description or any other key details.

How to create a Formula Field

To create the formula field:

  1. Click on the + symbol to the right of the last field header in the Table view.

  2. This pops up a form page, on which you click on Field type to select the formula field.

    💡 Note: The formula field type is a system-generated field type

  3. Type in the Field Name and Description.

  4. Click on Next

  5. Type in the formula(click on a function to pick the functions you wish to perform and input field where you compute any logical calculation. ).

    💡 Note: The Syntax and Example give you an idea of what each function should look like when typing in the Formula.

  6. Mark, any of the corresponding Checkboxes to show a better representation of the field on the board.

  7. Click on Create Field

FORMULA OPERATORS

1.String Functions

  • JOIN

This operator joins or concatenates text together, it also operates on field types with strings

EXAMPLE

JOIN({'welcome'},{'to'}, {'Quabbly'})

EXPECTED RESULT

welcome to Quabbly

  • LEFT

Extracts a specified number of characters from a string from the left-hand side

EXAMPLE

LEFT({'Welcome'},4)

EXPECTED RESULT

Welc

  • LEN

Gives you the number of characters for given string characters in counts or numbers, for example, the number of characters in the word 'David' == 5

EXAMPLE

LEN({'Quabbly'})

EXPECTED RESULT

7

  • LOWER

Changes any record you pass in string characters to all lower cases

EXAMPLE

LOWER({'QUABBLY'})

EXPECTED RESULT

quabbly

  • UPPER

Changes any record you pass in string characters to all upper case

EXAMPLE

UPPER({'QUABBLY'})

EXPECTED RESULT

QUABBLY

  • REPLACE

Helps replace a part of a strIng character you pass as a record to a field with another character.

EXAMPLE

REPLACE({'Monday'},{0},{2},{'Fri'})

EXPECTED RESULT

Friday

  • REPEATS

The repeat function allows you present string characters a specified number of times.

EXAMPLE

REPEAT({'quabbly'},{2})

EXPECTED RESULT

quabblyquabbly

  • RIGHT

Extracts a specified number of string characters from a string all on the right-hand side

EXAMPLE

RIGHT({'Welcome'},{4})

EXPECTED RESULT

come

  • SEARCH

Search for a particular character within another string character, returns the value 'not' if it doesn't exist, and returns 'exist' if the character is present within the other

EXAMPLE

SEARCH({'Board'},{'Board Group'})

OR

SEARCH({'Board'},{'Teacup'})

EXPECTED RESULT

exist (because the word "board" exists in the word "board group")

OR

not (because the word "board" does not exist in the word "Teacup")

  • SUBSTITUTE

The substitute function helps you replace part of a string character by specifying the string character and the part you want to replace

EXAMPLE

SUBSTITUTE ({'Goodmorning'}, {'morning'}, {'night'})

EXPECTED RESULT

Goodnight

  • TEXT

This allows you to convert a number field type that doesn't support characters in a form that takes string characters

EXAMPLE

TEXT({200}, {$#,###.00})

EXPECTED RESULT

$200

  • TRIM

Removes whitespace from a text.

EXAMPLE

TRIM({' Jamiuozigi'})

EXPECTED RESULT

Jamiuozigi

2. MATH FUNCTIONS

  • ABS

Finds the absolute value of a number. An absolute number is the number of units that separates it from a zero, it's never a negative value. For instance the absolute number for 5 abs(5)=5 which is the same as abs (-5)=5

EXAMPLE

ABS(-5) or ABS(5)

EXPECTED RESULT

5

  • LOG

Returns the logarithmic value of a numeric no

EXAMPLE

LOG(5)

EXPECTED RESULT

0.6989

  • SQRT

Gives you the number of characters for given string characters in counts or numbers, for example, the number of characters in the word 'David' == 5

EXAMPLE

Returns the square root of a numeric field type

EXPECTED RESULT

4

  • CBRT

Returns the cubic root for a value in the numeric field type

EXAMPLE

CRBT(27)

EXPECTED RESULT

3

  • ROUND_UP

This function rounds up a number to the highest value after the decimal.

EXAMPLE

ROUND_UP(7.57)

EXPECTED RESULT

8

  • ROUND_DOWN

Rounds down a number to the lowest value after the decimal.

EXAMPLE

ROUND_DOWN(7.57)

EXPECTED RESULT

7

  • SIN

Returns the sine of a value in a numeric field type

EXAMPLE

SIN(2.45)

EXPECTED RESULT

0.63776

  • COS

Returns the cosine of a value in a numeric field type

EXAMPLE

COS (2.45)

EXPECTED RESULT

-0.77023

  • TAN

Returns the tangent of a value in a numeric field type

EXAMPLE

TAN (2.45)

EXPECTED RESULT

-0.82802

  • AVG

Finds the average of numbers

EXAMPLE

AVG(3, 5, 7)

EXPECTED RESULT

5

  • DIV

The division operator gives a quotient value when a field A (Dividend) is divided by another field B( divisor).

EXAMPLE

DIV(20, 10)

EXPECTED RESULT

2

  • SUM

This is known as the addition operator. You can use the addition operator to add numbers from different fields. With an additional operator, you can concatenate strings from different fields.

EXAMPLE

SUM(20,10)

EXPECTED RESULT

30

  • MUL

The multiplication operator allows a number in a field to have a multiplying factor on another field.

EXAMPLE

MUL(20,10)

EXPECTED RESULT

200

  • MOD

The modulus is another name for the remainder after division.

EXAMPLE

MOD(17,5)

EXPECTED RESULT

2, since if we divide 17 by 5, we get 3 with the remainder 2.

  • SUB

This is known as the subtraction operator and it works on numbers and money fields. With this operator, you can derive the difference between numbers in the fields.

EXAMPLE

SUB(15,5)

EXPECTED RESULT

3

  • MIN

This operator works on numbers and money fields. With this operator, you can derive the smallest value in the fields.

EXAMPLE

MIN(15,5,10,20,2,17)

EXPECTED RESULT

2

  • MAX

This operator works on numbers and money fields. With this operator, you can derive the highest value in the fields.

EXAMPLE

MAX(15,5,10,20,2,17)

EXPECTED RESULT

20

3. DATE PROCESSING FUNCTIONS

  • DAY ({Created Date})

This formula extracts the day from a specified date

SYNTAX

DAY({Date}) DAY({DATE AND TIME})

  • WEEK

The date function allows you to extract the week number in a year based on a specific date

SYNTAX

WEEK ({DATE}), WEEK ({DATE AND TIME})

  • MONTH

This date function returns the month from a specified date

SYNTAX

MONTH ({DATE}), MONTH({DATE AND TIME})

  • HOUR

This date function operates on the time and date-time field to return the hour of the day as a number from 0 to 23- hour

SYNTAX

HOUR({DATE AND TIME}), HOUR({TIME})

  • MINUTE

This date function operates on the time and date-time field to return a minute in a time as a number from 0 to 59

SYNTAX

MINUTE({DATE AND TIME}), MONTH({TIME})

  • SEC

This date function operates on the time and date-time field to return seconds in a time as a number from 0 to 59

SYNTAX

SEC({DATE AND TIME}), SEC({CREATED TIME}), SEC ({TIME})

  • TODAY - should evaluate to the current date at midnight

This function returns the current date from midnight

SYNTAX

TODAY()

  • NOW - should evaluate to current date and time

This function helps evaluate the current date and time, current date from midnight, and time-based on a 24-hour clock

SYNTAX

NOW()

  • YESTERDAY - should evaluate to yesterday at midnight

This function returns yesterday's date from the current date from midnight

SYNTAX

YESTERDAY()

  • NOW_YESTERDAY - should evaluate to this time yesterday

This function evaluates from the current time to yesterday's date.

SYNTAX

DAYS_BETWEEN(TODAY(),{Last Visit}),

  • DAYS_BETWEEN(x, y) e.g. DAYS_BETWEEN(TODAY(), {Last Visit})

Takes two date parameters and returns the difference between two dates

SYNTAX

NOW_YESTERDAY()

  • ADD_DAY({Date},y) .. returns a new date

This function takes one date parameter and number, it adds a number to the specified date to return a new value

SYNTAX

ADD_DAYS()

SUBTRACT_DAY({Date}, y) .. returns a new date

This function takes one date parameter and number, it removes a number from the specified date to return a new value

SYNTAX

ADD_DAYS

SUBSTRACT_DAYS(x)

Did this answer your question?