Description
Use TEXTJOIN() to combine a range of text values across related records.
Use case examples include:
- Testing if a certain value is contained among the set of associated Records.
- Combining all partner names into a single string in a field on the parent Form.
Usage
The TEXTJOIN() function combines the values from a range of text strings, and includes a delimiter you specify between each of the combined text values. The delimiter can be an empty text string, in which case the function effectively concatenates the range of text values.
TEXTJOIN(delimiter, ignore_empty, text_array)
| Argument | Type | Required | Description |
|---|---|---|---|
| delimiter | text string | Yes | Either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string. Numbers are treated as text. Example: ", " to add a comma and a space between the text values. |
| ignore_empty | boolean | Yes | If TRUE, empty cells are ignored. |
| text_array | array | Yes | Array of text values to be joined. Can be values across Subform records or referenced records (when using a multiple reference field). Specify the field containing the values to join using dot notation (e.g. SubformName.SubformField) |
To combine text strings within the context of a single Record, use the CONCAT function.
Examples
1. Did an Individual live in a certain country?
You can test to see whether a certain value is contained among the set of associated Records. For example, you have a Form titled Individuals that contain information about the countries an individual has lived in. In this Form, a Multiple reference field linked to the Countries form has been added to allow individuals to be associated with multiple countries.
We can use TEXTJOIN() to combine all the countries selected into a single set that we can evaluate. Once the countries have been combined, we can now determine whether an individual has lived in a specific country, using the following Formula:
ISNUMBER(SEARCH("Venezuela", TEXTJOIN(", ", TRUE, Countries.name)))
This Formula returns results like this:
2. Combine Partner names into a single string
In a Subform that contains a partner name or other string, you may want to combine all partner names into a single string in a Field on the parent Form.
In this case use the following syntax: TEXTJOIN(", ", TRUE, Partners.Name)