Object Reference

Classes
Command Interface
- xf_Command
External
- xf_External_VB6_32bit
- xf_External_VB7_32_64bit
General
System Core
System Library

xf_DataListDataList Base Class

Public Sub CreateAggregateColFromSecondaryList (
PrimaryListIDColNameOrIndex As Variant,
PrimaryListAggColNameOrIndex As Variant,
SecondaryList As xf_DataList,
SecondaryListIDColNameOrIndex As Variant,
SecondaryListAggColNameOrIndex As Variant
)

Collate related FK values from a second DataList as a delimited list.
See overview for more detailed information.
Two lists are loaded with the main and related tables, taking care that the secondary list is ordered first by the column matching the primary list ID column.
This way the aggregation can be achieved with a single loop of the secondary list, rather than a loop for each row in the primary set.
This function was designed to be a very fast replacement to an otherwise slow SQL operation.

PrimaryListIDColNameOrIndex as Variant

The column name or index of the PK ID column in the primary table (this DataList). This will be used to identify related secondary table information.

PrimaryListAggColNameOrIndex as Variant

The column name or index of the column in the primary table into which to write the list.

SecondaryList as xf_DataList

The secondary DataList object, containing the secondary table.

SecondaryListIDColNameOrIndex as Variant

The column name or index of the FK ID column in the secondary table. This will used to match the PK ID column in the primary table.

SecondaryListAggColNameOrIndex as Variant

The column name or index of the column in the secondary table to use to construct the delimited list. The value from thsi column for rows with an FK value matching a given PK value in the primary table will be added to the delimited list.

Call Template:

Dim obj As New xf_DataList

obj.CreateAggregateColFromSecondaryList PrimaryListIDColNameOrIndex, PrimaryListAggColNameOrIndex, SecondaryList, SecondaryListIDColNameOrIndex, SecondaryListAggColNameOrIndex

Example 1:

Dim lstProduct As New xf_DataList
Dim lstOrder As New xf_DataList

lstProduct.AddSQLResultsetDbConn xf.DbConnect, "SELECT TOP 10 ProductID, ProductName, '' AS OrderList FROM Products ORDER BY ProductID"
lstOrder.AddSQLResultsetDbConn xf.DbConnect, "SELECT dt.OrderID, dt.ProductID " _
& " FROM [Order Details] as dt " _
& " INNER JOIN [Orders] ON [Orders].OrderID=dt.OrderID " _
& " WHERE dt.ProductID<11 " _
& " AND YEAR(OrderDate)=1996 " _
& " ORDER BY dt.ProductID, dt.OrderID"

lstProduct.CreateAggregateColFromSecondaryList "ProductID", "OrderList", lstOrder, "ProductID", "OrderID"
lstProduct.PrintVals

Printing  10  Rows: ( 3  Columns)
/1:ProductID /2:ProductName                     /3:OrderList
/1:ProductID /2:ProductName                     /3:OrderList
/1:          /2:                                /3:*

i:1   /1:1         /2:Chai                            /3:10285, 10294, 10317, 10354, 10370
i:2   /1:2         /2:Chang                           /3:10255, 10258, 10264, 10298, 10327, 10335, 10342, 10393
i:3   /1:3         /2:Aniseed Syrup                   /3:10289
i:4   /1:4         /2:Chef Anton's Cajun Seasoning    /3:10309, 10326, 10336, 10339, 10344
i:5   /1:5         /2:Chef Anton's Gumbo Mix          /3:10258, 10262, 10290, 10382
i:6   /1:6         /2:Grandma's Boysenberry Spread    /3:10309, 10325
i:7   /1:7         /2:Uncle Bob's Organic Dried Pears /3:10262, 10385
i:8   /1:8         /2:Northwoods Cranberry Sauce      /3:10344, 10345
i:9   /1:9         /2:Mishi Kobe Niku                 /3:
i:10  /1:10        /2:Ikura                           /3:10273, 10276, 10357, 10389