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.
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) |
|||||