Formula · Documentazione · SAPUGO Community

A Formula column calculates a value from other columns on the same record and saves the result with it. When one of those columns changes, the result updates. People can read a formula but can't edit it.

When to use it#

Use a Formula for values that follow directly from the record itself:

Line Total, from Quantity times Unit Price.
Full Name, from First Name and Last Name.
A "High" or "Low" label based on an amount.

Pick a different type when:

The inputs come from linked records, such as a sum of order lines: use a Rollup.
The value depends on other tables, the person saving, or the moment of saving: use a computation rule.

Settings#

Setting	What it does
Formula Expression	The calculation. It can be up to 2,000 characters long.
Return Type	What the formula produces: Text, Number, Boolean (true or false), or Date.
Decimal Places	How many decimal places to keep, from 0 to 10. Only for the Number return type.

Pick the return type that matches what your expression produces. If SAPUGO can tell that the expression produces a different type, it suggests the right one. The return type decides how the column sorts and filters.

Write a formula#

Refer to other columns by their Field Name, the internal name generated when the column was created. A column named "Unit Price" has the field name unit_price. Click a column under Available Fields to insert it, or start typing to get suggestions for columns and functions.

You can combine columns with operators:

Operator	Meaning
`+` `-` `*` `/`	Add, subtract, multiply, divide
`=` `!=`	Equal, not equal
`>` `<` `>=` `<=`	Greater than, less than, and so on
`\|\|`	Join two pieces of text

Put text in double quotes, like "High". Some examples:

quantity * unit_price

CONCAT(first_name, " ", last_name)

IF(GREATER_THAN(amount, 1000), "High", "Low")

ROUND(DIVIDE(hours_spent, hours_planned) * 100, 1)

SAPUGO checks the formula as you type and shows any errors before you save.

Not sure how to write it? Describe what you want in the AI Formula Assistant, for example "multiply quantity by price and add tax", and click Generate. If you already have a formula, describe the change and click Modify.

Functions#

Every function is available on every plan. Browse them in the Functions Library below the editor, or search by name.

Math#

Function	What it does
`ADD(a, b)`	Adds two numbers.
`SUBTRACT(a, b)`	Subtracts the second number from the first.
`MULTIPLY(a, b)`	Multiplies two numbers.
`DIVIDE(a, b)`	Divides the first number by the second. Returns nothing if the second number is 0.
`ABS(a)`	The absolute value of a number.
`ROUND(a, places)`	Rounds a number to the given decimal places.
`FLOOR(a)`	Rounds down to the nearest whole number.
`CEIL(a)`	Rounds up to the nearest whole number.
`MOD(a, b)`	The remainder after dividing. Returns nothing if the second number is 0.
`POWER(a, b)`	Raises the first number to the power of the second.
`SQRT(a)`	The square root.
`LN(a)`	The natural logarithm.
`SIN(a)`, `COS(a)`, `TAN(a)`	Sine, cosine, and tangent, with the input in radians.
`GREATEST(a, b, ...)`	The largest of the values.
`LEAST(a, b, ...)`	The smallest of the values.

Text#

Function	What it does
`CONCAT(a, b, ...)`	Joins text values together.
`UPPER(text)`	Converts text to uppercase.
`LOWER(text)`	Converts text to lowercase.
`LENGTH(text)`	The number of characters.
`TRIM(text)`	Removes spaces from the start and end.
`LEFT(text, n)`	The first n characters.
`RIGHT(text, n)`	The last n characters.
`SUBSTRING(text, start, length)`	Part of the text, from a position and with a length.
`REPLACE(text, find, replacement)`	Replaces every occurrence of some text.
`REVERSE(text)`	Reverses the text.
`POSITION(find, text)`	The position of the first value inside the second.
`SPLIT_PART(text, separator, n)`	Splits the text and returns the part at position n.

Logic#

Function	What it does
`IF(condition, then, else)`	Returns one value if the condition is true and another if it is false.
`EQUALS(a, b)`	Checks whether two values are equal.
`GREATER_THAN(a, b)`	Checks whether the first number is greater than the second.
`COALESCE(a, b, ...)`	Returns the first value that isn't empty.

Date#

Function	What it does
`EXTRACT_YEAR(date)`	The year of a date.
`EXTRACT_MONTH(date)`	The month of a date.
`EXTRACT_DAY(date)`	The day of the month.
`EXTRACT_HOUR(date)`	The hour of a date and time.
`EXTRACT_MINUTE(date)`	The minute of a date and time.

Conversion#

Function	What it does
`TO_TEXT(value, places)`	Converts a value to text. For a number, the optional second argument sets the decimal places, for example `TO_TEXT(price, 2)`. `TO_STRING` does the same.
`TO_NUMBER(value)`	Converts a value to a number. Returns nothing if it isn't a valid number.

What a formula can read#

A formula can use most columns on the same record. It can't use other Formula columns, Rollups, Location columns, or the other side of a Relation. Checkbox columns are read as true or false, Number columns as numbers, Date columns as dates, and most other columns as text.

Tips#

Write the calculation in words first ("line total is quantity times unit price"), then as a formula.
If any input comes from another table, it isn't a formula. Use a Rollup or a computation rule.
Changing the expression of an existing formula recalculates the column for every existing record when you save.
Formula columns can't be converted to another type, and can't be added to time series tables.