You are here:
Notes about Filtering on Types of Fields and Values
Keep these tips in mind when filtering on text fields, date fields, numeric values, picklist values, and blank or null values.
Filtering on Text Fields
- Separate search terms by commas to filter by more than one value. For example, to search for accounts in California, New York, or Washington, use State contains CA,NY,WA.
- Filtering is case-sensitive. For example, searching State contains ID returns all matches for “ID”, but doesn't return any instances of “Florida” and “Idaho” because they contain “id” in their names.
- When you filter on standard long text area fields, such as Description or Solution Details, only the first 1000 characters of the field are searched for matches in reports. Only the first 255 characters are shown for custom long text area fields in list views.
- If your org has Enable Middle Names for Person
Names turned on, which adds a "Middle Name" field to standard objects like
users, leads, and contacts, then you may notice that the "Full Name" field on reports
excludes middle names.
For example, if a user's first, middle, and last name are Nadia Nancy Smith, then in reports the full name appears as "Nadia Smith".
As a work-around, consider adding a person's middle name to their "First Name" field; "Nadia" would become "Nadia Nancy".
A missing middle name could cause issues if you’re filtering reports on the "Full Name" field, because filtering by Full Name equals Nadia Nancy Smith doesn't return the user. Instead, filter the report by Full Name equals Nadia Smith or add three filters:
- First Name equals Nadia
- Middle Name equals Nancy
- Last Name equals Smith
Note Case reports and Lead reports are affected by the discrepancy between Full Name and Middle Name described in this article. Other reports may also be affected, but not all reports are. - Field formatting for names in list view differs between Classic and Lightning. In Classic, names use the format "Lastname, Firstname". In Lightning, names use the format Firstname Lastname.
Filtering on Date Fields
- If entering a date, use the format allowed by your Locale setting. You can also use special date values like TODAY, NEXT WEEK, NEXT YEAR, LAST <number> DAYS, and so on.
- The standard Birthdate field allows you to filter the birthdate based only on the month and day, ignoring the year. The year is always included when you filter on any custom date fields.
Filtering on Numeric Values
- Place quotation marks around numbers or other data that includes commas. For example Amount equals "10,000" returns records that have an amount of $10,000 but Amount equals 10,000 returns $10,000 as well as $10 and $0.
- To search for phone numbers, include the exact phone number formatting or example, Phone starts with (561).
Filtering on Picklist Values
- When filtering on multi-select picklist fields, use a semicolon
between values to specify an exact match.
For example, selecting the “equals” operator and a semicolon between two values includes records with both values specified, excluding all other values.
- If your organization uses record types, the lookup dialog lists picklist values for all record
types. Use the “equals” or “not equal to” operators for these
filters.Note If you change the label for a picklist value that’s used as a filter criterion, the picklist value is automatically removed from the filter criteria. For example, if your report contains a filter where Lead Source equals Email or Web and you change the picklist value Web to Referral, your report filter changes to Lead Source equals Email. If the changed picklist value was the only value specified for a particular filter, it continues to show up in your filters, but an error appears.
Filtering on Blank or Null Values
- When you use the “less than,” "greater
than,” “less or equal,” or “greater or equal” operators
on fields that have numeric values, records with blank or “null” values are
returned as if their value is zero (0). For example, if you create a workflow rule or a
lead assignment rule for accounts with the criteria Annual Revenue less than
100000, account records match if their Annual Revenue
is blank.
However, records with blank field values aren’t considered matches in report filters, custom list views, and account assignment rules (which assign accounts to territories).
- To limit results to records that are blank or contain “null” values for a particular field, choose the field and the “equals” or “not equal to” operators, leaving the third field blank. For example, Amount equals returns records with blank amount fields. You can search for blank values or other specified values at the same time. For example, Amount equals 1,,2 returns records where the Amount is blank or contains the value “1” or “2”.
