Collection_Group
Purpose
Returns a new collection that is an aggregate or grouping of a collection. The
action is analogous to SQL “GROUP BY”.
Arguments
Collection_Group( <coll>, <group_list> )
Examples
Collection_Group
(Collection_Create
(""
, "FB_LN"
, "SELF![CAT]='FREIGHT'"
, "[CMDTY_CLASS]
[Quantity:CLng(ConvertNulls(SELF![QTY_LABL],0))]
[CAT]
[Line:SELF![LINE_ITEM_NUM]]"
)
, "[Group][Sum][Line:Count]"
//Group list
)
Notes
In this example, Collection_Group is grouping the collection resulting from a Collection_Create. SELF! refers to the collection we are creating from.
The collection atributes are:
"[CMDTY_CLASS]
[Quantity:CLng(ConvertNulls(SELF![QTY_LABL],0))]
[CAT]
[Line:SELF![LINE_ITEM_NUM]]"
The group functions are applied positionally to the attributes:
[Group] Group is applied to [CMDTY_CLASS]
[SUM]
Sum is applied to [Quantity]
[CAT]
By default, First is applied to [CAT].
[Line:COUNT]" Count is applied to [Line]
In general, an item in a group list is of the form:
[<attribute name>:<group function>] or [<group function>]
Where the <attribute name> is omitted, the group function is applied to the attribute whose position in
the attribute list of the collection corresponds to the position of the group
function in the group list.
The effective order of the attributes in the group list determines the
major-to-minor sort order on which grouping is based.
Group Functions
Sum
| Sum the total of the values in the group.
|
Count
| Count the number of occurrences in the group.
|
List
| Build a list of the values from the bracketed list in the group.
|
Av
| Figure the average for the values in the group that has a value other than
null.
|
Min
| Use the minimum value in the group.
|
Max
| Use the maximum value in the group.
|
Cat
| Concatenate string values in the order of occurrence.
|
First
| Use the first value in the group.
|
Last
| Use the last value in the group.
|