Text Formula Fields

What You'll Learn

You'll discover how to create text formulas that merge multiple text values into a single field, extract specific information from complex fields, and build dynamic text strings that update automatically. This knowledge will help you create more readable and informative displays, generate unique identifiers, and present your data in ways that are more meaningful to your users without requiring manual data entry or maintenance.

What is a Text Formula?

Text formulas are powerful tools that allow you to merge multiple text values into one field, including values from other fields. Similar to equations, text formulas can be added as fields to tables in the "Data" section of the Builder.

Text formulas are incredibly versatile and allow you to perform various operations on text values. For instance, you can extract the street from an address field, retrieve the day from a date field, or even combine multiple field values into a single field.

Managing Text Formulas

Adding a Text Formula

To add a text formula field:

1. Go to the "Data" section in the Builder
2. Select the desired table from the left menu
3. Click on the "Add Field" button
4. Choose the Text Formula field type
5. Configure the field settings
6. Click "Add Field"

Editing a Text Formula

To modify an existing Text Formula, choose the table that contains the Text Formula field and then click on the ellipsis next to the field's name.

Deleting a Text Formula

To remove a Text Formula field, choose the table that contains the Text Formula field and then click on the ellipsis next to the field's name. From the dropdown menu, select the "Delete" option.

Building a Text Formula

You have the ability to merge multiple fields together in order to generate a new text value.

To create your text formula:

1. Type the name of the field directly in the formula input box.
2. Use the "Fields" dropdown list to select any available fields, or
3. Type text or operators directly into the input box.

Accessing Connected Fields

Similar to equations, text formulas also provide the capability to retrieve data from connected records. When you begin typing the name of a field on a connected table, you will notice that it appears in the suggestions with the format of Field connected with Table > Connected Table.

Text Formula Examples

Part Number + Part Name = Product ID display field

Combining multiple fields for a more recognizable display field:

{Part Number} - {Part Name}

Result example = 77 - Air Cleaner

Pre + Auto ID + Suffix = ID Field

Combining a prefix and suffix with an Auto Increment field to create a more meaningful ID field:

{Prefix} - {Auto ID} - {Suffix}

Result example = AC - 3 - FRAM

User ID + Last Name = User Display Name

Creating a user display name by combining ID and last name:

{User ID} - {Last Name}

Result example = 12 - Smith

Order ID + Date = Order Display ID

Creating an order reference by combining ID and date:

{Order ID} . {Order Date}

Result example = 3765.03/21/2017

Text Formula Functions

Certain text formula functions will work with any field that can be treated as text (short text, paragraph text, and numbers), while others work with address, name, link, and date fields.

You can store data that is extracted or modified from existing records. For example, you can retrieve the month from a Date Field or replace part of a Short Text Field with other text.

Notes & Troubleshooting

Populating a Text Formula with Values

Once your text formula is created, the values in this field will automatically populate. Depending on the number of records in that table, it may take just a bit of time.

Note: Text formula values cannot be manually edited. The value of the field is determined by the formula entered into the field.

Scenarios Where Text Formulas Cannot Be Used

There are certain scenarios where text formulas cannot be utilized:

• Cannot be present in a form element
• Connection fields with a "many" connection
• A connection field in another table

Processing of Text Formulas

When you add a text formula field to a table with existing records, the formula will begin processing for those records right away. If the table has a significant number of records, the processing might require some additional time to complete.

Processing Order

You can add text formulas based on other text formulas and fields. When a record is updated, it takes place in the following order:

1. All text formulas for the record(s) are updated first.
2. Next, the text formulas of the parent records connected to the updated records will also be updated.