Advanced users and administrators can create views that combine two or more filter statements to create refined views that display a specific set of records that meet multiple criteria. You use Boolean logic to connect multiple filter statements. This includes the following common connectors:

• AND: Restrictive; displays records that satisfy both filter statements.
• OR: Inclusive; displays records that satisfy either filter statement.
• NOT: Corresponds to records that do not match a filter statement.
• Parentheses: Enclose items in parentheses to dictate the order of operation (items within parentheses are compared first).

See the following sub-topics for advance Filtering: 

• Advanced Filter Logic
• Filtering for a Range
• Filtering Based on Multiple Attributes of the Same Related Service
• Note Concerning Modifying Filter Logic for Filters Based on Same Related Service

Advanced Filter Logic

By default, the system connects multiple filter statements together with an AND operator. However, you can manually override this setting within the Filter dialog by clicking the Show Advanced button, which is located at the bottom of the dialog.

You can use any combination of AND, OR, and NOT operators to combine filter statements.

For example, if you have two filters, Filter 1 and Filter 2, you can link them in one of two ways:

• 1 AND 2 (Example: 1 AND 2)
• 1 OR 2 (Example: 1 OR 2)

You can also use a NOT operator in conjunction with the AND/OR operators; for example:

• 1 AND NOT 2
• 1 OR NOT 2 (note: this is called an exclusive nor, and is rarely used)

To group more than 3 filters together, use the open and close parentheses (); for example:

• (1 AND 2) OR 3
• (1 OR 2) AND 3 (Example: (1 OR 2) AND 3)
• (1 OR 2) OR 3 (this is the same as 1 OR 2 OR 3)
• (1 AND 2) AND 3 (this is the same as 1 AND 2 AND 3)

Consider the following examples:

1 AND 2

The example below displays the Persons records whose last name matches Aaronson AND whose Company's Billing City is San Antonio.

1 OR 2

The example below displays Persons records whose last name matches Aaronson OR whose Company's Billing City is San Antonio. More records appears in this view than in the view that uses the 1 AND 2 filter.

(1 OR 2) AND 3

The example below displays the Persons records whose last name matches Aaronson AND whose Company's Billing City is San Antonio, OR whose State matches DC.

Filtering for a Range

To filter on a range of numeric values, you can create a combination of filters that use greater than and less than operators and then connect them with the appropriate filter logic.

Filtering on a range for a character-based field can be more complex. For example, suppose that a user wants to create a view of Persons whose Last Name begins with A, B, C, or D.

In this case, you can create the following filters for a Persons view:

Then, you can connect these four filter statements with the following logic to display the appropriate records:

1 OR 2 OR 3 OR 4

Filtering Based on Multiple Attributes of the Same Related Service

When you create a view that references another service, Aptify generates the necessary SQL statement automatically to retrieve the desired information. For the sake of efficiency, Aptify generates a single sub-query for all filter statements that apply to the related service.

This approach is suitable in most cases. Although, under some circumstances, your view may return no records if the filters you specified are mutually exclusive.

Consider the following example:

A user wants to create a view that returns the Persons who attended both the 2006 and 2007 organizational conferences. To that end, the user creates the following filter statements for a view in the Persons service and uses the default AND operator to connect them:

• Filter Statement 1:
  • Service: Meetings – Attendees
  • Field: Meeting Title
  • Operator: Exactly Matches
  • Value: Sampco 2006 Annual Conference
     
• Filter Statement 2:
  • Service: Meetings – Attendees
  • Field: Meeting Title
  • Operator: Exactly Matches
  • Value: Sampco 2007 Annual Conference

However, this view will return no results because the viewing system generates a single sub-query for both statements: there is no meeting that has a title of both Sampco 2006 Annual Conference and Sampco 2007 Annual Conference.

To specify that you want the system to generate a separate sub-query for each filter statement, enclose each filter statement in parentheses within the Filter Logic area, as shown in the following figure.

This automatically generates a new SQL statement that uses two sub-queries to identify meeting attendees. When loaded, this view returns the desired results: the list of Persons who have attended both the Sampco 2006 Annual Conference and the Sampco 2007 Annual Conference. 

Note Concerning Modifying Filter Logic for Filters Based on Same Related Service

If a view has multiple filters based on the same related service, and a user changes the order of the filter statements in the Filter Logic area (for example, from 1 AND 2 to 2 AND 1), the default filter logic is no longer used. In earlier versions, Aptify always generates a separate sub-query for each filter statement. Due to the multiple sub-queries, when the view is run, the records that are returned may be different than the single sub-query (as generated with the default filter logic), which may not be the desired results. Starting with Aptify 5.5, depending on the desired results, when the filter logic is changed for filters based on the same related service, the logic can generate a single sub-query or a separate sub-query for each filter statement (as in previous versions of Aptify).

This behavior is controlled by the Optimize Sub-Queries option found in the Filter Logic area of the Views Properties dialog. When selected, filters based on the same related service will be generated as a single sub-query. When cleared, the same related service filters will be generated as multiple sub-queries. The Optimize Sub-Queries options is selected for new views by default.