Search

TEXTLIST

Merges a series of text cells into a single text cell, separated by a comma or other separator.

Syntax

TEXTLIST(x, y, n, [d])

where:

  • x: Text is a line item of data type text.
  • y: Separator is the separator that divides the text strings.
  • n: Name of the list in the module that the text comes from.
  • d: Display every list item or just the first instance of each item.

TEXTLIST can also be used as an aggregation function in Blueprint.

x[TEXTLIST: n]         

Format

Input Format Output Format

x: Text

y: Text

n: List

d: Keyword (ALL, UNIQUE)

Text

Arguments

The function uses the following arguments:

  • x: Text: Text-formatted line item, text constant, or general expression
  • y: Text: Text-formatted line item, text constant, or general expression
  • n: Text: Text-formatted list
  • d: Keywords: 'ALL' or 'UNIQUE'

Constraints

The function has the following constraints:

  • The TEXTLIST aggregation function has a built in limit of 10,000 characters, to prevent enormous text strings being created. At 10,000 characters it will stop appending to the list and add an ellipsis(...) to indicate more items.
  • Commas in the source text are allowed.
  • The Users list cannot be used in the TEXTLIST function.

Excel equivalent

Example

In this example, the text contained in the Customer list is merged into a single text cell, separated by a comma.

The TEXTLIST all entries line item uses the following formula:

TEXTLIST(Sales Transactions.Customer, ", ", Transaction,ALL)        

Unique items only

In this example, the non-unique entries are removed, so that Birmingham for example only shows Fairgreen Furniture Ltd once, even though it appears twice in the list.

The TEXTLIST unique entries only line item uses the following formula:

TEXTLIST(Sales Transactions.Customer, ", ", Transactions, UNIQUE)        

TEXTLIST as an Aggregation Function

The TEXTLIST function lets you combine the individual text cells into a comma-separated list: E.g. Apples + Pears + Bananas = Apples, Pears, Bananas.

The formula construction is:

Result = Source[TEXTLIST: Mapping]

The mapping determines which items in the source are combined into each cell of the result. Both the source and the result must be formatted as data type Text.

When using TEXTLIST as an aggregation function, it's not possible to:

  • define a new separator for your aggregated text. The separator used is a comma followed by a space.
  • choose whether to display every list item or just the first instance of each item. Every list item is aggregated when using TEXTLIST as an aggregation function.

This is because when used for aggregation, the TEXTLIST syntax only takes the list to aggregate text from, and the list to map against, as arguments.

Aggregation Function

Suppose you want to combine the list of fruit products sold to each customer into a single cell. The Source Customers source module has dimensions consisting of a transactional record list and line items. Product Text is a text-formatted line item. The Customers line item is list-formatted on Customers so that it can be used for mapping into the TEXTLIST result.

Customer Summary is a module with Customers as line items. The TEXTLIST Products result line item is text-formatted.

Source Customers.Product Text[TEXTLIST:Source Customer.Customers]        

The TEXTLIST aggregation function has a built in limit of 10,000 characters, to prevent enormous text strings being created. At 10,000 characters it will stop appending to the list and add an ellipsis to indicate that there are more items. The ellipsis comes right at the end of the 10,000 character limit, so that if you append two strings that have already been truncated you won't get multiple ellipses, but it does mean that the last name will be truncated rather than stopping at the last full name that will fit.

Similar functions