Stacker
LoginSign upAstra Docs
  • Getting Started: Quick Guide
    • What is Stacker?
    • Key Features
    • Quick Overview of Stacker
    • What can I build in Stacker?
      • Creating a Home Page
      • Build an Internal Tool
    • Creating your first App
  • Stacker V 5(Astra) - Frequently Asked Questions
  • Account Settings
    • What is a Workspace?
      • Workspace Settings
      • Delete a Workspace
    • Adding Multiple Apps
      • Deleting an App
    • Subscriptions and Billing
  • Customer Access
    • Customer Access
  • Layout types
    • Layout Types
      • Layouts vs Custom Pages
    • Adding Custom Pages in Stacker
    • Detail Layout
      • Detail page widgets
        • Detail: Text Widget
        • Detail: Related Record Widget
        • Detail: Related Record List
          • Related record list widget not showing option
        • Detail: Quick Links Widget
        • Detail: Pipeline Widget
        • Detail: Highlights Widget
        • Detail: Fields Widget
          • Detail: Field Options
          • Separating fields into sections
        • Detail: Embed Widget
          • Embedding Google Maps
          • Embedding Airtable
          • Embedding Loom Videos
          • Embedding Vimeo
          • Embedding Youtube
          • Embedding Google Drive videos
          • Embedding Google Drive Images
          • Embedding content on a detail page
        • Detail: Callout Widget
        • Detail: Banner Widget
        • Detail: Big Numbers Widget
        • Detail: Charts Widget
      • Detail: Delete Record Button
      • Detail: Edit Button
      • Detail: Record Header
        • Change the title on the detail page
      • Detail: Tabs
        • Detail: Activity Tab
    • List Layout
      • Calendar Layout
      • List Options
        • List: Sort
        • List: Cover Image
        • List: Roles
        • List Layout: Change layout title
        • List: Inline Filters
        • List: Header
        • List: Duplicate Layout
        • List: Filter
        • Allow users to download list data as CSV
        • List: Relative Date Filtering
        • List: Field options
        • List: Show, Hide, and Reorder Fields
    • Customize the Form Layout
      • Creating multi step form
      • Auto-filling values into create forms
      • Set default values on Forms
    • Stacker Navigation
      • Navigation: Showing and hiding tables
      • Enable Breadcrumb Navigation
  • Permissions and Security
    • Introduction to Permissions
    • Configure a Permission Rule
      • Table Permissions
        • Editing Permissions
        • Multiple permission rules
    • Set up User Roles
      • Automatically assign Roles
    • Conditional Visibility
  • Customization
    • Client Logos
    • Customizing App Appearance
    • Getting started with CSS in Stacker
    • Custom CSS
    • Workspace Appearance
    • Custom Domain
      • Custom Domain not working
    • Whitelabel
    • Custom Scripts
    • Creating Buttons
      • Action Buttons
      • Add new button showing when it shouldn't
    • Translate your Stacker App
  • Users
    • Introduction to users in Stacker
    • Users: Overview
    • Users: Workspace Collaborators
      • Workspace Users
    • Users: App Users
      • Adding users to your app
      • Login flow for users
      • Open Signup
      • Registering and Logging in
    • Previewing an app as a user for testing
  • Data
    • Formula field
    • Rich Text
    • Third Party Integrations
      • FullStory integration
      • Intercom Integration
      • Zapier Integration
        • Sending an email with Zapier
      • Airtable
        • Syncing Schema and Data
        • Update Sharing link password
        • Real time Airtable sync
        • Upgrading Airtable data connections
        • Enabling tables and fields
    • Enable Color on Single and Multi Select Fields
    • Apply dynamic data filter to Link Fields
    • Importing data into Stacker
      • Importing data from CSV
    • Applying table data filters
    • Exporting data from Stacker
    • Creating lookups and rollups between tables
      • Creating links between tables
    • Stacker Tables: Editing field names and types
      • Stacker Tables: Pasting Data
      • Stacker Tables: Linked Fields
      • Stacker Tables: Editing and deleting tables
      • Stacker Tables: Creating a new table
      • Stacker Tables: Adding new fields
      • Stacker Tables: Adding and Editing Rows
      • Stacker Fields
  • Troubleshooting
    • There was an error saving your record
    • Unable to toggle on linked fields
Powered by GitBook
On this page
  • Create a formula field
  • Fields in formulas
  • List of formulas
  • Error messages
  1. Data

Formula field

PreviousPreviewing an app as a user for testingNextRich Text

Last updated 1 month ago

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

  2. Select the table and select Fields

  3. Click Add field

  4. Give your field a name

  5. Select the field type: Formula

  6. Type in your formula and click Save

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.

List of formulas

SUM

Returns the sum of two or more numbers.

Example:

SUM({Sold},{Not sold})

or

{Sold} + {Not sold}

SUBTRACT

Returns the difference of two numbers.

Example:

{Total stock} - {Sold stock}

MULTIPLY

Returns the product of two numbers.

Example:

{Sold} * {Price}

DIVIDE

Returns one number divided by another.

Example:

{Total sold} / {Price}

AVERAGE

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

Example:

AVERAGE({Sold},{Not sold})

NOT EQUAL TO

Check if one value is not equal to another.

Example:

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

EQUAL TO

Compare if one value is equal to another value.

Example:

1=1 =>True (represented as an empty checkbox)

GREATER THAN

Compare if one value is greater than another value.

Example:

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

GREATER THAN OR EQUAL TO

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

Example:

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

LESS THAN

Compare if one value is less than another value.

Example:

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

LESS THAN OR EQUAL TO

Compare if one value is less than another value.

Example:

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

CONCAT( , ) or &

Concatenate two values.

Example:

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

or

"Hello " & {Fullname} & " !"

IF (statement, Action A, Action B)

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

Example:

IF({UserId}, "https://dashboard.com/" + {UserId}, "")

AND( )

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

Example:

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

or

{Field 1} AND {Field 2}

OR( )

Returns true id any one of the argument is true.

Example:

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

or

{Field 1} OR {Field 2}

NOT( )

Reverse a true to false.

Example:

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

LEN

Returns the length of a string.

Example:

LEN("Hello World") =>11

REGEX_MATCH( )

Returns whether the input text matches a regular expression.

Example:

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

REGEX_REPLACE( )

Substitutes all matching substrings with a replacement string value.

Example:

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

REGEX_EXTRACT( )

Returns the first substring that matches a regular expression.

Example:

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

TRIM( )

Removes the whitespace at the beginning and end of string.

Example:

TRIM(" Hello ") =>"Hello"

UPPER( )

Makes string uppercase.

Example:

UPPER("Hello") =>"HELLO"

LOWER( )

Makes string lowercase.

Example:

LOWER("Hello") =>"hello"

RIGHT( )

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

Example:

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

IS_SAME( )

Compares two dates up to a unit and determines whether they are identical. Returns true or false.

Example:

IS_SAME({date 1},{date 2}, 'unit')

current acceptable units are:

'exact'(matches all units)

'year', 'month', 'day'

IS_BEFORE( )

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

Example:

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

IS_AFTER( )

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

Example:

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

YEAR( )

Returns the four-digit year of datetime.

Example:

YEAR({Date},[timezone])

MONTH( )

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

Example:

MONTH({Date},[timezone])

DAY( )

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

Example:

DAY({Date},[timezone])

HOUR( )

Returns the hour of a datetime as a number between 0 (12.00am) and 23 (11.00pm).

Example:

HOUR({Date},[timezone])

MINUTE( )

Returns the minute if datetime as a number.

Example:

MINUTE({Date},[timezone])

LEFT( )

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

Example:

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

DATEDIF( )

Returns the difference between datetimes in the unit specified.

Unit accepts:

'year','month','week','day','hour','minute'

Example:

DATEDIF([date1],[date2],"units")

MIN( )

Returns the item with the lowest value.

Example:

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

MAX( )

Returns the largest item between two or more parameters.

Example:

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

ABS(number)

Returns the absolute value.

Example:

ABS(-5)

DATEADD( )

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

Example:

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

TODAY(timezone)

Returns the current date, but not the current time.

NOW( )

Returns the current date and time.

ROUND( )

Rounds to a number of decimal places as specified by precision e.g "0", "1", etc.

Example:

ROUND({Unit Price},0)

ROUNDUP( )

Rounds the value to the number of decimal places given by "precision" always rounding up. e.g "0", "1", etc.

Example:

ROUNDUP({Unit Price},0)

VALUE(string)

Coverts text string to a number.

Example:

VALUE({Quoted Price})

INT( )

Returns the greatest integer that is less than or equal to the number.

Example:

INT({Unit Price})

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.

Screenshot_2022-03-31_at_16.06.25.png

Functions work with any , 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.

valid field data types