Table of Contents

Text Formulas

Lesley Updated by Lesley

What is a Text Formula?

Text Formulas are fields that combine multiple text values into a single field, including values from other fields. Text formulas are very similar to equations and can be added as fields to tables within the "Data" section in the Builder.

For example, you can use text formulas to extract the street from an address field, the day from a date field, or just to combine multiple field values into one field.

What does a text formula look like in a live app?

In the image above, the user is using a Text Formula field to append an ID field value to a user’s name. Here’s an example of how that would display in the live app:

Where do I access text formulas?

Text formulas can be accessed from the "Data" section in the Builder:

Manage Text Formulas

Add a Text Formula

From the "Data" section in the Builder, select the table you want to add your field to from the left menu. Then, click the ‘Add Field’ button at the top of the page. This will allow you to select the Text Formula field type from the left-side of the screen:

Once you select the Text Formula field type, a popup will appear where you can edit your text formula’s field’s settings. Once you have setup the field, click on the ‘Add Field’ button at the bottom of the pop-up:

Edit a Text Formula

To edit an existing Text Formula, select the table that contains the Text Formula field then click on the ellipsis next to the field’s name:

Delete a Text Formula

To delete a Text Formula field, select the table that contains the Text Formula field then click on the ellipsis next to the field’s name. Select the ‘Delete’ option from the dropdown:

Deleting a field is destructive and will cause all of field values for existing records to be removed, so confirm this is the correct action in your app before proceeding.

Build a Text Formula

You can combine multiple fields to create a new text value.

To build to 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

Below the text formula editor, the Example Output shows you an example output of the current formula.

Field values passed into Text Formulas are always parsed as strings. As such, they cannot be used for certain function parameters which expect a numerical value.
Access Connected Fields

As with Equations, text formulas give you the option of retrieving data from connected records. If you start typing the name of a field on a connected table, you'll see it appear among 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

Along with the option of simply combining field data with any text, there are particular scenarios where text formulas can be useful.

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}
  4. 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 "Pre"
  2. Auto Increment field "Auto ID"
  3. Short Text field "Suffix"
  4. Text Formula "Pre-AutoID-Suf" = {Prefix}-{Auto ID}-{Suffix}
  5. 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}
  5. 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}
  4. 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. See full list of text formula functions here.

You can store data pulled or modified from existing records, - e.g. just the month from a Date Field - or replace part of a Short Text Field with other text. The image below demonstrates the left function as it appears in the builder:

A note on vocabulary:

  • A string or text string is any value which can be treated as text. So, "Example", “Example15”, and “123456” can all be treated as strings.
  • An argument is something a function uses to determine its output; arguments go between the parentheses in formulas. So, the left function, left(String, End Position), takes both String and End Position as arguments.

Using Text Formulas

Using Text Formulas in the Builder

Text formula values will display in the "Records" section of a table in your builder. Here you can view and reference these values, as well as using record filters to display particular values for this text formula field.

Using Text Formulas in the Live App

Text formula fields can be inserted on to most page views, to then display the value on your Live App.

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

Notes & Troubleshooting

Populate 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 you don't see the values populate immediately, give it a few minutes and come back to that table's records. Do contact Knack support if these field values continue to stay blank.

Please note that 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 a few situations where text formulas are not able to be used:

  1. Not present in a form
  2. Fields with a "many" connection (since there are many values connected, not a single value from a record that can be pulled in)
  3. A connection field in another table

Value contains "formatted_value"

Un-closed brackets "{}" in your field values will cause the software to add "formatted_value" to the beginning of each portion of your text formula when these field values are combined. Using the following Unicode characters for brackets in your field values instead will resolve this issue:

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

How To Guides

How did we do?

Conditional Rules

About Fields

Contact