About Text Formulas

In this article, we will explore the implementation of text formula fields, as well as the management of these fields. Additionally, we will provide some helpful tips and notes to enhance your understanding.

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.

What does a text formula look like in the Live App?

The user in the screenshot above is utilizing a text formula field to concatenate an ID field value with a user's name. Here is an example of how this would appear in the Live App:

 

Where do I access text formulas?

To access text formulas, navigate to the "Data" section in the Builder and select the table where you want to add the field.

 

Managing Text Formulas

Adding a Text Formula

To add a field to your table, go to the "Data" section in the Builder and select the desired table from the left menu. Next, click on the "Add Field" button located near the top of the page.

This will present you with the option to choose the Text Formula field type from the left side of the screen. 

After selecting the Text Formula field type, a pop-up modal will appear where you can customize the field settings for your text formula. Once you have configured the field, simply click on the "Add Field" button at the bottom of the pop-up.

 

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:

• Type the name of the field directly in the formula input box

• Use the "Fields" dropdown list to select any available fields, or

• Type numbers 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.

You can also use the "Fields" dropdown to select available fields from connected tables:

 

Text Formula Examples

In addition to the ability to combine field data with text, there are specific scenarios where text formulas prove to be quite valuable.

Part Number + Part Name = Product ID display field

Combining multiple fields for a more recognizable display field:

1. Number field:  "Part Number"

2. Short Text field: "Part Name"

3. Text Formula: "Part Number - Part Name" = {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:

1. Short Text field: "Prefix"

2. Auto Increment field: "Auto ID"

3. Short Text field: "Suffix"

4. Text Formula: "Prefix - AutoID - Suffix" = {Prefix} - {Auto ID} - {Suffix} 

Result example = AC - 3 - FRAM

 

User ID + Last Name = User Display Name

1. Auto Increment field: "User ID"

2. Name field: "Name"

3. Text Formula field: "Last Name" = getNameLast({Name})

4. Text Formula: "User Display Name" = {User ID} - {Last Name}

Result example = 12 - Smith

 

Order ID + Date  = Order Display ID

1. Auto Increment field: "Order ID"

2. Date field: "Order Date"

3. Text Formula: "Order Display ID" = {Order ID} . {Order Date}

Result example = 3765.03/21/2017

 

Text Formula Functions

Certain text formula functions will work with any field which can be treated as text (Short Text, Paragraph text, and Numbers), while others work with Address, Name, Link, and Date Fields. 

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

The image below illustrates how the left function is displayed in the Builder:

 

Using Text Formulas

Using Text Formulas in the Builder

Text formula values are visible in the "Records" section of a table in the Builder. In this section, you can easily view and reference these values, and also utilize record filters to display specific values for the text formula field.

Using Text Formulas in the Live App

Text formula fields can be added to various page views, allowing you to display the corresponding value on your Live App.

 

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.

If the values do not populate immediately, please allow a few minutes and then check back on the records in that table. Please contact our support team if these field values continue to stay blank.

Scenarios Where Text Formulas Cannot Be Used

There are certain scenarios where text formulas cannot be utilized:

1. Cannot be present in a form view

2. Connection fields with a "many" connection (due to the presence of multiple connected values, rather than a single value from a record that can be retrieved).

3. A connection field in another table

Value Contains "formatted_value"

If you forget to close the brackets "{}" in your field values, the software will automatically add "formatted_value" at the beginning of each portion of your text formula when combining these field values.

To resolve this issue, you can use the following Unicode characters for brackets in your field values instead:

• "U+007B" can be used in place of "{".

• "U+007D" can be used in place of "}".

 

Processing of Text Formulas

It is essential to understand the process and order in which text formula fields process information from other fields. This knowledge is helpful to keep in mind while working with text formula fields.

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. If there are any additional records connected to those parent records, their text formulas will also be updated accordingly.

How To Guides

• How to Build a Quiz App

• How to Set a One Time Random ID

 

---