Formula field types

  • 2 minute(s) read
Prev Next

Most formulas live in formula fields in tables, but you can add formulas to reports and custom data rules too. Complex formulas perform better in tables than in reports.

Types of formula fields used in tables

Formula field type

Guidelines

Text

  • Use if your formula results in text or contains literals with alphanumeric characters

  • Enclose text literals  in double quotes, e.g.,
    SearchAndReplace(“Progressing” , “In progress”)

Numeric 

  • Use to display results of mathematical calculations

  • Numerical values only, positive or negative (integers, decimals)

  • Type numbers directly into formula
    e.g.,     Sum(12.5, 0.5)), or

  • Reference a numerical field
    e.g.,     Sum([Tax], [Total before tax]))

Date

  • Use if your formula results in a date

  • Dates do not have literal form in formulas

  • To reference a text field that holds a date: use ToDate()

    • e.g., a text field with the value Jan 30, 2021 should be referenced as ToDate("Jan 30, 2021")

  • To reference today's date: use Today()

Date/ Time

  • Use if your formula results in date and time of day

Time of Day 

  • Use if your formula results in time of day

Duration

  • Use if your formula results in a span of time

  • Create a duration: subtract two date fields or two date and time fields

  • Durations can be the result of functions, such as: Hours(2) or Days(1)

  • Durations can be positive or negative

  • Negative durations result from subtracting a later date value

Checkbox

  • Use if your formula results in a Boolean
    e.g., for an answer to: "Is the Revenue field greater than the Expenses field?", use:     [Revenue] > [Expenses](statement is true or false)

  • Use to turn on or off a checkbox

  • Use as a condition for an If() function

Phone Number 

Use if your formula results in a phone number

Email Address

  • Use if your formula results in an email address

  • If your table has names and email domains in different fields, use this field type to link them together and display a mailto link

  • When a user clicks the link, an email is generated

User

Use if your formula results in a user value

List-user 

Use if your formula results in a list of user values

URL

  • Use if your formula results in a URL

  • Use a formula to combine values from fields to create URLs, e.g.,
    If a field contains a website address and another field contains a specific page name on the website, create a formula to combine the fields. This creates a link in each record that leads to a different page on the website

Work Date

  • Use if your formula results in a date and your app uses a predecessor field to track the timing of dependent tasks

  • For partial days, use to measure dates in fractional amounts

Example:

  • A task begins on a certain date in the Start Date field (date field type)

  • Task takes 2.5 days to complete

  • Task is tracked in the Duration field (work date field type)

  • To calculate those values (including partial day), add the formula to a Finish Date field (formula work date field type) 

Rich Text

  • Use if your formula displays images, buttons, text, etc. e.g., 
    To display text in a different font size and color:

<div style=\"color:Salmon;font-size:28px\">"&[Task Name]&"</div>

Multi-select Text