Common functions:Filter Data (Queries)

Introduction

The filter tool allows to display onscreen 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.

Temporary filter and current filter

Temporary filter

The option "Temporary filter" displays the filter screen directly and allows you to create a filter that will be used only once.

Current filter

The option "Add filter" requires you to name the new filter and to assign it the desired conditions.

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

"Special" tab

Selection

When you apply a filter on a selection, it is important to remember that you can check more than one box to build your filter. If more than one box are checked, there are two possible operations: Intersection (AND) or Union (OR). All the checked boxes will then be treated as a whole.

Intersection (AND) or Union (OR)

Intersection (AND) 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 (AND) boxes, or the elements that do not correspond to at least one of the checked (OR) boxes.

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 clients 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 include David.

To filter fields, see Condition (AND) vs (OR)

When the appropriate operation is selected, all there is 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 (e.g. Client).	B: You must then choose the characteristic for which you want to create a filter.
C: A characteristic contains a lot of sections. You must therefore decide on the information to be filtered in the different sections (e.g. Value and Note). 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, click on "Add condition".

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

File:Filter information 007.png
A: Specify, in the "Value" section, that the chosen characteristic must be empty.	B: Click on "Add condition".

Links

It is possible to filter clients having a common link.


A: You must select the "Links" option.	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 you 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 you to search the second side of the link. For example, "is the employer of". The option "Reversed search" allows you to search the clients who do not have the selected link.
D: If some information is specified in several sections, 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: These buttons display the list in a precise order. "The grid" places the list in the same order as the columns of the management list displayed onscreen. "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 field you are placed. For example, "Acronym" is of "Text" type. There are six different types.	D: List of operations to be used. The available operations vary depending on the type of field you are placed on. More details.

Types of fields

Type of fields	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 fields allow you to use 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 and 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: Enter the desired value.	D: Click on "Add condition".
E: Click on "Apply" to view the result.

Example of a two-condition filter

After step 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 "M" gender.


F: Choose the information of the second condition.	G: Select the operation to be performed.
H: State the requested value.	I: Choose the condition (AND) or (OR).
J: Click on"Apply" 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 both 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 invert the conditions. In the previous example, the conditions would now become: the gender must not be "M" and the city must not be "Winnipeg".

By using the Reversed search on both conditions with the (AND) condition, the clients corresponding to both conditions will be found. In the previous example, the result would only include Mary, because she is not of "M" gender and her city is not "Winnipeg".

By using the Reversed search on both conditions with the (OR) condition, the clients corresponding to at least 1 of the 2 conditions will be found. In the previous example, the result would include the clients Robert, Julie and Mary, because they are either not of "M" gender and/or because their city is not "Winnipeg".

By using the Reversed search on only 1 of the 2 conditions (e.g. Gender: M) with the (AND) condition, the clients not meeting the "M" condition and meeting the "Winnipeg" condition will be found. In the previous example, the result would only include the client Julie.

By using the Reversed search on only 1 of the 2 conditions (e.g. Gender: M) as well as the (OR) condition, the clients meeting the "M" condition or meeting the "Winnipeg" condition will be found (the client can have one of the two conditions, but not both). In the previous example, the result would include the clients John, Julie and Mary.

To filter selections, see Intersection (AND) vs Union (OR)

"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 between each condition.
C: "Edit" allows you to change the value of a condition. "Delete" allows you to remove a condition completely.	D: "Empty" allows you 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".

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 either be 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 the 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.