Sorting and indexing using computed fields

If the format of the data in a key field prevents the accurate sorting or indexing of a table, you may be able to achieve accurate results by using an expression to create a computed field based on the key field, and by sorting or indexing using the computed field. Examples of data format impeding sorting or indexing include numbers that inconsistently contain non-numeric prefixes or leading blanks, and names that are inconsistently capitalized.

You can also sort or index using a computed field to order tables in ways other than a strictly sequential ordering of key field values. For example, you can use a computed field to reverse the order of records in a table, group even dollar values, or group names that contain a particular string.

A best practice when indexing using a computed field is to add the computed field to the view so you can see exactly how the computed values are being used to index the table. When you sort using a computed field, the computed field is automatically included in the new, sorted table.

It is possible to directly incorporate expressions in the sorting and indexing operations, and achieve the same results without creating a computed field, but this approach is not recommended because it hides the actual values that are being used to order the table.

Table 1 provides several examples of sorting and indexing using a computed field, and for comparison includes the key field sequentially ordered. The computed field expression is also included.

Table 1. Sorting and indexing using a computed field

Description

ACL Function

Key field

(physical order)

Key field

(sequential order, non-Unicode)

Computed field

(sequential order)

Key field

(order based on computed field order)

Ignore non-numeric characters and blanks, sort/index on numeric characters only

INCLUDE( )

92

12

T-38

  20

#85

  20

#85

12

92

T-38

12

20

38

85

92

12

  20

T-38

#85

92

Computed field expression:     INCLUDE(dept_ID, “1234567890”)

Avoid sorting/indexing discrepancies caused by differing upper and lower case

UPPER( )

Smythe

JONES

Smith

JOHNSON

SMYTHE

Jones

SMITH

Johnson

JOHNSON

JONES

Johnson

Jones

SMITH

SMYTHE

Smith

Smythe

JOHNSON

JOHNSON

JONES

JONES

SMITH

SMITH

SMYTHE

SMYTHE

JOHNSON

Johnson

JONES

Jones

Smith

SMITH

Smythe

SMYTHE

Computed field expression:     UPPER(last_name)

Invert the physical order of records in a table

Note: requires computed field in descending order; record numbers always display in ascending order even if a descending order is applied

RECNO( )

82.12

87.00

62.79

97.47

43.00

43.00

62.79

82.12

87.00

97.47

1

2

3

4

5

43.00

97.47

62.79

87.00

82.12

Computed field expression:     RECNO( )

Group even dollar amounts, sequentially order amounts within the group

Note: requires a nested sort/index of key field inside computed field

MOD( )

82.12

87.00

62.79

97.47

43.00

43.00

62.79

82.12

87.00

97.47

0.00

0.00

0.12

0.47

0.79

43.00

87.00

82.12

97.47

62.79

Computed field expression:     MOD(trans_amount, 1.00)

Group values containing a particular string, sequentially order values within the group

Note: requires computed field in descending order; requires a nested sort/index of key field inside computed field

FIND( )

Lilydale Hardware

Binford Tools

T

Global Trade Hardware

Triathalon Group

Bolton Distribution

T

Lilydale Hardware

Wholesome Hardware

Global Trade Hardware

T

Wholesome Hardware

Steel Case Manufacturing

Industrial Equipment Co-Op

F

Binford Tools

Industrial Equipment Co-Op

Lilydale Hardware

F

Bolton Distribution

Global Trade Hardware

Steel Case Manufacturing

F

Industrial Equipment Co-Op

Binford Tools

Triathalon Group

F

Steel Case Manufacturing

Bolton Distribution

Wholesome Hardware

F

Triathalon Group

Computed field expression:     FIND(“Hardware”, vendor_name)

Related concepts
Testing sequential order, sorting, and indexing
About testing sequential order
About the Sort Order option and sort sequences
Sorting versus indexing
About sorting
About indexing
Related tasks
Sorting or indexing records using computed fields


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback