1. Stacker
  2. Glossary
  3. Stacker Fields

Formula field

Updated

Who can use this feature?

👤  Available on all plans

Formulas let you transform and combine existing fields into a new Formula field. For example, you can:

  • Concatenate First name and Last name fields into a Full name Formula field. 
  • Multiply Price and Units field to get the result in the Revenue Formula field.

The field will show only in your Stacker app, and won't show up in your Airtable base or Google Sheets.

Create a formula field

  1. Go to  Manage Fields and data > [Table name] > Fields
  2. Click Add field > Formula
  3. Type in your formula
  4. Click Save

Press space in the formula field box to see a list of all available fields and functions.

Fields in formulas

To use a field in a formula, the field needs to be in the table where you're creating the formula field. It also needs to be one of the following data types:

  • Text
  • Long Text
  • Number
  • Checkbox
  • URL
  • Single Select Dropdown
  • Percentage
  • Currency
  • Rich Text
  • Date
  • Date and Time
  • Multiple Select Dropdown

To use a field in a formula, you will need to wrap it in curly brackets. For example, {Price}. If you type in the opening curly bracket, we will suggest all available fields.

Screenshot_2022-03-31_at_16.06.25.png

List of formulas

Functions work with any valid field data types, while operators work only on number fields. If you use an operator for a non-number field, we will try to convert it into a number. If we can't, you'll see an error message.

 

Type Name Description Syntax Examples
Operator  SUM

Returns the sum of two or more numbers.

SUM( , )

or

+

SUM({Sold},{Not sold})

or

{Sold} + {Not sold}
Operator Subtract

Returns the difference of two numbers.

- {Total stock} - {Sold stock}
Operator Multiply

Returns the product of two numbers.

* {Sold} * {Price}
Operator Divide

Returns one number divided by another.

/ {Total sold} / {Price}
Operator Average

Returns the average of the values in two or more fields.

AVERAGE( , ) AVERAGE({Sold},{Not sold})
Operator Not equal to

Check if one value is not equal to another value.

!=

1!=2 =>True (represented as a checked checkbox)

Operator Equal to

Compare if one value is equal to another value.

=

1=1 =>True (represented as a checked checkbox)

Operator Greater than

Compare if one value is greater than another value.

>

1>5 =>False (represented as an empty checkbox)

Operator Less than

Compare if one value is less than another value.

<

5>1 =>False (represented as an empty checkbox)

Operator Less than or equal to

Compare if one value is less than, or equal to, another value.

<=

2<=2 =>True (represented as a checked checkbox)

Operator Greater than or equal to

Compare if one value is greater than, or equal to, another value.

>=

2<=2 =>True (represented as a checked checkbox)

Function CONCAT Concatenate two values.

CONCAT( , )

or

&

CONCAT("Hello ", {Fullname}, " !")

or

"Hello " & {Fullname} & " !"

Function Condition

Check whether the statement is true. If it is true, then do Action A. If it is false, then do Action B.


IF(statement, Action A, Action B) IF({UserId}, "https://dashboard.com/" + {UserId}, "")
Function Expression

Returns true if all the arguments are true, returns false otherwise.

AND()

AND({Field 1}),{Field 2})

or

{Field 1]AND{Field 2}

Function Expression

Returns true if any one of the arguments is true.

OR()

OR({Field 1}),{Field 2})

or

{Field 1]OR{Field 2}

Function Expression

Reverses a true to false.

NOT()

NOT(a<b)=> Returns the same as a>=b

Function string

Returns the length of a string.

LEN

LEN("Hello World") =>11

Function  string, regex

Returns whether the input text matches a regular expression.

REGEX_MATCH()

REGEX_MATCH("Good Morning","Good.Morning" =>True (represented as a checked checkbox)

Function string, regex, replacement

Substitutes all matching substrings with a replacement string value.

REGEX_REPLACE()

REGEX_REPLACE("Good Morning","M*",""=>"Good"

Function string, regex

Returns the first substring that matches a regular expression.

REGEX_EXTRACT()

REGEX_EXTRACT("Good Morning","M*",""=>"Morning"

Function string

Removes whitespace at the beginning and end of string.

TRIM()

TRIM(" Hello ") =>"Hello"

Function string

Makes string uppercase.

UPPER()

UPPER("Hello") =>"HELLO"

Function string

Makes a string lowercase.

LOWER()

LOWER("Hello") =>"hello"

Function string, howMany

Extract howMany characters from the end of the string.
Accepts string for first arg, number for second

RIGHT()

RIGHT("Hello",2) =>"lo"

Function  

Compares two dates up to a unit and determines whether they are identical. Returns 1 if yes, 0 if no.

IS_SAME()

IS_SAME({date 1},{date 2}, 'unit' => current acceptable units are:

'exact'(matches all units)

'year', 'month','day'

Function  

Determines if [date1] is earlier than [date2]. Returns 1 if yes, 0 if no.

IS_BEFORE()

IS_BEFORE({date 1},{date 2})

Function  

Determines if [date1] is later than [date2]. Returns 1 if yes, 0 if no.

IS_AFTER()

IS_AFTER({date 1},{date 2})

Function  

Returns the four-digit year of a datetime.

YEAR()

YEAR({Date},[timezone])

Function  

Returns the month of a datetime as a number between 1 (January) and 12 (December).

MONTH()

MONTH({Date},[timezone])

Function  

Returns the day of the month of a datetime in the form of a number between 1-31.

DAY()

DAY({Date},[timezone])

Function  

Returns the hour of a datetime as a number between 0 (12:00am) and 23 (11:00pm).

HOUR()

HOUR({Date},[timezone])

Function  

Returns the minute if datetime as a number.

MINUTE()

MINUTE({Date},[timezone])

Function  

Extract how many characters from the beginning of the string.
Accepts string for first arg, number for second

LEFT()

LEFT("Hello",2) =>"He"

Function  

Calculates the number of the specified periods between two datetime values.

DATETIME_DIFF()

DATETIME_DIFF([date1],[date2],'units')

Function  

Returns the item with the lowest value, or the item with the lowest value in an iterable.

 MIN()

MIN(number 1, number 2, number 3...)

Function  

Returns the largest item in an iterable. It can also be used to find the largest item between two or more parameters.

MAX()

MAX(number 1, number 2, number 3...)

Function  

Returns the absolute value of the given number. If the number is a complex number, abs() returns its magnitude.

ABS()

ABS(value)

Function  

Adds a time/date interval to a date and then returns the date.

DATEADD()

DATEADD([date],[#],'units) accepts years, months, weeks and days 9as well as singular counter-parts)

 

Error messages

If a formula is invalid, you will see an error message that might help you understand what went wrong.

 

Error Meaning
Missing formula function or Unexpected formula function Means that you were very creative, but we don't have the formula function yet.
Function only works with field_type but field is a another_field_type Shows when using a field with an incompatible field type.
In an IF function, field_name and field_name_2 must be of similar types Shows in an IF function when the Action A and Action B are different types.
Missing keys in formula 'IF' function: else Means that there is a missing else (Action B) in the IF function.