Collection_Driver

Performs a Collection_Create, Collection_Group, Collection_Calc and Collection_Total in a single function. This single function is much faster than performing each function separately.

Arguments

Collection_Driver(
<source>	// Collection Location, e.g. an MDB.
, <schema>	// Collection name, e.g table name within MDB.
, <selection_criteria>	// What rows to extract.
, <Create_attr_list>	// What attributes to instantiate.
, <Group_list>	// How to perform the Grouping.
, <Calc_list>	// How to perform the Calculation.
, <total spec>	// How to perform the Total.
)

Example

In this example, a Collection_Driver is performing a Create, Group by, and Calculation on the result of a Collection_Create.

Collection_Driver(

"" // <source> is current MDB

, 'FB_LN' // base table

, 'SELF![CAT]="FREIGHT"' // <selection_criteria>

, '[CMDTY_CLASS] // Begin Create List.

[Quantity:CLng(ConvertNulls(SELF![QTY_LABL],0))]

[CAT]

[Line_Num:SELF![LINE_ITEM_NUM]]

' // End Create List.

, '[GROUP] // Begin Group List.

[SUM]

[Line_Num:COUNT]

' // End Group list.

, '[Line_Num:SELF![Line_Num]*10]' // Calc.

, "" // No total.

)

SELF! refers to the collection we are creating from.

The result of the Collection_Create possesses the following attributes:

[CMDTY_CLASS]

[Quantity:CLng(ConvertNulls(SELF![QTY_LABL],0))]

[CAT]

[Line_Num:SELF![LINE_ITEM_NUM]]"

The Collection_Group actions are applied positionally to the expressions. The actions in this example are:

[GROUP][SUM][Line_Num:COUNT], where:

Group is applied to [CMDTY_CLASS]
Sum is applied to [Quantity]

'First in list' is applied to [CAT] ( FIRST is used as default if nothing is
specified).
Count is applied to [Line_Num:SELF![LINE_ITEM_NUM]]

Collection_Calc is used for: [Line_Num:SELF![Line_Num]*SELF![Line_Num]]

Collection_Total is not performed, so the total spec is an empty string.

The first four parameters are required. The last three parameters are optional. If you provide a total spec, then a single value is returned, otherwise a collection.

Notes

<table name> parameter has been modified so that either a “Table_Name” “Table_Name:Index_Name” can be used. “Index_Name” is optional. If it is not given (as has been true to this point), then “PrimaryKey” is assumed. In addition, this function can be passed fewer keys in the <row select> parameter than are contained in the index being used. If fewer keys are provided, then a Find_First where the provided keys are equal is performed. For example, the first two keys of a three key index could be used. For Collection_Create (and Driver), the resulting collection will be populated based on all records which match the supplied keys (as was previously the case).

This function can limit its search one of several ways, keeping in mind that “Lookup” routines find a single record (the first match), while “Collection” routines find all which match the request:

1. A simple value . . . which implies a single key search.

2. A list of values, such as “[v1] [v2] [v3]” which implies a multi-key search even though the index may have more keys.

3. An SQL where clause using SELF! [ ] references.

4. A list of values (as in option 2) followed by “ AND “ then an SQL where clause. For example: “[v1] [v2] AND SELF![MyFld] = ‘v3’”