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:
Click on the + symbol to the right of the last field header in the Table view.
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
Type in the Field Name and Description.
Click on Next
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.
Mark, any of the corresponding Checkboxes to show a better representation of the field on the board.
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)