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?

textformulas1

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:

textformulas2

 

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.

textformulas3

 

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.

textformulas4

 

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.

textformulas5

 

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.

textformulas6

Caution: Please exercise caution when deleting a field, as this action will permanently remove all field values from existing records. It is important to confirm that this is the correct action for your app before proceeding.

 

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.

textformulas7

Notes:

  • The Equation Output section below the text formula editor provides a visual representation of the current formula's output.
  • Field values passed into text formulas are always parsed as strings. As such, they can not be used for certain function parameters which expect a numerical value.
 

 

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.

textformulas8

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

textformulas9

 

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. Below, we will delve into some examples:

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} 

textformulas10

Result example = 77 - Air Cleaner

image alt text

 

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}

textformulas12

Result example = AC - 3 - FRAM

image alt text

 

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 that 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:

textformulas14

Notes on vocabulary:

  • A string or text string refers to any value that can be interpreted as text. This means that "Example," "Example15," and "123456" can all be considered as strings.

  • An argument is a value that a function uses to calculate its result. In formulas, arguments are placed within parentheses. For example, the left function, left(String, End Position), takes both the String and End Position as arguments.


 

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.

textformulas15

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.

textformulas16

Tip: For more information on adding or editing views for your live app pages, check out our article About Views.

 

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.

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:

  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