Articles in this section

See more

Text Formulas

Avatar
Sam
Updated
Follow

What is a Text Formula?

Text Formulas fields work by combining data from multiple fields that is calculated for each record. Text formulas are very similar to equations. They are a type of a field on an object within your "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 fields into one field.

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

Where do I access text formulas?

Text formulas can be accessed from the "Data" section in the Builder, in the left column, by selecting the object where you want the text formula to be placed.

Manage Text Formulas

Add a Text Formula

From the "Data" section in the Builder, select the object you want to add your field to from the left menu. Hover over the Text name/icon at the top and choose "Text Formula". Add your field name and click "Add Field".

Edit a Text Formula

From the "Data" section in the Builder, select the object where you want to edit your text formula. To edit, click on the gear icon on the field.

Delete a Text Formula

From the "Data" section in the Builder, select the object where you want to delete your text formula. To delete, click on the trash can icon on the field.

Warning Deleting a field is destructive and will cause all of its values 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 your text formula: 

  • Click in the text formula input box and use the dropdown list to select your desired field,
  • Type numbers or mathematical operators (eg. * / + -) directly into the input box, or
  • Type field names directly into the input box.

Above the text formula editor, the Example Output shows you an example output of the current formula:

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 object, you'll see it appear among the suggestions with the format of Field connected with Object > Connected Object. 

 

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}
    image alt text
  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}
    image alt text
  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

Unique shipment tracking URLs

For a more complex example of the what you can do with text formulas, take a look at this Knack forum contribution on how to create unique shipment tracking URLs. Note that this field type used to be called "Text Combos", which is why you see it referenced in the post.

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:

image alt text

Knack Tip 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 Equations. So, the left function, left(String, End Position), takes both String and End Position as arguments.

Using Text Formulas

Builder: Data > Records

Text formula values will display in the "Records" tab in an object 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.

Builder: Pages > Views

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

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 object, 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 object's records. Do contact Knack support if these field values continue to stay blank.

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 object

How To Guides

Related articles