Archive for July, 2013

Filters in Apsona – Part 1

Wednesday, July 31st, 2013

Searching or querying of data is a very common need for Salesforce users. The required data that drives the filtering frequently does not reside in just one object, but rather in several related objects. For example, you might want to find Contact records whose Accounts are in a particular city, so that even though the data sought is Contact data, the filtering condition is on the related Account. And once you have figured out all the filtering conditions you want to apply, and retrieved the data you want, you would want to save the search, so that you can reuse it later.  Apsona for Salesforce offers powerful search and filter capabilities for all Salesforce objects, both native and custom, out of the box. 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.

A query/filter can be built in two ways from the Apsona user interface.

The first one is by clicking Search and More options, and the second is by clicking the filter dropdown and selecting New filter as seen in the screen shot above.

The same filter editor opens up with both options, and here is where you will specify the search terms. When you click the drop-down to specify search terms, you will see a list of panels, one for each object. Each panel is labeled with the object name, and contains the fields for that object. The object at the top of the list is the one from which you are running the filter, with all its fields. Below that panel will appear all its related objects (and all their fields) which are one step away – basically the children and parents of that object. Note that one panel appears not just for each related object, but rather for each relationship. For instance, in the screen shot above, the Account object contains two lookup fields, Master Record and Parent Account, both referring to the Account object, so you see a panel corresponding to each.

If the current object is the parent in the relationship, you will see an asterisk next to the name. In the above screen shot, the Contact or Lead panels both have asterisks shown, since the Account object is a parent of each of those objects. But if the current object is a child in the relationship (as in the case of the Master Record and Parent  Account relationships), no asterisk is shown.

When running a filter in a one to many relationship, you can run a Quantified search. In this search, 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. Let’s take an example where we want to find Accounts whose contacts have the salutation of Dr. (Doctor). We run a search from the Accounts object. For the search terms, we select the field “Salutation” from the Contacts table. Since the field is a picklist we get the option of choosing is among as one of the field operators. For the quantifier, let us choose the option for all records. Click search. We now get all the Accounts whose contacts have the salutation Dr. Notice that the returned list might include Accounts which have NO contacts in them. This is because an Account with no contacts satisfies – albeit vacuously – the requirement that all its contacts have a salutation of Dr.  Since we selected the quantifier as “all records”, Accounts with no contacts are also entitled to qualify.

This is just one option of the 3 options available in the quantified search. The” no records” and the “atleast for one record” are equally powerful and will be touched upon in the coming blogs.

You can try out these searches and more by downloading Apsona for Salesforce from the AppExchange.