Contact us Blog

Searching and filtering

Overview

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. 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.

Quick search

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.

Filtering search

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 Account.Industry, the operator is equals and the value is Pharmacy. 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 documentation 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 Account (the built-in one in Salesforce), and the other two are called Church Attending and Referral Agency. So there are three corresponding options in the field chooser, labeled Contact via Account, Contact via Church Attending and 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 Contact, named _SYSTEM:One2OneContact and Head of Household. Therefore, two choices labeled Contact (_SYSTEM:One2OneContact and Contact (Head of Household) also appear, but without asterisks, because those are one-to-one relationships, since the lookups are from Account to Contact.

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 right).

Comparison operators

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.

Ordering-related operators

There are the usual six ordering-related operators: equals, not equal to, less than, at least, at most, and greater than. 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 shot below. 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.

Wild-card search

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 Bob Clements or Robert Clements. You could use a filter term Full Name starts with _ob%Clements, meaning that the matching name should have the letters ob as the second and third letters, followed by any characters and then the string Clements.

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 Account.Billing State fully matches either NV or CA or OR. So if a record contains a value NV,C in that field, it will not match this filter condition.

Similarly, you 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 Account.Name field contains any one of the three strings Plumber, Plumbing, or Water treatment, regardless of where they occur in the value. For example, field values Joe's Plumbing Hardware and Eagle water treatment chemicals will both match the filter condition. Notice how this partial matching contrasts with the way is among and is not among behave.

Multi-term searches

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. Relative dates are also referred to as floating or rolling 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 filter term.

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., 8:15 am or 13:25:15. 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 Opportunity fields.

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.

Exception search

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 assigned tasks. 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 associated cases.

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).

Quantified search

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 "Closed Won".

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.

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:
  1. Click the "Save as filter" link in the search panel.
  2. Type a name for the new filter, and click the "Save" button.
  3. 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:
  1. Click the "Filters" menu item under "Tools".
  2. 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.

Nested search

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 filter condition. 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.

Limitations