Common functions:Filter Data (Queries)

Introduction

The filter tool allows to display on the screen elements that have something in common.Whether it is all the clients living in the same city, all the transactions of a golf tournament or all the GL accounts starting with 40. This tool is available in all the software managements and is represented by this icon: .

By clicking on the button, the following menu will appear.

If you choose the option "Add filter", you will have to name it and it will then be found in the "Current filter" drop-down menu.

Temporary filter and current filter

Temporary filter

The option Temporary filter" displays the filter screen directly.

Current filter

The option "Add filter" allows to name a filter that is about to be created.

When the filter is created, it is then available in the "Current filter" drop-down menu for further use.

"Special" tab

Selection

When you filter a selection, it is important to remember that you can check more than one box to make your filter. There are two possible operations. Intersection (ET) or Union (OR). These operations apply only when you check more than one box.

Intersection (ET) allows you to filter all the clients corresponding to all the boxes you have checked.

Union (OR) allows you to filter the clients corresponding to at least one of the boxes you have checked.

Reversed search allows you to get the opposite results. You can therefore filter the elements that do not correspond to all the boxes (AND), or the elements that do not correspond to at least one of the checked boxes (OR).

• Example using the notion of client and of categories:
• The client Robert has the categories "Volunteer (cocktail)" and "Volunteer (golf)".
• The client John has the category "Volunteer (cocktail)", but he does not have the category "Volunteer (golf)".
• The client David does not have the category "Volunteer (cocktail)", nor the category "Volunteer (golf)".
• In the selection of the filter, the boxes corresponding to the categories "Volunteer (cocktail)" and "Volunteer (golf)" are both checked, as well as the option "Reversed search".
• By using the Intersection (AND), the client who do not have all the boxes checked will be found (the client may have one of the boxes checked, but not both). The result will include the clients David and John.
• By using the Union (OR), the clients having none of the boxes checked will be found. The result will only comprise David.

When the appropriate operation is selected, all there is left to do is to click on "Add condition".

Characteristics

It is possible to filter all the information found in one characteristic.

A: You must first select the type of characteristic (eg: Client).	B: You must then choose the characteristic for which you want to create a filter.
C: A characteristic contains a lot of information, if you conduct a search in several sections, the found characteristic will have to include all the selected information.	D: When step C is completed, all there is to do is to click on "Add condition".

It is possible to filter elements that do not have any precise characteristic.

Links

It is possible to filter clients having a common link.

A: You must select the "Links" section.	B: You must specify the type of link on which the search will be made.
C: The side of the link to be filtered is shown in blue. The option "Use the link in both directions" allows to search both sides of the link. For instance, to search the link employee AND the link employer. The option "Use the reverse link" allows to search the 2nd side of the link. For example, the "is the employer of". The option "Reversed search" allows to search the clients who do not have the selected link.
D: If some information are specified in several zones, the result will then correspond to all the information.	E: Finally, click on "Add condition".

"Fields" tab

A field generally corresponds to the place where the information is entered in the software. For example, the box in which you enter the client's city corresponds to the field "City". Another way to approach this notion is to consider that if the requested information can not be found in the "Special" tab, it is probably a field.

A: Allows to display the list in a precise order. "The grid" places the list in the same order as the columns of the management list displayed on the screen. "A-Z" and "Z-A" sort the list in alphabetical order.	B: List of fields from which you can create a filter.
C: Indicates on which type of information you are placed. There are six different types.	D: List of operations to be used. The available operations vary depending on the type of information you are placed on. More details.

Types of fields

Type of field	Description	Example
Numeric without decimals	Corresponds to a number/digit without decimals	Age
Numeric with decimals	Corresponds to a number with decimals	Lifetime cumulative
Date and/or time	Corresponds to a date and/or a precise time	Created on
Text	Corresponds to data produced with text characters	City
Memo	Corresponds to a long text field	Comments
Logic or short integer	Generally corresponds to a box to be checked	Annual receipt

Use of operations

The filters on the characteristics or on the fileds allow the use of operations; here are their special features.

You can check the option "Reversed search" for all possible operations. This option allows you to invert the possible operations. For instance, "is empty" becomes "is not empty". The wording of the operations changes to indicate the exact operation that is going to be performed.

Regular search	Reversed search

is empty

Means that there is no information in the corresponding field.

For most of the "is empty" types of fields, it means that nothing is written in the corresponding field.

In the case of a "Logic or short integer" type of field, "is empty" means there is no check mark in the given box.

is equal to...

Means that the information is identical to the value you have entered.

is between ? and ?

Means that the information is comprised between the values you have specified.

For a text type field, the information will be filtered in alphabetical order. So if you enter: between A and Dz, anything that begins with A,B, C or D will be filtered. The z is used to filter the information beginning with D that contains other characters. It has to be done this way in order to respect the alphabetical order since D comes before Da whereas Dz comes last.

For a numeric with or without decimals type of field, the information will be filtered in increasing numerical order. So if you enter: between 8 and20, anything that is equal to 8, 9, 10 [...], 19 and 20 will be filtered.

For a date and/or time type of field, the information will be filtered in inclusive chronological order. So if you enter: between 2010/01/01 and 2010/12/31, anything that contains a valid date in 2010 will be filtered.

is in the list...

Means that the information to be filtered corresponds to one of the values you have specified. To produce your list, you have to enter a value, press "Enter" and enter your second value.

is greater or equal to...

For a text field, greater is determined according to the normal alphabetical order.

For a numeric field, greater is determined according to the increasing numerical order.

For a date and/or time field, greater is determined according to the chronological order.

is smaller or equal to...

For a text field, smaller is determined according to the normal alphabetical order.

For a numeric field, smaller is determined according to the increasing numerical order.

For a date and/or time field, smaller is determined according to the chronological order.

is true

Only applies to the "Logic and short integer" type fields and means that there is a check mark in the given box.

contains...

Means that the requested information exists in the corresponding field. You can search a letter, a word, a sentence...

begins with...

Means that the requested information is available at the very beginning of the corresponding field. You can search a letter, a word, a sentence...

ends with...

Means that the requested information is available at the very end of the corresponding field. You can search a letter, a word, a sentence...

Others

Only applies to the "date and/or time" type fields and allows you to use the same date search codes as in the standard search bar. The different codes are displayed in the drop-down list.

List of choices

Allows you to choose among all the existing values in the corresponding field. For example, for the text type field "City", you will access the list of all the cities that already exist in your database.

Example of simple filter

In this example, the filter will find all the elements that contain the letters "Winn".

A: Choose the information to be filtered.	B: Select the operation to be performed.
C: Capture the desired value.	D: "Add condition" to the filter.
E: "Apply" the filter to view the result.

Example of a two-condition filter

After the D in the previous example, it is possible to add other conditions to the filter. In this very example, in addition to searching the cities containing the letters "Winn", we also want to find elements of gender "M".

E: Choose the information of the second condition.	F: Select the operation to be performed.
G: State the requested value.	H: Choose the condition (AND) or (OR).
I: "Apply" the filter to get the result.

Condition (AND) vs (OR)

The client John is of "M" gender and his city is "Winnipeg".

The client Robert is of "M" gender and his city is "Ottawa".

The client Julie is of "F" gender and her city is "Winnipeg".

The client Mary is of "F" gender and her city is "Toronto".

Condition (AND) implies that the element to be filtered corresponds to the 2 conditions. In the previous example, the search would only produce the client John as a result.

Condition (OR) implies that the element to be filtered corresponds to at least 1 of the 2 conditions. In the previous example, the search would produce the clients John, Robert and Julie as a result.

In reversed search, we have to reverse this reasoning.

By using the condition (OR), the clients having none of the information will be found. In the previous example, the search would only produce the client Mary as a result.

"Query" tab

Prior to applying a filter, it is possibe to use the "Query" tab to revise the whole filter.

A: Contains the list of all the conditions of the filter.	B: Contains the entire query and presents the interaction with each condition.
C: "Edit" allows to change the value of a condition. "Delete" allows to remove a condition completely.	D: "Empty" allows to correct the query and to delete all the conditions if needed. After emptying, it is possible to double-click on the conditions in A and to use the buttons "AND/OR" between each condition.
E: When the query is ready, click on "Apply" the filter.

Process to filter several conditions

With a complex filter, it is often necessary to dissect the different elements of a statement. These elements will then become the conditions used to build the filter. This process can be done on paper or mentally.

Here is an example of a typical process:

Verbal statement I want to have a list of the board members, as well as the volunteers (golf) living in Winnipeg.

Interpret the statement The first step will be to interpret the statement to make sure that the outcome corresponds to the desired result.

My statement could be interpreted in two ways concerning the city:

• I want the board members living anywhere, as well as the volunteers (golf) living specifically in Winnipeg.
• I want the board members living in Winnipeg, as well as the volunteers (golf) living in Winnipeg.

In my example, I chose the second interpretation.

My statement could be interpreted in two ways concerning the phrase "as well as":

• I want the client to be a board member AND a volunteer (golf) (both at the same time).
• I want the client to be either a board member OR a volunteer (golf) (one of the two).

In my example, I chose the second interpretation.

It could have been necessary to push the interpretation further: does the board member have to be active or not, does this person have to hold a particular position, etc.?

Establish the conditions My statement could be broken down in three distinct conditions:

• The client must be on the board of directors.
• The client must be a volunteer (golf).
• The client must live in Winnipeg.

Transform verbal conditions into sofware conditions If need be, you have to define what each information corresponds to in your software.

• Being a board member: The characteristic "Board member" must be present in the client file.
• Being a volunteer: The category "Volunteer (golf)" must be checked in the client file.
• Living in Winnipeg: The field "City" must be equal to "Winnipeg".

Add the necessary conditions to the filter This is the first step that will be done in the software. For more details on the addition of conditions, see the relevant sections.

List of conditions.	B: Interaction between the different conditions.

Establish the interaction of the conditions Depending on my interpretation, the conditions will interact in the following way:

• The client must be a board member AND live in Winnipeg.
  
• The client must be a volunteer (golf)AND live in Winnipeg.
  
• The client must either be a board member OR a volunteer (golf).
  

In one line: The client must either be a board member OR a volunteer (golf) AND must live in Winnipeg.

To make sure that AND is processed after OR, it is preferable to put in brackets the conditions that must be processed together.

Which gives: (The client must be a board member OR a volunteer (golf)) AND must live in Winnipeg.

Since the current query does not correspond to the interpretation, it will be necessary to modify the query.

C: Click on "Clear" in order to empty the query.	D: Since the conditions are right, answer "No" to the question.

E: Click on the "Brackets" button in order to add brackets in the "Query" section. The cursor will blink between the brackets.

F: Double-click on the first condition to be used. The condition willl appear in the brackets.

G: Click on the "OR" button in order to add it after the first condition.

H: Double-click on the second condition to be used.

J: Click on the "AND" button in order to add it after the bracket.

I: Click next to the bracket in order to get the cursor out of the brackets.

K: Double-click on the last condition in order to add it to the query.

L: Finally, click on "Apply" in order to see the result on screen.

---

Document name: Common functions:Filter Data (Queries)

Printable version