Print      Open PDF Version of Online Help

Getting Started > Working with Records > Finding Records > About Filter Conditions

Previous Topic

Next Topic

About Filter Conditions

For searches, different filter conditions are available depending on the data type of the search field, as shown in the following table.

NOTE: In some cases, a search does not return records that have a null value in a field that you use in the criteria for a search or list filter. For example, if you search for opportunities that have a status that is not equal to Won, then opportunities that have a null status are not returned. If you want your search to return records that have a null value in a field, then include an Is NULL condition for that field. In the example used earlier, if you want your search to return opportunities that have a null status as well as opportunities that have a status other than Won, then include an Is Null condition for the status field in the search criteria, as well as the condition to find records that have a status that is not equal to Won.

Data Type

Filter Conditions Available

Number, Integer, Percent, Currency
  • Between
  • Equal to
  • Greater than
  • Less than
  • Is NULL
  • Is not NULL
  • Not equal to
  • Not Between

Date, Time
  • Between
  • Equal to
  • In the last 90 days
  • In the next 90 days
  • On
  • On or after
  • On or before
  • Today *
  • Yesterday*
  • Tomorrow*
  • In the last ? day
  • In the next ? day
  • Last Week*
  • Next Week*
  • This Week*
  • Last Month*
  • Next Month*
  • This Month*
  • This Quarter*
  • Last Quarter*
  • Next Quarter*
  • This Year*
  • Next Year*
  • Last Year*
  • Is NULL*
  • Is Not NULL*
  • Not equal to
  • Not Between

For conditions marked with an asterisk (*), the value field is disabled when the condition is selected.

For the condition In the last ? day, and In the next ? day, the variable ? is substituted with a number of days selected in the filter value field.

Checkbox
  • Is checked
  • Is not checked

Picklist
  • Contains all values
  • Contains at least one value
  • Contains none of the values
  • Equal to
  • Equals any*
  • Is NULL
  • Is not NULL
  • Not equal to
  • Between*
  • Greater than*
  • Less than*

For information about the conditions marked with an asterisk (*), see the About Filter Conditions for Picklist Fields section of this topic.

Text
  • Between
  • Contains all values
  • Contains at least one value
  • Contains none of the values
  • Equal to
  • Greater than
  • Less than
  • Is NULL
  • Is not NULL
  • Not equal to
  • Begins with
  • Does not begin with
  • Is Like
  • Is not Like
  • Not Between

Phone
  • Begins with
  • Between
  • Contains all values
  • Contains at least one value
  • Contains none of the values
  • Equal to
  • Greater than
  • Less than
  • Not equal to

About Filter Conditions for Picklist Fields

For the Between, Greater than, and Less than conditions for picklist fields, Oracle CRM On Demand searches the language independent codes (LICs) of the picklist values and not the picklist values themselves, to find the records that meet the filter criterion that you specify. So, your search results include all records in which the LIC of the picklist value meets the criterion, even if the picklist value itself does not. Similarly, your search results do not include records in which the picklist value meets the criterion, but the LIC of the picklist value does not.

For example, assume that your administrator creates a picklist value called Beta. The LIC for the new picklist value is the same as the picklist value by default. So, a search with the following filter returns records in which the picklist value is Beta:

Between Alpha, Gamma

However, if your administrator later changes the picklist value from Beta to Not Beta, then the LIC for the picklist value does not change. So, if you search for records using the filter shown above, then your search results include all records in which the picklist value is Not Beta, because the LIC for the picklist value is still Beta. However, if you search for records using the following filter, then your search results do not include records in which the picklist value is Not Beta, because the LIC for the picklist value does not meet the specified criterion:

Between Gamma, Zeta

Similarly, if you search for a translated version of a picklist value, then your search results include all records in which the LIC of the picklist value meets the specified criterion, even if the translated picklist value does not.

For conditions for picklist fields other than the Between, Greater than, and Less than conditions, Oracle CRM On Demand searches the picklist values themselves and not the LICs, to find the records that meet the filter criterion that you specify.

Equals Any Filter Condition

The Equals any filter condition searches for records that exactly match any one of the specified values. This filter condition is supported only for picklist fields for which the Lookup icon (magnifying glass) is available in record Detail pages and Edit pages. It is not supported for picklist fields for which a drop-down list of values is available in record Detail pages and Edit pages. For this type of picklist field, use the Contains all values filter condition to search for records that exactly match any one of the specified values.

In addition, the Equals any filter condition is not supported for multi-select picklist fields. For information about searching on multi-select picklist fields, see About Searching on Multi-Select Picklists.

How Filter Conditions Are Used

The following table shows how various filter conditions and operators are used:

Use This

With

To Match

For These Results

Between

Text, numbers or dates

Two values separated by commas

Displays records with values between A and B (excludes values equal to A and B).

Contains all values (equal to)

Text, numbers, or dates

Single or multiple values separated by commas

Displays records matching the exact values. The application does not retrieve substrings of the values, or the same value with different capitalization (uppercase or lowercase).

Contains at least one value

Text only

Single or multiple values separated by commas

Displays records that partially match the values (allows substring matching). For example, entering Brown retrieves records with Browns Trucking, Browning Equipment, and Lester Brown Car Wash.

NOTE: This behavior applies to filtered lists only, and not to other areas in Oracle CRM On Demand that use the condition, Contains At Least One Value.

Also, using this condition affects the speed at which the records are retrieved, because Oracle CRM On Demand looks for partial matches.

Contains none of the values (not equal to)

Text, numbers, or dates

Single or multiple values separated by commas

Displays records not matching the values. This is the inverse of Contains all values.

Equal to

Text, numbers, or dates

Single value for each line. To add values, use the operator OR and add another line.

Displays records matching the values.

Equals any

Picklist

Single or multiple values separated by commas

Displays records that exactly match any one of the values.

NOTE: For more information about the Equals any filter condition, see the About Filter Conditions for Picklist Fields section of this topic.

Greater than

Text, numbers or dates

Single value

Retrieves records with a value greater than the entered value.

Less than

Text, numbers or dates

Single value

Retrieves records containing a value less than the entered value

Operator

AND

Text, numbers, or dates

Two or more conditions

Displays records for which all given conditions are true.

For example, entering Close Date > 10/01/2003 AND Revenue > 500000 will return records that match both criteria.

OR

Text, numbers, or dates

Any condition

Displays records for which at least one condition is true.

For example, entering Industry = Energy OR Billing State=CA returns records that match either of these criteria.

Published 1/9/2017 Copyright © 2005, 2017, Oracle. All rights reserved. Legal Notices.