You can use an Analytics command to select the first record in a table that matches the search criteria. The record is selected, but not isolated, unlike the other types of searching in Analytics. The rest of the records are still present in the table view.
Usefulness in scripts
The ability to select the first matching record is primarily useful in Analytics scripts. For example, in conjunction with other scripting techniques, the commands below can be used to move sequentially through records in a table as a precursor to performing a repeated action based on the contents of each selected record.
Search dialog box
In the Analytics user interface, you access the commands in the Search dialog box (Data > Search).
The table below explains the different options in the Search dialog box. It also provides the equivalent ACLScript commands on the assumption that the options are primarily useful in Analytics scripts.
Note
You can click any command name below for detailed information about the command.
|
Search dialog box option |
Equivalent Analytics command |
Description |
|---|---|---|
|
Locate Record |
Selects a specific record number in a table. |
|
|
Locate If |
Selects the first occurrence of any type of literal, or of an expression that uses any data type, or mix of data types. The table does not have to be indexed. For example:
|
|
|
Find Literal |
Selects the first occurrence of a character literal (for example, New York) in a character field indexed in ascending order. Note The FIND command and the FIND( ) function are two separate Analytics features with significant differences. |
|
|
Seek Expression |
Selects the first occurrence of a character literal (for example, “New York”), or a character expression (for example, v_city), in a character field indexed in ascending order. |
Index requirement
To use the Find Literal or Seek Expression options, you must first index the character field that you want to search, in ascending order. Both options search in the indexed field only.
If a table is indexed by more than one field (a nested index), only the primary key field is searched, assuming it is a character field indexed in ascending order. If an index is conditional, any records excluded from the view are also excluded from the search.
Guidelines
| Data type |
All options can be used with character fields. Only the Locate If option can be used with datetime or numeric fields. |
|---|---|
| Partial matching |
Partial matching is supported when searching character fields, however the search string must appear at the start of the field. For example, Vendor_Name = "Uni" finds “United Equipment”, but Vendor_Name = "Equip" does not. |
| Case sensitivity | When used to search character fields, all options are case-sensitive. |
| Performance | The Locate If option searches a table sequentially and therefore is slower than the Find Literal or Seek Expression options, which search indexed tables. However, the Locate If option does not require the time spent to index a table. |
| Record order | The Locate If option maintains the original order of the records in a table, which depending on the nature of your analysis may be desirable. |
Select a specific record number in a table
- From the Analytics main menu, select Data > Search > Locate Record.
- Type the record number in the Expression text box
and click OK.
If the record number is found, it is selected and the table is positioned at the record.
Select the first occurrence of any type of literal or expression
- From the Analytics main menu, select Data > Search > Locate If.
- Do
one of the following:
- Enter an expression in the Expression text box and click OK.
- Click Expression to open the Expression Builder, create an expression, click OK, and click OK again.
The expression can be as simple or as complex as required, can involve one field or multiple fields, and can mix data types. For example:
Vendor_Name = "United Equipment"
Invoice_Amount > 1000
Vendor_Name = "United Equipment" AND Invoice_Amount > 1000 AND Invoice_Date > `20140930`
You must enclose character literal values in quotation marks, and datetime values in backquotes.
If the specified value is found, the table is positioned at that record.
If the specified value is not found, the table is positioned at the first record in the table.
Select the first occurrence of a character literal in an indexed table
- Activate an index for the table you want to search.
The table must be indexed by the character field you want to search.
- From the Analytics main menu, select Data > Search > Find Literal.
-
Type a character literal value in the Expression text box and click OK.
Do not enclose the character literal value in quotation marks unless the quotation marks are part of the data in the field. For example:
-
United Equipment
-
R (to find the first value starting with “R”)
If the specified value is found, the table is positioned at that record.
If the specified value is not found, the message “No index matched key” is displayed. The table is positioned at the first record with a key field value greater than the specified value, or at the first record in the table if no value is greater than the specified value.
-
Select the first occurrence of a character literal or expression in an indexed table
- Activate an index for the table you want to search.
The table must be indexed by the character field you want to search.
- From the Analytics main menu, select Data > Search > Seek Expression.
- Do
one of the following:
- Enter a character-type expression or a character literal value in the Expression text box and click OK.
- Click Expression to open the Expression Builder, create an expression, click OK, and click OK again.
For example:
v_vendor_name
"United Equipment"
You must enclose character literal values in quotation marks.
If the specified value is found, the table is positioned at that record.
If the specified value is not found, the message “No index matched key” is displayed. The table is positioned at the first record with a key field value greater than the specified value, or at the first record in the table if no value is greater than the specified value.
A comparison of Analytics search commands
The table below provides a high-level comparison of Analytics search commands. If you use any of the commands in an Analytics script, it can be useful to know how the specific rules that govern each command may differ.
| Locate Record / Locate If | Find Literal | Seek Expression | |
|---|---|---|---|
| Data types searchable |
(you can also search by record number) |
Character | |
| Searches in |
|
Field | |
| Searches in related fields |
Yes (fully qualified field name must be specified) |
Yes | |
| Index required | No |
Yes (ascending order required) |
|
| Leading spaces searchable |
Yes (spaces in data or search string treated like a character) |
No |
Yes (spaces in data or search string treated like a character) |
| Case-sensitive | Yes | ||
| Partial matching |
Yes (search string must appear at the start of the field, Character only) |
Yes (search string must appear at the start of the field) |
|
| Quotation marks around search term required |
|
No (search term must not be enclosed in quotation marks, unless the quotation marks are part of the data) | Yes |
| Affected by Exact Character Comparisons option (SET EXACT ON/OFF) |
Yes | No | |
| Expressions supported | Yes | No | Yes |
| Additional remarks | The Locate Record and the Locate If operations in the Search dialog box, and the LOCATE RECORD/LOCATE command, are identical. | The Find Literal operation in the Search dialog box, and the FIND command, are identical. | The Seek Expression operation in the Search dialog box, and the SEEK command, are identical. |
