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:
CAPITALIZE(string)
Converts the first character of a string to uppercase and the remaining characters to lowercase.
Example:
PROPER(string)
Capitalizes the first letter of each word in a text string and converts all other letters to lowercase.
Example:
DOMAIN(url)
Extracts the domain name from a URL.
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:
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:
XOR(logical1, logical2, ...)
Returns TRUE if an odd number of arguments are true, FALSE otherwise.
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, [significance])
Rounds a number up to the nearest integer, or to the nearest multiple of significance if specified.
Example:
FLOOR(number, [significance])
Rounds a number down to the nearest integer, or to the nearest multiple of significance if specified.
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:
COUNT(array)
Counts the number of non-null items in an array.
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:
RANDBETWEEN(low, high)
Returns a random integer between the specified low and high values (inclusive).
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:
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 1 if two dates are identical, or 0 otherwise. Optionally compares at a specified unit (e.g., “day”, “month”).
Example:
IS_AFTER(date1, date2)
Returns 1 if date1 is after date2, or 0 otherwise.
Example:
IS_BEFORE(date1, date2)
Returns 1 if date1 is before date2, or 0 otherwise.
Example:
LAST_MODIFIED_TIME()
Returns the most recent modification time for the record.
Example:
WORKDAY(start_date, num_days)
Returns a date that is a specified number of working days from the start date, excluding weekends.
Example:
WORKDAY_DIFF(start_date, end_date)
Returns the number of working days between two dates, excluding weekends.
Example:
WEEKDAY(date, [startDayOfWeek])
Returns the day of the week as a number. By default, the week starts on Sunday (0 = Sunday, 1 = Monday, …, 6 = Saturday). Pass “monday” as the second argument to start the week on Monday instead.
Example:
WEEKNUM(date, [startDayOfWeek])
Returns the week number of the year for a given date. By default, the week starts on Sunday. Pass “monday” as the second argument to start the week on Monday instead.
Example:
DATESTR(date)
Converts a date to a string in “YYYY-MM-DD” format.
Example:
DATE(value)
Converts a value to a date type.
Example:
DATETIME(value)
Converts a value to a datetime type.
Example:
EDATE(date, [months])
Returns the date that is the specified number of months before or after the start date, keeping the same day. If the day doesn’t exist in the target month, it rolls to the last day of the target month. If months is omitted, defaults to 0.
Example:
EOMONTH(date, [months])
Returns the last day of the month that is the specified number of months before or after the start date. If months is omitted, defaults to 0 (returns last day of the current month).
Example:
DAY(date)
Returns the day of the month from a date.
Example:
MONTH(date)
Returns the month from a date (1–12).
Example:
YEAR(date)
Returns the year from a date.
Example:
HOUR(date)
Returns the hour from a datetime value (0–23).
Example:
MINUTE(date)
Returns the minute from a datetime value (0–59).
Example:
SECOND(date)
Returns the second from a datetime value (0–59).
Example:
DAYS_IN_MONTH([date])
Returns the number of days in the month of the given date. If no date is provided, uses the current date.
Example:
Array Functions
ARRAYCOMPACT(values)
Removes empty or null values from a list and returns the result as a comma-separated string.
Example:
ARRAYJOIN(values, [delimiter])
Joins array elements into a single string, separated by an optional delimiter.
Example:
ARRAYUNIQUE(values)
Removes duplicate values from a list and returns the result as a comma-separated string.
Example:
ARRAYSLICE(array, start, [end])
Extracts a portion of an array from the start index to the optional end index (exclusive). Indexes are 1-based.
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()
Returns an empty string.
Example:
ERROR()
Returns an error value.
Example:
IFERROR(expression, fallback)
Returns the value of the expression if it is not an error; otherwise, returns the fallback value.
Example:
TRUE()
Returns the number 1 (true).
Example:
FALSE()
Returns the number 0 (false).
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.