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.

• 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 tasks.
• 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 related records.
• 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.

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 left) contains a top panel with the fields for the current object, and below it, one panel for each related object. 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 example at left, the the Contact object refers to the Account object, and is referred to by several other objects. So we see a panel for each of these objects in the field chooser.

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.

Field indicators

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

The filter terms panel understands the data types of the fields for matching. So 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. But if you choose a picklist field, as shown in the screen shot at left, 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. 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.

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.

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 "Prospecting". Normally, this would mean that we want all account records that have at least one corresponding opportunity whose stage is "Prospecting". But Apsona lets us go further, and use a quantifier on the secondary (Opportunity) field. So we can assert that we want account records all of whose opportunity records have stage "Prospecting." A similar quantifier search is shown in the screen shot below, where the one-to-many relationship is from Contact to Case, and we want the Contact records for which all related cases have type "Mechanical".

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.