About quick searching

You can enter one or more search terms in the Filter text box at the top of the View tab to perform a quick search of the data in a table (Figure 1). All the source data in the table is searched, not just the data displayed in the current view. For information about source data, tables, and views, see The structure of ACL tables.

Figure 1. Filter text box with search term

Entire records are searched, including any undefined portion of the record, rather than specific fields. For example, entering casino finds records that contain “casino” anywhere in the record. You can subsequently modify the search if you need to search a specific character field.

Searching character data is the most straightforward use of quick search. You can also search numeric and datetime data, however there are some additional considerations to take into account, explained in subsequent sections.

Automatic conversion of search term to a filter

The search term or terms you enter are automatically converted to a global filter that uses the FIND( ) function. The filter auto-populates the Filter text box, from where you can modify it, if required. For example, entering casino results in the filter FIND("casino") (Figure 2), which you could then modify to limit the search to a specific field: FIND("casino", Merchant).

Figure 2. Filter text box with auto-populated filter

The filter is also added to the filter history and to the command log, from where you can subsequently reapply it.

Search terms and filter syntax automatically distinguished

The Filter text box automatically distinguishes between search terms and filter syntax. For example, entering match in the Filter text box searches for the character string “match”, whereas entering match(City, "New York", "Washington") creates a filter using the MATCH( ) function.

Quick searching character data

When quick searching character data, you can enter whole or partial words, or exact phrases. If you enter more than one word, the quick search performs a logical OR operation and finds records that contain at least one of the words. If you want to search for an exact phrase, enclose the phrase in double quotation marks.

To isolate a search term, include a trailing space after the term and enclose the term and the space in double quotation marks. For example, "cash " returns “cash” but not “cashier”, assuming that in the data the string “cash” is followed by at least one space.

Table 1 provides several examples that illustrate how quick searching character data works.

Table 1. Quick searching character data

Search terms

Return records that contain:

cas

  • casino

  • cash

  • Americas

  • Lancashire

  • etcetera . . .

casino

  • casino

  • casinos

casino liquor

  • casino

  • casinos

  • liquor

  • liquors

  • casino (and) liquor (order not considered)

  • etcetera . . .

“Diamond Casino”

  • Diamond Casino

“Diamond Casino” “Golden Casino”

  • Diamond Casino

  • Golden Casino

  • Diamond Casino (and) Golden Casino (order not considered)

casino, “ABC Liquors”

  • casino

  • casinos

  • ABC Liquors

  • casino (and) ABC Liquors (order not considered)

  • etcetera . . .

“ABC L”

  • ABC Liquors

  • ABC Limousine

  • ABC Learning

  • etcetera . . .

“cash ”

(the word ‘cash’ followed by one space)

  • cash

    (in the data, requires that the string ‘cash’ is followed by at least one space)

  • does not return ‘cashier’ or ‘Lancashire’

Quick searching numeric or datetime data

Note

If you want to search for numeric or datetime data in a specific field, use quick filtering. For more information, see About quick filters.

When quick searching numeric or datetime data, you need to remember that you are searching the underlying source data rather than the data displayed in a view. Numbers, dates, and times are often formatted differently in the source data than they are in the view. Search terms need to match the source data formatting rather than the formatting in the view. You can select Edit > Table Layout to view the source data for a table.

Table 2 and Table 3 provide several examples that illustrate how quick searching numeric or datetime data works.

Table 2. Quick searching numeric data

Search terms

Numeric format in view

Numeric format in source data

Return records that contain:

1234.00

9999.99

9999.99

1234.00

9,999.99

no records returned

1,234.00

9,999.99

9999.99

no records returned

9,999.99

1,234.00

9.999,99

no records returned

(1234.00)

(9999.99)

(9999.99)

(1234.00)

-9999.99

no records returned

1234.01

9999.99

(number rounded)

for example: 1234.01

9999.9999

for example: 1234.0085

no records returned

1234.0085

1234.0085

123 456

9999.99

9999.99

  • 123

  • 456

  • 123 (and) 456 (order not considered)

 

Table 3. Quick searching datetime data

Search terms

Datetime format in view

Datetime format in source data

Return records that contain:

12/31/2015

MM/DD/YYYY

MM/DD/YYYY

12/31/2015

DD/MM/YYYY

no records returned

YYYYMMDD

no records returned

31/12/2015

MM/DD/YYYY

no records returned

DD/MM/YYYY

31/12/2015

YYYYMMDD

no records returned

20151231

MM/DD/YYYY

no records returned

DD/MM/YYYY

no records returned

YYYYMMDD

20151231

2015-12-31

YYYY-MM-DD

YYYY-MM-DD

error message

no records returned

FIND("2015-12-31")

2015-12-31

23:59:59

hh:mm:ss

hh:mm:ss

23:59:59

hhmmss

no records returned

20151231.235959

MM/DD/YYYY hh:mm:ss

YYYYMMDD.hhmmss

20151231.235959

MM/DD/YYYY hh:mm:ss

no records returned

Additional characteristics of quick searching

Quick searching has these additional characteristics:

Additional searching and filtering information

For many other data searching options, including using wildcards, see About searching data.

For more information about filtering, see Using filters. For more information about the FIND( ) function, see the ACL Language Reference.

Related tasks
Quick searching a table
Related reference
FIND( ) function


(C) 2015 ACL Services Ltd. All Rights Reserved.