Fields
      Back to home
      1. Knowledge Base
      2. Builder Guide
      3. Fields

      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. 

      See the full list of text formula functions here.

      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.

      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