Differences

This shows you the differences between two versions of the page.

Link to this comparison view

expression function - build a list [2016/09/14 14:19] (current)
Line 1: Line 1:
 +====== 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. ​
 +
 +{{:​build_a_list_initial.png?​direct&​600|}}
 +===== Parameters =====
 +The **Build a List** function has three parameters, one of which are required:
 +
 +==== Inputs ====
 +
 +Allowed Inputs: [[Field|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|field]] values on one or many [[record|record(s)]] in a [[Table|table]].
 +==== Separator ====
 +
 +Allowed Inputs: [[Field|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, 2019