Creating formula fields

In this article:

• Formula field types
  • Formula - Text
  • Formula - Numeric 
  • Formula - Date
  • Formula - Date / Time
  • Formula - Time of Day 
  • Formula - Duration 
  • Formula - Checkbox
  • Formula - Phone Number 
  • Formula - Email Address
  • Formula - User 
  • Formula - List-user 
  • Formula - URL
  • Formula - Work Date
  • Formula - Multi-select Text
  • Formula - Rich Text
• Type conversion functions
• Adding a formula field to a table

Most formulas live in formula fields on an app table. However, you can also add formulas to reports and custom data rules.

Keep in mind that very complex formulas will always perform better if they live on a table rather than on a report.

Formula field types

Every field in Quickbase has a a data type associated with it (text, numeric, phone number, etc.).

For each data type, only specific operations make sense. For example

• Multiplication makes sense on numeric values, but not on text values

• Concatenation (linking values together in a chain) is an operation that makes sense on text values, but not dates

Some operations produce the same data type that they act on, while others produce a new data type.

• Example: When you subtract one date from another, Quickbase returns a duration, not a date.

When you add a formula field to a table, you want to add the field type that matches the data type your formula will produce.

You can choose from the following field types:

Formula - Text

Use this type of field if your formula results in text or contains literals with alphanumeric characters.

• When you use a text literal in a formula, you must set it apart from the rest of the formula by enclosing it in double quotes.

  • Example: SearchAndReplace(“Progressing” , “In progress”)

  • Learn more about literals in the Formula foundations help article.

Formula - Numeric 

Use this type of field to display the results of mathematical calculations. It will only work with numerical values.

• Numeric values can be integers or decimals, positive or negative.

• You can type numbers directly into your formula (for example, Sum(12.5, 0.5)), or you can reference a numerical field (for example, Sum([Tax], [Total before tax])).

  • Learn more about field references in the Formula foundations help article.

Formula - Date

Use this type of field if your formula results is a date.

• Dates do not have a literal form in formulas. If your table contains a text field that holds a date (for example, Jan 30, 2021), you can still reference it as a date in your formula by using the ToDate()function.

  • Example: ToDate("Jan 30, 2021") allows the formula to use Jan 30, 2021 as a date value rather than a text value.

• You can obtain today’s date by using the function Today().

Formula - Date / Time

Use this field type if your formula results in a date and time of day.

Formula - Time of Day 

Use this field type if your formula results in a time of day.

Formula - Duration 

Use this type of field if your formula results in a span of time.

• Durations can be created by subtracting two date fields or two date and time fields. They can also be the result of certain functions like Hours(2) or Days(1).

• Durations can be either positive or negative. Negative durations result from subtracting a later date value.

Formula - Checkbox

Use this type of field if your formula will have a Boolean result.

• Boolean formulas always produce one of two values — true or false.

• Boolean values result from comparison operations, like 4<5. For example, if you want your formula to answer the question "Is the Revenue field greater than the Expenses field?", the formula could look like this: [Revenue] > [Expenses]. This statement is either true or it is false.

• You use Boolean results to turn on or off a checkbox or as a condition for an If() function. See the Commonly used formula functions help article [link to new article]******* to learn more about the If() function.

Formula - Phone Number 

Use this field type if your formula results in a phone number.

• This is most commonly used for formulas that display an area code and a phone number together.

Formula - Email Address

Use this field if your formula results in an email address.

• If your table contains names in one field and email domains in another, you can use a Formula - Email Address field to link them together.

• Quickbase displays the result as a mailto link, which automatically generates an email when a viewer clicks on it.

Formula - User 

Use this type of field if your formula will result in a user value.

Formula - List-user 

Use this type of field if your formula will result in a list of user values.

Formula - URL

Use this type of field if your formula results in a URL.

• You can use a formula to combine values from fields to create URLs. For example, if a field contains a website address and another field contains a specific page name that is part of the website, you could compose a formula that combines the two fields, creating a link in each record that leads to a different page on the website.

Formula - Work Date

Use this type of field if your formula will result in a date and your application uses predecessors, which track the timing of tasks when one task depends on another.

• If you need to account for partial days, it is especially important to use Work Date fields because they let you measure dates in fractional amounts.

  • Example: One task in your app begins on a certain date recorded in a field called Start Date. This task takes 2.5 days to complete and is tracked in a field called Duration. You want to create a third field that takes those values and calculates the Finish Date. To do so, you'd need a Formula - Work Date field because regular date fields cannot calculate partial days.

  • Work Date field types are only available in applications that already include a predecessor field.

Formula - Rich Text

Formula - Rich Text fields are used to display images, workflow buttons, formatted text, and more. For example, to display text in a different font size and color:

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

For information on using rich text fields for color-coding fields, see Color-coding fields.

Formula - Multi-select Text

Formula – multi–select text fields enable you to dynamically build lists of text items. For more information, see Using formula-multi-select text fields.

Type conversion functions

Type conversions are a category of functions that let you turn one type of data into another type of data.

Examples

• ToDate("Jan 30, 2021") takes "Jan 30, 2021," which is a text literal and converts it to a date value.

• ToBoolean("Yes") takes "Yes," which is a text literal and converts it to the Boolean value true.

• ToFormattedText(Date(2021,01,30),"MMDDYYY") takes a date value and converts it to formatted text, in this case 01-30-2021.

• ToNumber("-12.3") takes the text value -12.3 and converts it to a numerical value.

• ToWorkDate([Completion Date]) takes the values in the Completion Date field and converts them to work dates.

For a complete list of available type conversion functions and additional examples, see the Quickbase Formula Functions Reference app.

Adding a formula field to a table

1. Add a new field to the table. Choose the appropriate formula field type. Formula field types appear at the bottom of the field type list.

   

2. After you have added the field, click the field name to see the field properties.

3. Enter your formula into the Formula box. To learn more about writing formulas, see the Writing formulas help article.
   

    

4. Click Save.