Using Indexes to Group Data

From RAD Studio
Jump to: navigation, search

Go Up to Sorting and Indexing


When you use an index in your client dataset, it automatically imposes a sort order on the records. Because of this order, adjacent records usually contain duplicate values on the fields that make up the index. For example, consider the following fragment from an orders table that is indexed on the SalesRep and Customer fields:

SalesRep Customer OrderNo Amount

1

1

5

100

1

1

2

50

1

2

3

200

1

2

6

75

2

1

1

10

2

3

4

200



Because of the sort order, adjacent values in the SalesRep column are duplicated. Within the records for SalesRep 1, adjacent values in the Customer column are duplicated. That is, the data is grouped by SalesRep, and within the SalesRep group it is grouped by Customer. Each grouping has an associated level. In this case, the SalesRep group has level 1 (because it is not nested in any other groups) and the Customer group has level 2 (because it is nested in the group with level 1). Grouping level corresponds to the order of fields in the index.

Client datasets let you determine where the current record lies within any given grouping level. This allows your application to display records differently, depending on whether they are the first record in the group, in the middle of a group, or the last record in a group. For example, you might want to display a field value only if it is on the first record of the group, eliminating the duplicate values. To do this with the previous table results in the following:

SalesRep Customer OrderNo Amount

1

1

5

100

2

50

2

3

200

6

75

2

1

1

10

3

4

200



To determine where the current record falls within any group, use the GetGroupState method. GetGroupState takes an integer giving the level of the group and returns a value indicating where the current record falls the group (first record, last record, or neither).

When you create an index, you can specify the level of grouping it supports (up to the number of fields in the index). GetGroupState can't provide information about groups beyond that level, even if the index sorts records on additional fields.

See Also