Search and filter using Analytics functions

You can use Analytics functions to perform powerful and effective searching and filtering of data in tables.

To use a function to search or filter, you create a filter in the Filter text box at the top of the table view. The filter uses one of the Analytics functions explained below.

For example, this filter uses the FINDMULTI( ) function to isolate all records that contain at least one of the search terms anywhere in the record:

Guidelines for searching or filtering using functions

Field names	When you specify a field name to search in, it must be the physical field name in the table layout, not the display name in the table view. Tip To see the physical field name, right-click a column header in the table view and select Properties.
Quotation marks	Text search terms must be enclosed in "quotation marks".
Back quotes	Datetime search terms must be enclosed in `back quotes`.
Datetime format	Datetime search terms must use YYYYMMDD or YYMMDD format. Any time portion must use hhmmss format, and be preceded a single blank space, the letter ‘t’, or the letter ‘T’. For example: `t183000` Do not use any separators such as slashes (/) or colons (:) between the individual components of dates or times.
Related fields	To search in a related field you must specify the fully qualified field name: table name.field name.
Function rules	Each function has specific rules that govern how it works – things like supported data types, and case-sensitivity. For a high-level comparison of the rules governing Analytics search functions, see A comparison of Analytics search functions. For detailed information about any function, click the linked function name below.

Types of searches

You can use a function to search or filter text, numeric, or datetime data. However, you need to use the right function for the type of data that you are searching or filtering:

• Data type supported by a function Functions are designed to work with a specific data type, or in some cases they can work with more than one data type.

  For example, you can use the ISBLANK( ) function with text data (character data) but not with numeric or datetime data. You can use the MATCH( ) or BETWEEN( ) functions with character, numeric, or datetime data.

• Data type of the data You need to be aware of the data type of the data that you are searching or filtering and use a function appropriate for the data type. Numbers and dates typically have a numeric or datetime data type. However, they may be using a character data type.

Text searches (character data type)

Search for a single text term

Use: FIND( ) function

Description: The search function with the fewest restrictions. Not case-sensitive. Allows searching entire records in addition to searching an individual field or fields.

Example	Result
FIND("United Equipment")	Isolates all records that contain the name “United Equipment” anywhere in the record.
FIND("Equip")	Isolates all records that contain the string “Equip” anywhere in the record.
FIND("United Equipment", Vendor_Name)	Isolates all records that contain the name “United Equipment” in the Vendor_Name field.
FIND("United Equipment", Vendor.Vendor_Name)	Isolates all records that contain the name “United Equipment” in the Vendor_Name field in the related Vendor table.

Search for blank text values

Use: ISBLANK( ) function

Description: Allows you to search for blank values in a character field.

Example	Result
ISBLANK(First_Name)	Isolates all records with a blank First_Name field.

Search for multiple text terms

Use: FINDMULTI( ) function

Description: The same as FIND( ), but allows specifying multiple search terms.

Example	Result
FINDMULTI(RECORD, "United Equipment", "Muller Corp.")	Isolates all records that contain the name “United Equipment” or "Muller Corp." anywhere in the record.
FINDMULTI(RECORD, "equip", "supp")	Isolates all records that contain the strings “equip” or "supp" anywhere in the record.
FINDMULTI(Vendor_Name, "United Equipment", "Muller Corp.")	Isolates all records that contain the name “United Equipment” or "Muller Corp." in the Vendor_Name field.
FINDMULTI(Vendor.Vendor_Name, "United Equipment", "Muller Corp.")	Isolates all records that contain the name “United Equipment” or "Muller Corp." in the Vendor_Name field in the related Vendor table.

Use: MATCH( ) function

Description: A versatile search function that allows you to search a field for multiple search terms simultaneously, or search multiple fields for the same search term. Also allows you to find matching values in two fields.

Example	Result
MATCH(Vendor_City, "Phoenix", "Austin", "Los Angeles")	Isolates all records in which the value in the Vendor_City field exactly matches, or begins with, “Phoenix”, “Austin”, or “Los Angeles”.
NOT MATCH(Vendor_City, "Phoenix", "Austin", "Los Angeles")	Isolates all records in which the value in the Vendor_City field does not exactly match, or begin with, “Phoenix”, “Austin”, or “Los Angeles”.
MATCH(Product_Code, "A", "D", "F")	Isolates all records that have product codes “A”, “D”, or “F”, or product codes beginning with “A”, “D”, or “F”, in the Product_Code field.
MATCH(Product_Code, "A", "D", "F")	Isolates all records that have one-character product codes “A”, “D”, or “F” in the Product_Code field. The Exact Character Comparisons option must be on.
Note MATCH( ) examples assume that the Exact Character Comparisons option is off, except where noted.

Search for case-sensitive text terms

Use: MATCH( ) function

Description: A versatile search function that allows you to search a field for multiple search terms simultaneously, or search multiple fields for the same search term. Also allows you to find matching values in two fields.

Example	Result
MATCH(Last_Name, "SMITH")	Isolates all records in which the value in the Last_Name field is "SMITH", all uppercase.
MATCH(Last_Name, "smith")	Isolates all records in which the value in the Last_Name field is "smith", all lowercase.
MATCH(Last_Name, "Smith")	Isolates all records in which the value in the Last_Name field is "Smith", proper case.

Search for a text term in multiple fields

Use: MATCH( ) function

Description: A versatile search function that allows you to search a field for multiple search terms simultaneously, or search multiple fields for the same search term. Also allows you to find matching values in two fields.

Example	Result
MATCH("Phoenix", Vendor_City, City, City_2)	Isolates all records in which at least one of the values in the Vendor_City, City, or City_2 fields exactly matches, or begins with, “Phoenix”.

Search for matching text terms

Use: MATCH( ) function

Description: A versatile search function that allows you to search a field for multiple search terms simultaneously, or search multiple fields for the same search term. Also allows you to find matching values in two fields.

Example	Result
MATCH(Vendor_Address, Employee_Address)	Isolates all records with identical vendor and employee addresses. You may need to use additional functions to standardize the format of vendor and employee addresses.

Search for one or more occurrences of a specific character or substring

Use: OCCURS( ) function

Description: Allows you to search for one or multiple occurrences of a substring in a character field.

Example	Result
OCCURS(Invoice_Number, "-") > 1	Isolates all records in which the invoice number contains 2 or more hyphens.
OCCURS(Full_Name, ALLTRIM(Last_Name))=1	Isolates all records in which the value in the Last_Name field appears in the Full_Name field. Including the ALLTRIM( ) function in the expression removes any leading or trailing spaces from the Last_Name field, ensuring that only text values are compared.
OCCURS(Vendor_Name, "UNITED EQUIPMENT") > 0	Isolates all records that contain the name “UNITED EQUIPMENT”, in uppercase, in the Vendor_Name field. Unlike the FIND( ) function, the OCCURS( ) function is case sensitive.

Search for a substring starting at a specific character position

Use: AT( ) function

Description: Allows you to search for a substring, or a subsequent occurrence of the substring, in a character field, and specify the starting byte position of the target substring.

Example	Result
AT(2, "-", Invoice_Number) > 10	Isolates all records in which the invoice number contains 2 or more hyphens, and the second hyphen occurs after the tenth character in the string.

Search for text in a range

Use: BETWEEN( ) function

Description: Allows you to search for text values that fall within a range.

Example	Result
BETWEEN(Last_Name, "C", "K")	Isolates all records in which the value in the Last_Name field begins with one of the letters from “C” to “K”, inclusive. The Exact Character Comparisons option must be off.

Search for nearly identical text values (fuzzy duplicates)

Use: ISFUZZYDUP( ) function

Description: Allows you to search for nearly identical values (fuzzy duplicates), as well as identical values. Not case-sensitive.

Use: LEVDIST( ) function

Description: Similar to ISFUZZYDUP( ), but case-sensitive by default.

Example	Result
ISFUZZYDUP(Last_Name, "Braun", 2)	Isolates all records with the name “Braun”, or fuzzy duplicates of the name “Braun”, in the Last_Name field. The Levenshtein distance (degree of fuzziness), set to 2 in this example, can be increased or decreased.
LEVDIST(TRIM(Last_Name), "Braun") < 3	Isolates all records with the name “Braun”, or fuzzy duplicates of the name “Braun”, in the Last_Name field. The Levenshtein distance (degree of fuzziness), set to < 3 in this example, can be increased or decreased. Including the TRIM( ) function in the expression removes any trailing spaces from the last name field, ensuring that only text values are compared.

Search for a basic pattern

Use: MAP( ) function

Description: Allows you to search using wildcard characters, literal characters, or a mix of both.

Example	Result
MAP(Invoice_Number, "XX99999")	Isolates all records with invoice numbers that consist of, or that start with, two letters followed by five numbers.
MAP(Invoice_Number, "AB12345")	Isolates all records with invoice numbers that are exactly “AB12345”, or that start with “AB12345”.
MAP(Invoice_Number, "AB99999")	Isolates all records with invoice numbers that consist of, or that start with, “AB” followed by five numbers.
NOT MAP(SSN, "999-99-9999")	Isolates all records that do not match the standard format of social security numbers in the SSN field.

Search for a more complicated pattern

Use: REGEXFIND( ) function

Description: The most powerful and flexible search function. Allows you to search using regular expressions that combine literal characters and metacharacters. Can be more complicated to use than other search functions.

Example	Result
REGEXFIND(Vendor_City, "Phoenix|Austin|Los Angeles")	Isolates all records in which the value in the Vendor_City field contains “Phoenix”, “Austin”, or “Los Angeles”.
REGEXFIND(Product_Code, "\b\d{3}-[a-zA-Z]{6}\b")	Isolates all records with a product code that starts with 3 numbers, followed by a hyphen and 6 letters.
REGEXFIND(Product_Code, "\b\d{3,}-[a-zA-Z]{6}")	Isolates all records with a product code that starts with 3 or more numbers, followed by a hyphen and 6 or more letters.

Numeric searches

Search for a number

Use: MATCH( ) function

Description: A versatile search function that allows you to search a field for multiple search terms simultaneously, or search multiple fields for the same search term. Also allows you to find matching values in two fields.

Example	Result
MATCH(Invoice_Amount,154.00)	Isolates all records with an invoice amount of $154.00.
MATCH(Invoice_Amount,154.00, 522.00)	Isolates all records with an invoice amount of $154.00 or $522.00.
NOT MATCH(Inventory_Value_at_Cost, Cost_x_Quantity)	Isolates all records with different amounts in the Inventory_Value_at_Cost field and the computed Cost_x_Quantity field.

Search for numbers in a range

Use: BETWEEN( ) function

Description: Allows you to search for numeric values that fall within a range.

Example	Result
BETWEEN(Invoice_Amount, 1000, 5000)	Isolates all records with an invoice amount from $1000 to $5000, inclusive.

Search for a number throughout an entire table

Use: FIND( ) function

Description: Allows searching entire records in addition to searching an individual field or fields.

Use: FINDMULTI( ) function

Description: The same as FIND( ), but allows specifying multiple search terms.

Example	Result
FIND("154.00")	Isolates all records that contain the exact characters 154.00 anywhere in the record in the source data file.

Datetime searches

Search for a datetime value

Use: MATCH( ) function

Description: A versatile search function that allows you to search a field for multiple search terms simultaneously, or search multiple fields for the same search term. Also allows you to find matching values in two fields.

Example	Result
MATCH(Invoice_Date, `20170731`)	Isolates all records with an invoice date of 31 Jul 2017.
MATCH(Invoice_Date, `20170731`, `20170831`, `20170930`)	Isolates all records with an invoice dated the last day of the month in each month of the third quarter.

Search for blank or invalid date values

Use: VERIFY( ) function

Description: Allows you to search for blank or invalid values in a date field.

Example	Result
NOT VERIFY(Invoice_Date)	Isolates all records with a blank or invalid date in the Invoice_Date field.

Search for datetime values in a range

Use: BETWEEN( ) function

Description: Allows you to search for datetime values that fall within a range.

Example	Result
BETWEEN(Invoice_Date, `20140930`, `20141030`)	Isolates all records with an invoice date from 30 Sep 2014 to 30 Oct 2014, inclusive.
NOT BETWEEN(Invoice_Date, `20140930`, `20141030`)	Isolates all records with an invoice date that does not fall between 30 Sep 2014 and 30 Oct 2014, inclusive.

Search for a datetime value throughout an entire table

Use: FIND( ) function

Description: Allows searching entire records in addition to searching an individual field or fields.

Use: FINDMULTI( ) function

Description: The same as FIND( ), but allows specifying multiple search terms.

Example	Result
FINDMULTI(RECORD, "31/07/2017", "31/08/2017")	Isolates all records that contain the exact characters 31/07/2017 or 31/08/2017 anywhere in the record in the source data file. The normal restriction regarding datetime format (YYYYMMDD, YYMMDD, hhmmss, hhmm) does not apply when using FIND( ) or FINDMULTI( ) to search for a datetime value.

A comparison of Analytics search functions

The tables below provide a high-level comparison of Analytics search functions. As you construct search expressions in Analytics it can be useful to know how the specific rules that govern each function may differ.

Data types when searching

Search locations (field, fields, record)

Leading spaces searchable

Case-sensitivity

Partial matching

Multiple search terms

Affected by Exact Character Comparisons option (SET EXACT ON/OFF)