FINDMULTI( ) function

Returns a logical value indicating whether any string in a set of one or more specified strings is present in a particular field, or anywhere in an entire record.

Syntax

FINDMULTI({search_in|RECORD}, string_1 <,...n>)

Parameters

Name Type Description
search_in | RECORD

character

The field, or variable, to search in.

Specify the keyword RECORD to search the entire record, including any undefined portion of the record.

You can also specify a list of fields by concatenating field names:

Field_1+Field_2+Field_3
string_1 <,...n>

character

One or more character strings to search for. Separate multiple search strings with commas:

FINDMULTI(RECORD, "Joa", "Jim", "Joh")

The search is not case-sensitive.

Output

Logical. Returns T (true) if any of the specified string values are found, and F (false) otherwise.

Examples

Basic examples

Searching an entire record

Returns T for all records that contain "New York" or "Chicago" in any field, across any field boundaries, and in any undefined portion of the record. Returns F otherwise:

FINDMULTI(RECORD, "New York", "Chicago")

Searching a single field

Returns T for all records that contain "New York" or "Chicago" in the City field. Returns F otherwise:

FINDMULTI(City, "New York", "Chicago")

Returns T for all records that contain the string "Ne" or "Chi" in the City field. Returns F otherwise:

FINDMULTI(City, "Ne", "Chi")

Returns T for all records that contain "New York" or "Chicago" preceded by one or more spaces in the City field. Returns F otherwise:

FINDMULTI(City, " New York", " Chicago")

Returns T for all records that have a value in the Description field that matches, or contains, any of the values in the v_search_term variables . Returns F otherwise:

FINDMULTI(Description, v_search_term_1, v_search_term_2, v_search_term_3)

Searching multiple fields

Returns T for all records that contain the string "New York" or "Chicago" in either the City or the City_2 fields. Returns F otherwise:

FINDMULTI(City+City_2, "New York", "Chicago")

Returns T for all records that contain the string "New York" or "Chicago" in either the City or the City_2 fields. Returns F otherwise:

FINDMULTI(City, "New York", "Chicago") OR FINDMULTI(City_2, "New York", "Chicago")

Combining with other functions

Returns T for all records that have a value in the Last_Name_1 field that matches, or contains, the trimmed value from the Last_Name_2 or Last_Name_3 fields. Returns F otherwise:

FINDMULTI(Last_Name_1, ALLTRIM(Last_Name_2), ALLTRIM(Last_Name_3))

Remarks

When to use FINDMULTI( )

Use the FINDMULTI( ) function to test for the presence of any of the specified strings in a field, two or more fields, or an entire record.

How matching works

The string value can be exactly matched or it can be contained within a longer string. Leading spaces in fields do not affect the search unless you include one or more leading spaces in the string value.

Search an entire record

If you specify RECORD instead of a search_in field, the entire record is searched, including any undefined portion of the record. Field boundaries are ignored when the entire record is searched, and trailing spaces in fields are treated as characters.

Note

When you search an entire record, the physical record is searched. Any computed fields or related fields are not searched.

Search a subset of fields

You can concatenate two or more fields in the search_in parameter if you want to search in a subset of the fields in a table. For example, to search both the City and the City_2 fields for the strings "New York" or "Chicago":

FINDMULTI(City+City_2, "New York", "Chicago") 

The concatenated fields are treated like a single field that includes leading and trailing spaces from the individual fields, unless you use the ALLTRIM( ) function to remove spaces.

You can also build an expression that searches each field individually:

FINDMULTI(City, "New York", "Chicago") OR FINDMULTI(City_2, "New York", "Chicago")

If a string value includes a leading space, search results from the two approaches can differ.

Case sensitivity and Exact Character Comparisons

The FINDMULTI( ) function is not case-sensitive, and finds both ASCII and EBCDIC characters. The function is not affected by the Exact Character Comparisons option (SET EXACT ON/OFF).

Search in a computed field

To search in a computed field you must specify the name of the field in search_in. For example, if Vendor_City is a computed field that isolates the city in an address:

FINDMULTI(Vendor_City, "New York", "Chicago")

Search in a related field

To search in a related field you must specify the fully qualified name of the field (that is, table.field name) in the search_in value:

FINDMULTI(Vendor.Vendor_City, "New York", "Chicago")

Search datetime or numeric data

It is possible to use the FINDMULTI( ) function to search datetime or numeric data at the record level, when specifying RECORD. Specifying a search_in field is not supported for datetime or numeric searching.

The numeric or datetime string values must be enclosed in quotation marks, and need to exactly match the source data formatting rather than the formatting in the view.

Using the FINDMULTI( ) function to search datetime or numeric data in computed or related fields is not supported.

Note

Using the FINDMULTI( ) function to search datetime or numeric data is not recommended because it can be difficult to do successfully.

ACL Scripting Guide 14.1