Common functions:Filter Data (Queries)

Filter Data (Queries)

Introduction

The filter tool allows you to display onscreen elements based on specific criteria. For instance, it allows you to filter all the clients being part of a precise category, living in the same city and who do not own companies. This tool is available in all the software managements and is represented by this icon: .

By clicking on the button, the following menu appears.

Filter Options

Temporary Filter

A "Temporary filter" can not be saved. To see the result later on, you will have to make the filter again.

By clicking on the "Temporary filter" option, this window opens. It allows you to create the wanted filter, based on precise criteria.These criteria are configured from the "Special", "Fields" and "Query" tabs.

Adding a Filter

This filter will be saved to enable you to reuse it without having to reconfigure it in the future. By clicking on the "Add filter" option, the "Add filter" window opens and you will have to name the filter. After naming it, you have to assign it the desired criteria. These criteria are configured from the "Special", "Fields" and "Query" tabs.

It is also possible for the user to configure the visibility of the filter as well as defining who will be able to modify it by clicking on the "Visibility" tab.

NOTE: If the "Network module" is not activated (module allowing more than one user in the software at the same time), this tab is visible, but there will only be one user in the drop-down list.

In this example, the filter is visible and usable by all the users, but only "John Smith" will be able to modify it.

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

Modification of a Saved Filter

If modifications are to be done to the filter, it will always be possible to select it, then to choose the "Modify filter" option from the button.

After, the "Query" tab will allow you to modify it.

Filter Configuration (explanation of the three tabs)

IMPORTANT: Prior to building a filter, it is necessary that you ask yourself several questions in order to know what information will be involved in the query. This help document contains just about all the possibilities accessible in the software.

A filter may contain criteria on:

Selections (categories, dynamic lists, static lists, etc.);
Characteristics (client or transaction);
Links;
The database fields

MOREOVER, ALL THESE ELEMENTS CAN BE COMBINED BY (AND)'S AS WELL AS BY (OR)'S.

BEFORE CLICKING ON "APPLY", MAKE SURE THAT ALL THE NECESSARY CRITERIA ARE INCLUDED IN YOUR CURRENT QUERY.

"Special" Tab

The section on the left contains three different filter options: "Selection", "Characteristics" and "Links". the section on the right contains the available choices related to the filter option chosen in the section on the left.

Selection

When you apply a filter on a "Selection", you can choose from many "Selection" types (e.g. "Categories", "Dynamic lists", etc.) Furthermore, according to the chosen "Selection" type, there could be more than one check box in the section on he right and it is important to remember that you can check more than one box to build your filter. If it is the case, there are two possible operations: Intersection (AND) as well as 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.

Example using the "Categories" of the filter option "Selection":

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

The "Reversed search" allows you to get the opposite result. 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.

Examples of reversed search:

In the selection of the filter, IF 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 MUST NOT have one AND the other boxes checked, the client must therefore have only one or none of the boxes checked.).

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 client MUST NOT have one OR the other boxes checked, the client must therefore have none of the boxes checked.).

The result will only include David.

When the wanted "Selection" and operation "AND/OR" are selected, click on " Add condition".

If other conditions are to be added, continue configuring the filter using the "Characteristics" or the "Links" of the "Special" tab, or from the "Fields" and "Query" tabs.

When all the conditions of the filter have been configured, click on " Apply" to view the result onscreen.

Characteristics

When applying a filter on "Characteristics", you must choose between two options: "Client" or "Transaction". Depending on selected option, the characteristics available in the drop-down list on the right will not be the same.


A: You must first select the type of characteristic (e.g. "Client).	B: You must then choose the characteristic for which you want to make a filter (e.g. "Board member").
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", "Start", "End" and "Note"). If you conduct a search in several sections, the result will include all the selected information.	D: By selecting the operation in C, the list of possible values will be available in the choices list. For instance, "Board member" and "is equal to...", bring the list of choices created for this characteristic (e.g. "President", "Treasurer", "Administrator", etc.). It is also possible that there be no available choice, for instance, in the "Note" section, you will then have to enter the value manually.
E: When all the desired operations are selected and the values are entered, click on " Add condition".	NOTE: If other conditions are to be added, continue configuring the filter using the options "Selection" or "Links" of the "Special" tab, or from the "Fields" and "Query" tabs. When all the conditions of the filter have been configured, click on " Apply" to view the result onscreen.

Links

When applying a filter on "Links", it is possible to filter from the information of links between client files.


A: Select the "Links" option.	B: Indicate the type of link from which the filter must be made.
C: Specify the direction of the link (first direction / second direction) that will be filtered. By selecting a link, the first direction of the link (e.g. "is an employee of") appears by default. The option "Use the link in both directions" allows you to search both directions of the link. For example, to find the links "is an employee of" AND "is the employer of". The option "Use the reversed search" is based on the first selection: By selecting the link, therefore the default first direction, the opposite would be to not contain the first direction of the link (e.g. If the first direction (is an employee of) is selected and that the option "Reversed search" is checked, the clients not having the "is an employee of" link in their files will be found.). By selecting "Use the link in both directions", the opposite would be to not contain neither the "is an employee of" nor "is the employer of" links will be found.). By checking the "Use the reverse link" box, the opposite would be not to contain the second direction (e.g. If the box "Use the reverse link" is checked and the "Reversed search" option is also checked, the clients who do not have the link "is the employer of" in their files will be found.).
D: Allows you to select the operation to apply (start date, end date, etc.). If information is specified in these sections, the result will correspond to all the criteria.	E: When the wanted operation is selected, click on " Add condition".
NOTE: If other conditions are to be added, continue configuring the filter using the options "Selection" or "Characteristics" of the "Special" tab, or from the "Fields" and "Query" tabs. When all the conditions of the filter have been configured, click on " Apply" to view the result onscreen.

"Fields" Tab

This tab allows you to make a filter on the information of a field in the database. For instance, the box in which you enter a client's city corresponds to the "City" field. 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 you to display the list in a specific order. The button places the list in the same order as the columns of the management grid displayed onscreen. The and buttons sort the list in ascending or descending alphabetical order.	B: List of fields from which it is possible to make a filter.
C: Indicates the type of field. For example, "Acronym" is of text type. There are six different types.	D: List of operations to use. The available operations vary depending on the type of field you are positioned on. For more information concerning the operations, click here.
NOTE: If other conditions are to be added, continue configuring the filter using the three tabs. When all the conditions of the filter have been configured, click on " Apply" to view the result onscreen.

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 fields, the characteristics or on the links 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 label 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 types of fields, "is empty" means that nothing is written in the corresponding field.

In the case of a "Logic or short integer" field type, "is empty" means that the given box is not checked.

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 specified values.

For a "Text" type of 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. Between A and D would be until Cz.

For a "Numeric" type of field with or without decimals, the information will be filtered in ascending numerical order. So if you enter: between 8 and 20, 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 specified values. To produce a list, you have to enter a value, press "Enter" and enter your second value.

is greater or equal to...

For a "Text" type of field, greater is determined according to the normal alphabetical order.

For a "Numeric" type of field, greater is determined according to the ascending numerical order.

For a "Date" and/or "Time" type of field, greater is determined according to the chronological order.

is smaller or equal to...

For a "Text" type of field, smaller is determined according to the normal alphabetical order.

For a "Numeric" type of field, smaller is determined according to the ascending numerical order.

For a "Date" and/or "Time" type of field, smaller is determined according to the chronological order.

is true

Only applies to the "Logic and short integer" type of field and means the given box is checked.

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" types of fields. 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 are already present in the database.

Example of Simple Filter (one condition)

In this example, the filter will find all the elements that contain the portion of word "Winn".


A: Choose the field to be filtered.	B: Select the operation to be performed.
C: Enter the desired value.	D: Click on " Add condition".
E: When the condition of the filter is configured, click on " Apply" to view the result onscreen.	NOTE: If the filter does not have only one condition, do not click on " Apply", continue configuring the filter using the three tabs.

Example of Filter with two or more Conditions

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 portion of word "Winn", we also want to find elements of "M" gender.


F: Choose the field of the second condition.	G: Select the operation to be performed.
H: State the requested value.	I: Choose the condition (AND) or (OR).
J: When all the conditions of the filter have been configured, click on" Apply" to view the result onscreen.	NOTE: If other conditions are to be added, continue configuring the filter using the three tabs.

Condition (AND) vs (OR)

Example using the following criteria: The "Gender" field must be "M" and the "City" must contain the portion of word "Winn":

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

Example using the criteria: "M" gender and "Winnipeg" city

By using the Reversed search on both conditions with the (AND) condition, the clients not corresponding to both criteria 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 criteria with the (OR) condition, the clients corresponding to at least 1 of the 2 criteria 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 criteria (e.g. Gender: "M") with the (AND) condition, the clients not meeting the "M" criterion AND meeting the "Winnipeg" criterion 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 criteria (e.g. Gender: "M") as well as the (OR) condition, the clients not meeting the "M" criterion OR meeting the "Winnipeg" criterion will be found (the client can have one of the two criteria, but not both). In the previous example, the result would include the clients John, Julie and Mary.

"Query" Tab

Prior to applying a filter, it is possible 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 entirely. For more information, click here.	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. For more information, click here.
E: When the query is ready, click on " Apply" to apply the filter.	NOTE: It is always possible, using the keyboard, to manually modify a query. Simply click where the modification is needed.

Process to Filter Several Conditions

To configure a filter that contains several conditions, 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:

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
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 city field must be equal to "Winnipeg".

Establish the interaction between 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 necessary to put in parentheses, using the button, 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.

Result of the interpretation.

A: Contains the list of the conditions of the filter.	B: Contains the whole query and presents the interaction between each condition.

Example of Modification of a Query

To modify a query, it is possible to use the buttons , , or to manually modify the query (section on the right) using the keyboard, by clicking where the modification is needed.


A: To edit a condition, select the condition to be modified, then click on the " Edit" button.	B: To delete a condition, select the condition to be removed, then click on the " Delete" button.
C: The button allows you to erase the query. After clicking on this button, the "Confirmation" window opens.	D: If certain conditions are right, answer " No" to the question, which will only erase the query on the right and the conditions will remain in the left part. If the conditions are all to be modified, answer " Yes" to the question, which will erase everything (the query on the right AND the conditions on the left).