Common functions:Filter Data (Queries)

From Logilys
Revision as of 15:34, 22 May 2019 by Mboissonneault (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
Filter Data (Queries)


CAUTION : This document is for VERSION 5 of Prodon. To go to the document for version 4, click here.


WARNING : This document is currently being edited/revised, it is possible that some parts are missing or obsolete.

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: Button Filter.png.


By clicking on the Button Filter.png button, the following menu appears.

Filter information 001.png

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.

Filter information 002.png


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.

Filter information 003.png

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.

Filter information 003A.png

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.

Filter information 004.png

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 Filter.png 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)

Filter information 005.png

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

Diagram AND OR.png

  • 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 "Button Check mark Confirm.png 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 "Button Check mark Confirm.png 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.

Filter information 006.png

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 "Button Check mark Confirm.png 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 "Button Check mark Confirm.png 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.

Filter information 008.png

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 "Button Check mark Confirm.png 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 "Button Check mark Confirm.png 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.

Filter information 009.png

A: Allows you to display the list in a specific order.

The Bouton filtre grille.png button places the list in the same order as the columns of the management grid displayed onscreen.

The Bouton filtre AZ.png and Bouton filtre ZA.png 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 "Button Check mark Confirm.png 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
Filter information 010.png Filter information 011.png


is empty

Means that there is no information in the corresponding field.

Filter information 012.png

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.

Filter information 013.png

is between ? and ?

Means that the information is comprised between the specified values.

Filter information 014.png

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.

Filter information 015.png

is greater or equal to...

Filter information 016.png

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

Filter information 017.png

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.

Filter information 012.png

contains...

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

Filter information 013.png

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

Filter information 018.png

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

Filter information 019.png

Others

Only applies to the "Date" and/or "Time" types of fields. The different codes are displayed in the drop-down list.

Filter information 020.png

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.

Filter information 021.png

Example of Simple Filter (one condition)

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

Filter information 022.png

A: Choose the field to be filtered.

B: Select the operation to be performed.

C: Enter the desired value.

D: Click on "Button Check mark Confirm.png Add condition".

E: When the condition of the filter is configured, click on "Button Check mark Confirm.png Apply" to view the result onscreen.

NOTE: If the filter does not have only one condition, do not click on "Button Check mark Confirm.png 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.

Filter information 023.png

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"Button Check mark Confirm.png 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.

Filter information 024.png

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 " Button Check mark Confirm.png 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 Bouton Parenthese.png 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.

Filter information 025.png

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 Bouton Parenthese.png, Button AND.png, Button OR.png or to manually modify the query (section on the right) using the keyboard, by clicking where the modification is needed.

Filter information 026.png

A: To edit a condition, select the condition to be modified, then click on the "Button Edit.png Edit" button.

B: To delete a condition, select the condition to be removed, then click on the "Button Minus remove delete.png Delete" button.

C: The Button Clear.png button allows you to erase the query. After clicking on this button, the "Confirmation" window opens.

D: If certain conditions are right, answer "Button cancel.png 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 "Button Check mark Confirm.png Yes" to the question, which will erase everything (the query on the right AND the conditions on the left).


Here are some examples of modification using the buttons of the query.

Filter information 027.png

E: Click on the Bouton Parenthese.png button in order to add parentheses in the "Query" section. The cursor will blink between the parentheses.


Filter information 028.png

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


Filter information 029.png

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


Filter information 030.png

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


Filter information 031.png

I: Click next to the parenthesis in order to get the cursor out of the parentheses.


Filter information 032.png

J: Click on the Button AND.png button in order to add it after the parenthesis.


Filter information 033.png

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


Filter information 034.png

L: Finally, click on "Button Check mark Confirm.png Apply" in order to view the result onscreen.



Document name: Common functions:Filter Data (Queries)