Formulas
Formula fields in Copera Boards let you create calculated columns that derive their values automatically from other data in your rows. Powered by the Copera Formula Engine -- a dedicated formula language with its own parser and evaluator -- you can build everything from simple arithmetic to complex conditional logic and date calculations.
Formula Language Overview
Copera formulas use a syntax similar to spreadsheet formulas. Each formula is evaluated per row, with access to all column values in that same row. The formula engine supports:
- Arithmetic operators:
+,-,*,/for math operations - String concatenation:
&to join text values together - Comparison operators:
=,!=,>,<,>=,<= - Logical operators:
AND,OR,NOTfor combining conditions - Field references: Reference other columns by wrapping the column name in curly braces, e.g.,
{Column Name} - Nested functions: Functions can be used as arguments to other functions
Basic Syntax
{Price} * {Quantity}
This formula multiplies the value of the "Price" column by the "Quantity" column for each row.
IF({Status} = "Done", "Complete", "Pending")
This formula returns "Complete" when the Status column equals "Done", and "Pending" otherwise.
Function Reference
The Copera Formula Engine provides over 100 functions organized into the following categories. Below is a reference of the most commonly used functions.
Math Functions
| Function | Description | Example |
|---|---|---|
ABS(number) | Absolute value | ABS({Balance}) |
CEIL(number) | Round up to nearest integer | CEIL({Score} / 10) |
FLOOR(number) | Round down to nearest integer | FLOOR({Price}) |
ROUND(number, decimals) | Round to specified decimal places | ROUND({Average}, 2) |
MIN(a, b, ...) | Smallest value | MIN({Estimate}, {Budget}) |
MAX(a, b, ...) | Largest value | MAX({Score1}, {Score2}) |
SUM(a, b, ...) | Sum of values | SUM({Q1}, {Q2}, {Q3}, {Q4}) |
AVERAGE(a, b, ...) | Arithmetic mean | AVERAGE({Test1}, {Test2}) |
MOD(number, divisor) | Remainder after division | MOD({Row Number}, 2) |
POWER(base, exponent) | Raise to a power | POWER({Side}, 2) |
SQRT(number) | Square root | SQRT({Area}) |
Text Functions
| Function | Description | Example |
|---|---|---|
CONCAT(a, b, ...) | Join text values | CONCAT({First Name}, " ", {Last Name}) |
LEFT(text, count) | Extract from start | LEFT({Code}, 3) |
RIGHT(text, count) | Extract from end | RIGHT({Phone}, 4) |
MID(text, start, count) | Extract from middle | MID({ID}, 2, 4) |
LEN(text) | Character count | LEN({Description}) |
LOWER(text) | Convert to lowercase | LOWER({Email}) |
UPPER(text) | Convert to uppercase | UPPER({Code}) |
TRIM(text) | Remove leading/trailing spaces | TRIM({Input}) |
REPLACE(text, old, new) | Replace occurrences | REPLACE({URL}, "http", "https") |
SEARCH(find, text) | Find position of substring | SEARCH("@", {Email}) |
Date Functions
The formula engine integrates with an extensive date library, giving you access to powerful date manipulation and comparison functions.
| Function | Description | Example |
|---|---|---|
TODAY() | Current date | TODAY() |
NOW() | Current date and time | NOW() |
YEAR(date) | Extract year | YEAR({Created}) |
MONTH(date) | Extract month (1-12) | MONTH({Due Date}) |
DAY(date) | Extract day of month | DAY({Birthday}) |
WEEKDAY(date) | Day of week (0-6) | WEEKDAY({Start Date}) |
WEEKNUM(date) | Week number of year | WEEKNUM({Created}) |
QUARTER(date) | Quarter (1-4) | QUARTER({Close Date}) |
DATEADD(date, count, unit) | Add time to a date | DATEADD({Start}, 14, "days") |
DATEDIFF(date1, date2, unit) | Difference between dates | DATEDIFF({Start}, {End}, "days") |
IS_BEFORE(date1, date2) | Check if date1 is before date2 | IS_BEFORE({Due Date}, TODAY()) |
IS_AFTER(date1, date2) | Check if date1 is after date2 | IS_AFTER({Due Date}, TODAY()) |
IS_SAME_DAY(date1, date2) | Check if same day | IS_SAME_DAY({Created}, TODAY()) |
IS_TODAY(date) | Check if date is today | IS_TODAY({Due Date}) |
IS_WEEKEND(date) | Check if date falls on weekend | IS_WEEKEND({Delivery Date}) |
START_OF_MONTH(date) | First day of month | START_OF_MONTH({Date}) |
END_OF_MONTH(date) | Last day of month | END_OF_MONTH({Date}) |
START_OF_WEEK(date) | First day of week | START_OF_WEEK({Date}) |
END_OF_WEEK(date) | Last day of week | END_OF_WEEK({Date}) |
ADD_BUSINESS_DAYS(date, days) | Add business days only | ADD_BUSINESS_DAYS({Start}, 5) |
DIFF_BUSINESS_DAYS(d1, d2) | Business days between dates | DIFF_BUSINESS_DAYS({Start}, {End}) |
Logical Functions
| Function | Description | Example |
|---|---|---|
IF(condition, then, else) | Conditional value | IF({Amount} > 1000, "High", "Low") |
SWITCH(expr, val1, result1, ...) | Match against multiple values | SWITCH({Priority}, "P0", "Critical", "P1", "High", "Normal") |
AND(a, b, ...) | True if all conditions are true | AND({Complete}, {Approved}) |
OR(a, b, ...) | True if any condition is true | OR({Urgent}, {Overdue}) |
NOT(value) | Invert a boolean | NOT({Archived}) |
ISBLANK(value) | Check if empty | ISBLANK({Notes}) |
Common Formula Examples
Concatenating Names
CONCAT({First Name}, " ", {Last Name})
Combines first and last name columns with a space in between.
Calculating Days Until Due
DATEDIFF(TODAY(), {Due Date}, "days")
Returns the number of days remaining until the due date. Negative values indicate overdue items.
Conditional Status Labels
IF(IS_BEFORE({Due Date}, TODAY()), "Overdue",
IF(DATEDIFF(TODAY(), {Due Date}, "days") <= 3, "Due Soon", "On Track"))
Returns "Overdue" for past-due items, "Due Soon" for items due within 3 days, and "On Track" for everything else.
Percentage Calculation
ROUND({Completed Tasks} / {Total Tasks} * 100, 1)
Calculates completion percentage rounded to one decimal place.
Business Days Remaining
DIFF_BUSINESS_DAYS(TODAY(), {Deadline})
Calculates the number of working days (excluding weekends) between today and a deadline.
Error Handling
When a formula contains errors, Copera displays an error indicator in the cell instead of a value. Common errors include:
- Syntax errors -- Mismatched parentheses, missing operators, or unrecognized function names.
- Type mismatches -- Attempting to perform arithmetic on text values or date operations on numbers.
- Null references -- Referencing a column that has no value in the current row. Use
ISBLANK()to handle optional fields gracefully. - Division by zero -- Dividing by a column that contains zero. Wrap with an
IFto check:IF({Total} = 0, 0, {Part} / {Total}).
The formula editor shows a live preview of the result as you type, making it easy to catch and fix errors before saving.
AI-Assisted Formula Creation
If you are unsure how to write a formula, Copera offers an AI-powered formula assistant. Describe what you want to calculate in natural language, and the assistant will generate a formula for you. You can review, edit, and refine the suggestion before applying it to your column.
Next Steps
- Learn about Computed Fields to understand Formula, Function, and Tracker fields in depth.
- Set up Automations that trigger based on formula results.
- Use Filters & Sorting to filter rows by formula column values.