Build a List (Expression Function)

Purpose

The Build a List function (BUILD_A_LIST) is used to create a list of separated values from multiple field values.

Parameters

The Build a List function has three parameters, one of which are required:

Inputs

Allowed Inputs: field values

The first required parameter of the Build a List function is the field values that will be concatenated together to form the list. The input is a query built using the Query Builder to find field values on one or many record(s) in a table.

Separator

Allowed Inputs: field value or text string

The second parameter, which is optional, is a string used to denote what type of separator the list uses. If no value is entered for the second parameter, the function uses a comma as the separator. The value can either be obtained from a field using the Query Builder or it can be a text string.

Remove Duplicates

Allowed Inputs: omitted or “yes”

The third parameter, which is optional, is a string used to determine if duplicate values will be removed from the list. If this parameter is omitted then duplicates will not be removed from the resulting list. If the parameter is set to “yes” then any duplicates found in the list will be removed. If this parameter is used then the separator must also be defined in the second parameter

Output

The output of the Build a List function is a string comprised of all the values found by the query separated by the value provided in the separator. If only one value is found then no separator is added. For example if BUILD_A_LIST(${Contact - First Name},“, ”) only finds “Bob” then the result will be “Bob” but if the query returns “Bob” and “Alice” then the result will be “Bob, Alice”

Example

Most often used to build comma-separated record IDs to store in a multi-select field. E.g. if an Invoice record has two related Line Item records with WX System IDs “a495683|u123” and “a495683|u125”, BUILD_A_LIST(${Line Item ~> WX System ID},“,”) would result in “a495683|u123,a495683|u125” (a valid input for a multi-selection field).

expression function - build a list.txt · Last modified: 2016/09/14 14:19 (external edit)
Copyright WorkXpress, 2020