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

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

Links

It is possible to filter clients having a common link.

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

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