Apsona for Salesforce includes a wide range of search facilities, available for all Salesforce objects, both native and custom. All searches can be "cross-object"
in the sense that you can look for records of one type based on conditions enforced by records of a related type.
- Quick, one-object search on a small number of fields.
- Filtering search, in which you specify match values for fields in either the current object or in objects directly related objects.
- Metric-based search, in which you can use metric functions such as count, sum and average in restricting your search, e.g., finding accounts with at least 3
associated contacts, or finding accounts with total opportunity amount of at least $30,000.
- Exception search, where you seek records of one type based on the absence of records of a related type, e.g., opportunities that have no related
- Quantified search, where you look for records of one type such that all related records of a related type meet a certain condition, e.g., contact
records for which all related tasks have a "completed" status. This search is called quantified because it uses the quantifiers "all" or "none" for
- Nested search, carrying search results across chains of related objects, explained below.
You can also save your search conditions as named filters. Once you have saved a filter for a particular object (e.g., the Opportunity object), you can reuse it in all of
the views for that object - the "All records" console view with list and detail, the Tabular view and the Calendar view.
In addition, you can apply a filter you have saved for one object when searching for records in a related object. For example, suppose you have created a filter named Key
opportunities, which retrieves the opportunities that amount to at least $25,000 and have more than 10 completed tasks in the last week. You can then apply this filter in
the Account object and retrieve all account records that have one or more Key opportunities. We use the term nested
search to refer to such searches which use
filters on related objects.
To use the Quick search feature, you simply type a string into the search box at the top of the object's tab, and type the enter key or click the search icon.
You can also choose the fields in which the search will look for a matching value Clicking the "Search" button displays a small
popup window containing checkboxes for the fields to match.
You can invoke the filtering search by clicking the "Search" button, and then the "More options..." link. This displays a search panel (below) that lets you
specify one or more search terms
. Each search term consists of a field name or metric function, an
operator, and a value. For example, in the search term "Account.Industry equals 'Pharmacy'", the field name is
, the operator
and the value is
. To start the search, you simply choose one or more search terms and click the "Search" button.
To specify a search term, you choose a field by clicking the field chooser - the leftmost dropdown in the search terms panel. You then choose an operator by
clicking the middle dropdown. And finally, you specify a value for the field.
The field chooser (see screen shot at above) contains a left-side panel with the list of available objects, and a right-side panel containing the list of fields
from the object you select in the left-side panel. The list of objects includes the current object at the top, and below it, all the related objects. By
"related object", we mean any object that (a) either contains a field that refers to the current object via a master-detail or lookup relationship, or is referred to
by some field in the current object, and (b) is marked as visible to the current user's profile (see the access rules
for more about visibility). In the above example, the the Contact object refers to the Account object, and is referred to by several other
objects. So we see all of these objects in the left panel of the field chooser.
Notice also that both the left and right panels include search boxes at the top, labeled "Find object..." and "Find field..." into which you can type a search
string to narrow down the list of items shown in the panel.
When the current object refers to another object - e.g., the Contact object refers to the Account object - there is at most one Account record for a given Contact
record. But if some other object refers to the current object, e.g., Campaign Member refers to Contact, there can be many Campaign Member records for a given
Contact record. In such a case, the relationship from the current object is one-to-many, and the field chooser depicts this with an asterisk (*) next to the
related object's name.
Object names and field indicators
As another example, consider the screen shot above, depicting the field chooser that appears when selecting a field for filtering the Account object. In this org,
there are three lookup fields from Contact to Account: one is called
(the built-in one in Salesforce), and the other two are called
. So there are three corresponding options in the field chooser, labeled
Contact via Church Attending
Contact via Referral Agency
. All three are annotated with an asterisk to indicate that
they are one-to-many relationships. This way, you can elect to filter via any of those relationships. This org also contains two lookup fields from Account to
Head of Household
. Therefore, two choices labeled
Contact (Head of Household)
also appear, but without asterisks, because those are one-to-one relationships, since the lookups are from Account to
Salesforce provides each object with a record ID field, which is a field that uniquely identifies each record of the object. The field chooser indicates this
field with a key icon next to it, as in the case of the Contact ID field for the Contact object (above left). Also, the field chooser shows date and date/time fields
in a special way. When you choose a date field, the field chooser lets you specify either all of it, or just the month, the year, day of month or day of week for
matching. For this reason, when you click a date field in the field chooser, it shows radio buttons via which to choose the part of the date to match (above
The filter terms panel understands the data types of the fields for matching. For example, if you choose a picklist field, as shown in the
screen shot above, the operator dropdown shows just four choices, and the matching value is displayed as a dropdown that contains the range of picklist values
for that specific picklist. But if you choose a string field, the operator dropdown shows a number of comparison
operators such as "starts with", "ends with" and "equals", and a text box appears for the matching value. Similarly, if you choose a date field, the value box comes
with an attached popup menu that you can use to specify commonly-used date
values such as "today" or "yesterday" or choose a specific date via a date picker widget, as below.
There are the usual six ordering-related operators: equals
, not equal to
, less than
, at least
, at most
, and greater
. These operators compare strings in dictionary order. For example, if you ask for Account Name less than Q
, you will obtain all records whose
Account Name field has a value starting with any of the characters or alphabets up to but not including the letter Q. This includes all names beginning with A
through P as well as all names that begin with numerals.
Searching in string fields
String data deserves special treatment because strings are the most commonly-used data type. Apsona supports several string operators, as illustrated in the screen
Some of these operators need special mention. The simplest are the is empty
and is not empty
operators, which check for the availability of a value in
the string field. The remaining operators provide other kinds of functionality.
When matching against text fields using any of the "starts with", "ends with" or "contains" operators, you can use the wild-card characters "_" (underscore) to
match one single character, or "%" (percent sign) to match any number of (zero or more) characters. These wild-card characters are
supported natively by SOQL
For example, suppose you wanted to find a contact whose name you vaguely remember to be either
could use a filter term Full Name starts with
, meaning that the matching name should have the letters
as the second and
third letters, followed by any characters and then the string
Matching multiple pieces of text
The is among
and is not among
operators are useful when you want to find records where the field's value exactly matches or does not match a specific
set of strings. These two operators treat the value you provide as a list of strings separated by either commas or line breaks. For example, if you specify a filter term
you are asking for all the records in which the field
fully matches either
. So if a record contains a value
in that field, it will not
match this filter condition.
can use the filter term
Contact.Id is among 003C0000015HOi0,003C000001F1Od2,003C000001fjtDN
to find Contact records whose record ID is one of the three IDs given in the term.
The filtering logic also allows for strings to be separated by line breaks instead of commas. For example, below is a filter term that asks for accounts whose names
are one of the given ones:
This way of specifying match values can be particularly useful when you have a list of names in a CSV file, and you wish to find all the records in
your Salesforce system that match those names. For example, given a list of email addresses in a CSV file, you can use it to find all the contacts with those email
addresses. Simply select the entire column of values (leaving off the header row if there is one), copy it and paste it into the
search box to run the search. You can then export the resulting records or update them all, as needed, via the Tools menu.
The contains any of
and does not contain any of
operators similarly treat the value you provide as a list of strings separated by commas or line breaks. But they look
for partial matches rather than full matches. For example, if you specify a filter term
Account.Name contains any of Plumber,Plumbing,Water treatment
you will obtain records in which the
field contains any one of the three strings
, regardless of where they occur in the value. For example, field values
Joe's Plumbing Hardware
Eagle water treatment chemicals
will both match the filter condition. Notice how this partial matching contrasts with the way is among
is not among
You can narrow your search by adding terms to the condition. Click the "Add term" link to add new terms to the filter. Terms can be composed using the 'and' and
'or' filter logic connectives. For example, the screen shot below illustrates a search for leads with lead source "Phone inquiry", resulting from the "User Conference"
campaign, with phone numbers from one of two specific area codes. Notice that you can use as many terms as you want, and that terms can be created with fields from any
related objects, not just the current object. Notice also that the filter logic box refers to terms by number. The filter logic composition is very
similar to the feature supported natively by Salesforce
Searching date fields
You can match values against date and date/time fields in several ways. The list of comparison operators includes the usual equals
, less than and the like, as well as an in range
operator, as in the screen shot below.
If you select any of the operators other than the in range
operator, you will see a popup menu with which you can select either a fixed date using a date picker
(e.g., specifying that the Opportunity Close Date is on or after 8/15/2015), or a relative
date, such as yesterday
, last week
and the like.
dates are also referred to as floating
dates. The popup menu shows a range of relative dates. The key benefit of using a relative date is that
you don't need to rebuild a filter each time it is run. For example, if you save a filter which looks for the Opportunity Close Date matching last month
, then it
uses the current date as the reference for "last month" regardless of when it is invoked. Thus, if you invoke the filter in July 2015, it will match close dates in June
2015, but if you invoke it in January 2016, it will match close dates in December 2015.
Matching a range of dates
The in range
comparison operator adds some more flexibility. In effect, it lets you specify the two end points of a date range, and finds records whose date field
value falls within the range you specify. The date range popup includes five tabs, letting you specify a range by days, months, quarters, years and relative dates.
The screen shot below shows how to specify a fixed range of days via the "Days" tab.
In this tab, you can use the two side-by-side date pickers to select the start and end dates of the range you wish to use. Then click the "Apply" button to copy the date
range into the search term, and finally click the Search button in the filter editor to execute the search.
To select a range of months, you use the "Months" tab that presents a grid of months.
To select a range, you click the cells in the grid corresponding to the starting and ending months in the desired range, and the desired range will be highlighted. If
you change your mind about your selection, click the "Reset" button to make the selection disappear. Clicking the "Apply" button will copy the selected range into the
The "Quarters" and "Years" tabs provide similar grid-like visuals for selecting the range.
You can also select a range of relative dates using the "Relative" tab of the popup. In the example below, we have selected the range of dates beginning four weeks
ago and ending eight weeks from now.
Searching time fields
When looking for records based on time fields (i.e., time of day), you can provide a match value in either 12-hour or 24-hour formats, e.g.,
. Below is a screen shot illustrating this.
Using metric functions
Metric functions enable you to look for records in an object A, based on metrics on a related object B, when there is a one-to-many relationship from A to B. For
example, there is a one-to-many relationship from Account to Opportunity, since there can be many opportunities related to one account. So you can find all
accounts that have at least 3 opportunities, or that have a total opportunity value of at least $30,000, or any other similar metric function applied to the
The set of metric functions available for a field depends on the field's type. For example, date and date/time fields have "newest" and "oldest" available,
while number and currency fields have "sum", "average", "lowest" and "highest" available. When you click a field in the metrics area, the list if available metric
functions available for that field is shown with radio buttons, so that you can choose the one you want.
There is one special metric function labeled "# Records" that indicates the record count of the related records. You would use this metric for finding records
in the main object based on the number of corresponding records in the related object, as in the above example.
You can use more than one filter term with a metric function, or combine them with non-metric filter terms. For example, you can find account records that have
at least 4 opportunities with total value at least $25,000, and which also have at least 3 contact records and the most recent Task date no earlier than last week.
Given a "main" object A that has a one-to-many relationship to a "secondary" object B, an exception search lets you find records in object A that have no
corresponding records in object B. For example, you could use this search to find account records that have no opportunities, or contact records that have no
Apsona for Salesforce uses the existence (or otherwise) of the record id value of the secondary object to indicate this search. In the above screen shot, we wish
to conduct an exception search that finds all contact records that have no related case record. To do this, we open the field terms panel and choose the Case ID
field, which is the record ID for the Case object, and we choose "does not exist" as the operator. This search will then retrieve all contact records with no
As with other searches, you can combine exception search terms with any other terms, e.g., for finding contacts that have associated cases but are not parts of
any campaigns (via the Campaign Member object).
Suppose you have a main object A that has a one-to-many relationship to a secondary object B, such as the Account object has to the Opportunity
object. Suppose further that you want to find records in object A based on a condition on object B. Less abstractly, suppose we want account records for which the
opportunity stage is "Closed Won". Normally, this would mean that we want all account records that have at least one corresponding opportunity whose stage is
But Apsona lets us go further, and use a quantifier
on the secondary (Opportunity) field. So we can (for example) ask for Account
records all of whose
opportunity records have stage "Closed Won." To examine this idea in a bit more detail, consider the screen shot below.
The first term in the filter requires that only Accounts with two or more Opportunity records must be shown, since we are asking for Opportunity record count. The second term adds the quantifier constraint.
- If we select "at least one record", we're saying that an Account record should be retrieved only if it has at least one corresponding Opportunity whose Stage is Closed Won. In other words, any Account record that doesn't have any Closed Won Opportunities would not qualify.
- If we select "all records", we're saying that an Account record should be retrieved only if it has all of its corresponding Opportunities have a Stage of Closed Won. So with this choice, any account that has even one Opportunity with a Stage that isn't Closed Won wouldn't qualify to be shown.
- If we select "no records", we're saying the opposite: that an Account record should be retrieved only if it has none of its corresponding Opportunities have a Stage of Closed Won. So with this choice, any account that has even one Opportunity with a Stage that isn't Closed Won wouldn't qualify to be shown.
Saving your search
After executing a search with a set of conditions, you can save the search as a filter for later use. To do this:
- Click the "Save as filter" link in the search panel.
- Type a name for the new filter, and click the "Save" button.
- The saved filter will appear in the list of filters.
You can also edit an existing filter and change its name, description or filter conditions. To do this:
- Click the "Filters" menu item under "Tools".
- The list of filters available for the current object is displayed on the left. If you click any of the filters in the list, an editor panel appears on the
right, showing the filter's details which can be edited.
The nested search feature lets you carry search conditions across chains of related objects. The way you use a nested search on a particular object A is to start by
choosing the record ID field of a related object B. Doing so causes Apsona to show all the saved filters available for object B, from which you can choose one for
use. The choice of filter means that you wish to find all records in object A which have at least one related record in object B that satisfies the selected
As an example, suppose you have created an Opportunity filter named "Small opportunities", representing opportunity records of value $5,000 or less. You can then
use the nested filter feature to retrieve all account records that have small opportunities. Moreover, you can save this new search as a filter, say "accounts
with small opportunities", and you can then apply it to on the Contact object. This gives you contacts associated with accounts that have at least one small
opportunity. Notice that we are now able to enforce a condition on the Contact object based on data from the (completely-unrelated) Opportunity object. You can
repeat this process as many times as you want, thus carrying filter conditions across a chain of relationships. We use the term nested filter
to refer to a
filter that uses another filter within it, in this manner.
Note that when you save a nested filter, the containing filter makes a copy of the description of the contained filter. This means that even if you were to
delete the original contained filter, the containing filter will not be affected and will continue to function correctly.
Here is a video demo
showing an example of nested filters.
- You cannot filter by long text or rich text fields, because Salesforce's SOQL API does not offer the ability to search on such fields. Therefore, long text and rich text fields will not appear among the filterable fields in the filter builder.