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 gear-shaped icon on the field and select 'Delete' from the dropdown menu.
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.
Note: 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.
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:
- Number field "Part Number"
- Short Text field "Part Name"
- 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:
- Short Text field "Pre"
- Auto Increment field "Auto ID"
- Short Text field "Suffix"
- Text Formula "Pre-AutoID-Suf" = {Prefix}-{Auto ID}-{Suffix}
- Result, example = AC-3-FRAM
User ID + Last Name = User Display Name
- Auto Increment field "User ID"
- Name field "Name"
- Text Formula field "Last Name" = getNameLast({Name})
- Text Formula "User Display Name" = {User ID}-{Last Name}
- Result, example = 12-Smith
Order ID + Date = Order Display ID
- Auto Increment field "Order ID"
- Date field "Order Date"
- Text Formula "Order Display ID" = {Order ID}.{Order Date}
- 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:
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:
- Not present in a form
- Fields with a "many" connection (since there are many values connected, not a single value from a record that can be pulled in)
- A connection field in another object
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 "}"