Indexing records

You can index records by one or more key fields in the active table, and use the resulting index to temporarily reorder the records without affecting the underlying physical order of the data.

To index records:

  1. Select Data > Create Index.
  2. On the Main tab, do one of the following:
    • Select the field(s) to index from the Index On list.

    • Click Index On to select the field(s), or to create an expression.

    If you select more than one field, the order in which you select the fields dictates the nested indexing priority. The records are indexed by the first field you select, and if there are multiple occurrences of a value in the first field, the records within the group are then indexed by the second field you select, and so on. If you do not select additional fields, records within a group retain their original sort order relative to one another.

    For information about indexing using expressions and computed fields, see Sorting and indexing using computed fields.

    Note

    The combined length of the fields being indexed cannot exceed 247 characters.

  3. If you clicked Index On, you can optionally specify a descending index order for one or more selected fields by clicking the sort arrow (the default is ascending).
  4. If there are records in the current view that you want to exclude from processing, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.

    The IF statement considers all records in the view and filters out those that do not meet the specified condition.

  5. Do one of the following:
    • In the To text box, specify the name of the index file.

    • Click To and specify the index file name, or select an existing index file in the Save or Save File As dialog box to overwrite the file.

    If ACL prefills an index file name, you can accept the prefilled name, or change it.

    Tip

    A best practice is to give indexes meaningful names that describe the nature of the ordering imposed by the index. For example, “Date_Amount_D” could be the name of an index that orders a table by Date in ascending order, and within each day by Amount in descending order.

  6. Select or deselect Use Output Table depending on whether or not you want to activate the index immediately.

    You can activate a table’s index at any time by selecting it from the Index drop-down list at the top right of the view.

  7. Click the More tab.
  8. Select the appropriate option in the Scope panel.
    • All – This option is selected by default. Leave it selected to specify that all records in the view will be processed.

    • First – Select this option and enter a number in the text box to start processing at the first record in the view and include only the specified number of records.

    • Next – Select this option and enter a number in the text box to start processing at the currently selected record in the view and include only the specified number of records. The actual record number in the leftmost column must be selected, not data in the row.

    • While – Select this option to use a WHILE statement to limit the processing of records in the view based on a particular criterion or set of criteria. You can enter a condition in the While text box, or click While to create a WHILE statement using the Expression Builder.

      A WHILE statement allows records in the view to be processed only while the specified condition evaluates to true. As soon as the condition evaluates to false, the processing terminates, and no further records are considered. You can use the While option in conjunction with the All, First, or Next options.

    Note

    The number of records specified in the First or Next options references either the physical or the indexed order of records in a table, and disregards any filtering or quick sorting applied to the view. However, results of analytical operations respect any filtering.

    If a view is quick sorted, Next behaves like First.

  9. Click OK.
  10. If the overwrite prompt appears, select the appropriate option.
Related concepts
About indexing
Related tasks
Activating and deactivating indexes
Viewing index details
Maintaining indexes
Sorting or indexing records using computed fields


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