Formula
With formula field type you can pull data from other fields and make calculations to build any information you need. The formula type acts on money fields, number field, short text, dropdown, date and formula field.
However, you cannot use formula field type to draw data from field types like Files, Link, Location.
Formula field allows you make calculations like on normal excel software, but in a better way.
How to Add a formula field
To add formula field to your board:
Go to board settings, click on the dropdown icon and tap on "Manage Fields" from the dropdown options.
On the Field page, do click on "Create New Field"
A form page pops up where you fill in the name and description for the new field
Go on to click on "Field type" which displays a list of options.
Scroll through or simply type formula to select the field.
Once you choose the formula field, you get to see an input field where you compute any logical calculation.
Mark any of the corresponding Checkboxes to show a better representation of the field on the board.
Formula Operators
Arithmetic Operators
+
This is known as the addition operator. You can use the addition operator to add numbers from different fields. With addition operator you can concatenate strings from different fields.
For instance. if field A contains the price of an Item (55.00) and field B (45.00), the addition operator provides the sum of the fields.
45.00 + 55.00 = 100.00
Also, if field A contains a string ("John") and field B has a string known as ("Doe")
"John" + "Doe" = "John Doe"
-
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.
For instance, if field A contains price of an item (55.00) and field B (45.00) on your board, the subtraction operator works in like this
55.00 - 45.00 = 15.00
/
This is the division operator which gives a quotient value when a field A (Dividend) is divided by another field B( divisor).
For instance if field A comes with a number value of 50 and field B with value of 10 on your board, then the division operator works like this:
50 / 10 = 5
*
The * operator is known as the multiplication operator and it allows a number in a field to have multiplying factor on another field.
For instance, if Field A shows the total cost of a single item as $45 and Field B shows their are 3 units for that item available. Then the total cost for all item will be Field A * Field B.
$45 * 3 = $135
**
This is called the exponential operator and it specifies the number of times to use a number as a multiplying factor.
For instance, if Field A comes with a number value of 20 and field B has a number value of 5.
Field A** Field B == 20** 3 = 8000
()
The Bracket operator is used for a function call or logical condition. With the Bracket operator you can stipulate which operation you need to execute first before moving to the next.
For instance (Field A + Field B) * (Field A)
The addition operation between Field A and Field B executes before the multiplication operation.
For functions, you could have bracket operator which specify which function you need to execute before the next function call
$(Field A)<= 200? ($(Field B) * 0.2):
The equation above is interpreted
if value in Field A is less than or equal to 200, then execute the value in Field B * 0.2.
Comparison Operators
==
This operator allows you compare two fields on your board. You can also apply a condition to execute an action if the comparison is either true or false.
<=
This is the less than or equal to comparison operator. The less than or equal to operator is used to denote a condition while the values of a specific field is either less or equivalent to the other field.
>=
This is the greater than or equal to comparison operator. The greater than or equal to operator is used to denote a condition while the values of a specific field is either less or equivalent to the values on another field.
!=
This is known as the equal not-equal to sign. With this sign you can create a formula with a condition to execute an action where the field value isn't an equivalence of specified value.
Conditionals
?
The ? sign denotes the IF function and it's a commonly used function for the formula field type. With IF function, you can check if a particular condition is true or false. That allows you to set a turn value or computation for any of the condition.
If you don't understand how the conditional IF function should look like for formula field, We can help!
For instance,
$(Field A) > 2000 ? ($(Field A)+ 200)
Interpretation:
if Field A is greater than 2000, then add 200 to such values higher than 2000
:
The : sign denotes either an elif (else if) of an else condition. It is elif, when : is used in combination with a ? sign for a specific condition. Else when : sign is used as a standalone.
To check how a formula is fully built let use a typical board containing scores for university students, such board should have a formula field that
returns Grade A if field for total score is greater than 70
returns Grade B, if field for total score less than 70 but greater than 59
returns Grade C, if field for total score is less than 60 but greater than 49
returns Grade D, if field for total score is less than 50 but greater than 44
returns Grade E, if field for total score is less 45 but greater than 39
returns Grade F, if field for total score is less than 40
$(Total scores) < 40 ? "F" : ($(Total scores) < 45 ? "E" : ($(Total scores) < 50 ? "D" : ($(Total scores) < 60 ? "C" : ($(Total scores) < 70 ? "B" : "A" ))))
There are many other instances to use the formula field types. You will see these examples when you explore other Formula use cases.
Type of formula used
random numeric ---> {numeric [n]}This formula equation allows you generate random (n) numbers. n describes the size for the random number values. With the random numeric function you can create OTPs, codes or automatic passwords.
if you input{numeric [4]} into a formula builder, it can generate a string of numbers like4368
If you input {numeric [6]} in the formula builder, it can generate random sets of 5 numbers like 629543
random alphanumeric ---> {numeric [n]}This formula equation allows you generate random alphabets from A - Z on a particular field.
random alphabetic string >> {alphabetic [4]}Arithmetic operations on numeric and money fields >> $(numeric field) * $(money field)Concatenation of strings in two fields ---> $"(field A)" + $"(field B)"Simple arithmetic operations carried out on two or three operands >> 3* 5 +(20/3)Formula type to support distance field type as its list of options when creating it(i.e not only numeric/money field typeLast updated
Was this helpful?