Introduction to Formula Fields
Formula fields in Softr are a powerful field type that enable you to compute values based on other fields in your Database. You can add a formula field to a table like any other field type, then write a formula to generate outputs such as numbers, dates, or text strings. These fields automatically update when referenced data changes, making them ideal for dynamic calculations and data manipulation.To create a formula field:
- Add a new field to your table in Softr.
- Select “Formula” as the field type.
- Enter the formula in the formula editor, referencing other fields and using the functions below.
- Configure the output format (e.g., number, date, or string) under the Formatting tab.
Formula Syntax
-
When referencing other fields inside a formula, always put them in curly brackets, like so:
{Contact Name}or{Status} -
When you use a function, the function only applies to what is inside the parentheses
(). If you need to place functions inside other functions, you can use multiple parentheses, like so:
- Static, text based values always need to be inside quotation marks
“like so:
- Arrays (lists of items) are always displayed inside square brackets
[]
Formula Functions and Operators
Text Functions
CONCATENATE(text1, text2, ...)
Combines multiple text strings into a single string.
Example:
& to combine text together, like so:
LEFT(string, howMany)
Extracts a specified number of characters from the start of a string.
Example:
RIGHT(string, howMany)
Extracts a specified number of characters from the end of a string.
Example:
LEN(string)
Returns the length of a string in characters, including symbols and spaces.
Example:
FIND(search_text, within_text, [start_position])
Locates the position of a substring within a string. Returns 0 if not found.
Example:
SEARCH(search_text, within_text, [start_position])
Similar to FIND, but case-insensitive. Returns 0 if not found.
Example:
SUBSTITUTE(text, old_text, new_text, [instance_num])
Replaces occurrences of a specified substring with new text.
Example:
TRIM(text)
Removes leading and trailing spaces from a string.
Example:
LOWER(text)
Converts a string to lowercase.
Example:
UPPER(text)
Converts a string to uppercase.
Example:
TEXT(value, format)
Converts a value to text with a specified format.
Example:
MID(string, start_position, length)
Extracts a substring from a string, starting at the specified position and continuing for the specified length.
Example:
REPT(text, number_times)
Repeats a text string a specified number of times.
Example:
T(value)
Returns the text value of the input, or an empty string if the input is not text.
Example:
VALUE(text)
Converts a text string representing a number to a numeric value.
Example:
Logical Functions
=, !=, >, <, >=, <=
Comparison operators that return TRUE or FALSE.
Example:
IF(logical_test, value_if_true, value_if_false)
Returns one value if a condition is true, another if false.
Example:
AND(logical1, logical2, ...)
Returns TRUE if all arguments are true, FALSE otherwise.
Example:
OR(logical1, logical2, ...)
Returns TRUE if any argument is true, FALSE otherwise.
Example:
NOT(logical)
Reverses the logical value of its argument.
Example:
SWITCH(expression, value1, result1, [value2, result2, ...], default)
Evaluates an expression and returns a result based on matching values.
Example:
Numeric Functions
+, -, *, /
Performs addition, subtraction, multiplication, or division on numbers.
Example:
^
Raises a number to a power (alternative to POWER).
Example:
ROUND(number, num_digits)
Rounds a number to a specified number of decimal places.
Example:
CEILING(number)
Rounds a number up to the nearest integer.
Example:
FLOOR(number)
Rounds a number down to the nearest integer.
Example:
ABS(number)
Returns the absolute value of a number.
Example:
SUM(number1, number2, ...)
Adds a list of numbers.
Example:
AVERAGE(number1, number2, ...)
Calculates the average of a list of numbers.
Example:
MIN(number1, number2, ...)
Returns the smallest number in a list.
Example:
MAX(number1, number2, ...)
Returns the largest number in a list.
Example:
INT(number)
Returns the largest integer less than or equal to the input number (equivalent to FLOOR).
Example:
MOD(number, divisor)
Returns the remainder after dividing the number by the divisor.
Example:
POWER(base, exponent)
Raises a number to the specified exponent.
Example:
SQRT(number)
Returns the square root of a non-negative number.
Example:
Date and Time Functions
For a full list of Date and Time formatting options, please see this article.DATETIME_FORMAT(date, format)
Formats a date/time value as a text string.
Example:
DATETIME_PARSE(string, format)
Converts a text string to a date/time value.
Example:
SET_TIMEZONE(date, timezone)
Sets a specific timezone for a date field.
Example:
NOW()
Returns the current date and time.
Example:
TODAY()
Returns the current date without time.
Example:
DATEADD(date, number, unit)
Adds a specified number of units (e.g., days, months) to a date.
Example:
DATESUB(date, number, unit)
Subtracts a specified number of units from a date.
Example:
DATETIME_DIFF(date1, date2, unit)
Calculates the difference between two dates in the specified unit (e.g., days, hours).
Example:
CREATED_TIME()
Returns the date and time when the record was created.
Example:
IS_SAME(date1, date2, [unit])
Returns TRUE if two dates are identical, optionally comparing at a specified unit (e.g., “day”, “month”).
Example:
IS_AFTER(date1, date2)
Returns TRUE if date1 is after date2.
Example:
IS_BEFORE(date1, date2)
Returns TRUE if date1 is before date2.
Example:
LAST_MODIFIED_TIME()
Returns the most recent modification time for the record.
Example:
WORKDAY(start_date, num_days, [holidays])
Returns a date that is a specified number of working days from the start date, excluding weekends and optional holidays.
Example:
WORKDAY_DIFF(start_date, end_date, [holidays])
Returns the number of working days between two dates, excluding weekends and optional holidays.
Example:
Array Functions
ARRAYCOMPACT(values)
Removes empty or null values from an array (e.g., from a lookup field).
Example:
ARRAYFLATTEN(values)
Flattens nested arrays into a single array.
Example:
ARRAYJOIN(values, [delimiter])
Joins array elements into a single string, separated by an optional delimiter.
Example:
ARRAYUNIQUE(values)
Returns an array with duplicate values removed.
Example:
Regular Expression Functions
REGEX_MATCH(string, pattern)
Returns TRUE if the string matches the regular expression pattern.
Example:
REGEX_EXTRACT(string, pattern)
Extracts the first substring that matches the regular expression pattern.
Example:
REGEX_REPLACE(string, pattern, replacement)
Replaces the first match of the regular expression pattern with the replacement string.
Example:
Other Functions
RECORD_ID()
Returns the unique identifier for a record.
Example:
ISERROR(value)
Checks if a value or expression results in an error, returning TRUE or FALSE.
Example:
BLANK()
Represents an empty or null value.
Example:
Tips for Writing Formulas
- Plan Ahead: Before writing a formula, outline your goal in plain language (e.g., “Calculate the days remaining until a deadline”).
- Check Syntax: Softr’s formula editor highlights errors. Ensure field names are correct and enclosed in curly braces .
- Use Nesting: Combine functions for complex logic, such as nested IF statements:
- Format Outputs: Use the Formatting tab to set the desired output format (e.g., decimal places for numbers, date styles for dates).
- Test Thoroughly: Preview your formula results in Softr to confirm they behave as expected.
Common Use Cases
- Dynamic Text: Combine names or create custom labels:Result: “New York - USA”
- Conditional Logic: Categorize records based on criteria:Result: “High” or “Low”
- Date Calculations: Track project timelines:Result: Number of days until the due date
- Numeric Calculations: Compute totals or percentages:Result: 10% of Price, rounded to two decimals
Limitations
- Formula fields are read-only and cannot be manually edited.
- Complex formulas may impact performance in large Databases.
- Some functions (e.g., FIND vs. SEARCH) are case-sensitive; choose the appropriate function for your needs.
- Ensure referenced fields exist and contain valid data to avoid errors.
Additional Resources
- Softr Academy: Visit the Softr Academy to take full courses on building Softr apps.
- Community Forums: Join the Softr community to ask questions and share tips.
- Support: Contact Softr support at support@softr.io if you get stuck with your Database setup.