Where available, a select-click onwill open a Query and add a Filter Bar to the current view similar to that shown below. The actual fields will depend on the view in question and any adjustments such as hidden columns.
The Query Module allows you to produce a filter that restricts the data in the current view. When initially displayed the filter bar will consist of a single row, although more can be added if required. Each row is made up of Criteria Cells. A Criteria Cell is where you specify the selection criteria, if any, that is to be applied to the data field for the column.
To be included in a filtered view, a data record must match all selection criteria specified for a given row. Use separate rows to include records based on one column or another.
There are two ways to enter selection criteria into your query. Once you are familiar with the required Expression Syntax you will find the quickest method is to select the required cell and enter the expression directly. An alternative is to use the Expression Builder from the Query Menu. If you choose this method, be sure to press menu while over the criteria cell you wish to use.
Note: It is not possible to move to another Criteria Cell, or run the query, if the current cell contains an invalid expression.
An expression consists of an operator, a constant and optionally wildcard characters. The constant part of the expression will be a string, number or date depending on the field type.
Strings should be enclosed in quotes, however, in most cases the Query Module should be able to add them for you. String values that contain characters that are also operators should be quoted manually to avoid confusion.
Unless specified, the operator is assumed to be equal.
Example Expressions | |||
Type | Entry | Expression | Description |
String | gas bill | = "gas bill" | Select records were the field is equal to the string 'gas bill'. Unless specified otherwise, comparisons are not case sensitive |
<>b* | <> "b*" | Select all entries not beginning with 'b' | |
= *bill* | = "*bill*" | Select entries containing the word 'bill' | |
Date | >=1/1/98 | >= 1 Jan 1998 | All entries with a date on or after Jan 1st, 1998 |
Integer | <100 | <100 | Entries less than 100. |
Currency | >.5 | >0.50 | All entries that exceed 50 pence in value (Assuming that the selected currency is GBP) |
There are three operators to choose from which can be combined to give the following selections:
= | Equal To, include record if contents of field equal to specified value. This is the default if no operator is specified. |
< | Less Than, include record only if the contents of field are less than specified value. For string fields comparison is alphabetical. |
> | Greater Than, include record only if the contents of field are greater than specified value. For string fields comparison is alphabetical. |
<= | Less Than or Equal To. |
>= | Greater Than or Equal To. |
<> | Less Than or Greater Than, in other words not equal to specified value. |
String constants may optionally contain one or more of the following wildcard characters:
% | Any single character; 'late' and 'date' both match the constant '%ate' |
* | 0 or more characters; 'do' and 'don't' both match the constant 'do*' |
Pressing the mouse Menu button while over a Criteria Cell will invoke the Query Menu. Some of the options on the menu are criteria specific and relate to the 'parent' cell.
Note: menu-clicking over a cell places the text caret into that cell.
Opens the Expression Builder Dialogue for the parent cell.
Selecting Clear Criteria from the Query Menu removes the criteria expression from the parent cell. The menu entry will be 'greyed out' unless the relevant cell contains an expression.
By default, case is ignored when comparing text fields, i.e. 'Bill' is equal to 'bill'.
Select this option to match the case of the text as well as the content.
Adds another row to the current query.
By default, a record only needs to match one of the specified rows to be included in the view. This can be changed by the option Match All Rows.
Removes the row, over which the menu was invoked, from the query. If there is only one row then this function has the same effect as removing the query.
The default action of query is to match records against any of the available rows, i.e.. a record must match the first row or the second row. Selecting Match All Rows requires that the record matches all rows to be included, i.e.. the record must match the first and second rows (and all other rows in the query).
Load a previously saved query. Option leads to a sub menu that lists all previously saved queries. Selecting a query will replace the current selection criteria, if any. Select execute query to apply the filter.
Save the current query so that it can be used again at a later date. The option leads to a sub-menu dialogue where the query can be given a name and saved. The query name must be a valid filename.
Note: Most views that support queries will also save the current query name when the settings for a view are saved. To have a particular query automatically applied when a view is opened you must first save the query. Hide the query, if required. Then select 'save settings' from the parent views own menu.
To remove a default query from a view, select Remove Query from the Query Menu and re-save the parent views settings.
Hides the current query from view. The query remains active which is identified by the fact that the Run Query button remains enabled, that is, it is not greyed out. Select-click on the 'Show Query' button to redisplay the query.
Removes the query and all its rows from the current window and restores the unfiltered contents of the view.
Executes the current query.
Selecting the Build Criteria... option from the Query Menu will display the Expression Builder Dialogue. Its exact appearance will depend on the field type for which the expression is being created.
Display field detailing the name of the field (column) against which the expression will be created.
Pop-up menu offering a choice of actions that can be applied to the field. The actions available will depend upon the field type.
For string fields:
Begins with | Include records where the text field starts with the characters specified by the literal. |
Contains | Strings containing the characters specified in the literal field. |
Ends with | Strings that end with the characters specified in the literal. |
Equals | Strings that match the specified literal. |
Greater than | Strings that are alphabetically after the specified literal. |
Less than | Strings that are alphabetically before the specified literal. |
Not equal to | Strings that do not match the literal. |
Not greater than | Strings that are not alphabetically greater than, i.e.. less than or equal, the literal. |
Not less than | Strings that are not alphabetically less than, i.e.. greater than or equal, the literal. |
For numeric fields:
Equals | Numbers that match the specified literal. |
Greater than | Records where the field is greater than (>) the literal. |
Less than | Records where the field is less than (<) the literal. |
Not equal to | The field is not equal (<>) to the literal. |
Not greater than | Not greater than, i.e.. less than or equal, the literal. |
Not less than | Not less than, i.e.. greater than or equal, the literal. |
For date fields:
After | Dates after the specified literal. |
Before | Dates before the literal. |
Not on | Dates other than the date specified in the literal. |
On | Dates that match the literal date. |
On or after | Dates on or after the date specified. |
On or before | Dates on or before the date specified. |
Writable icon for the entry of the data against which the field will be matched. The literal entered must be valid for the field type, i.e.. a date field will require the entry of a valid date. If the parent field has a menu associated with it, such as currency code, then the pop-up menu will be available to allow selection from the menu if desired.
For date fields that support the time component then omission of the time separator is significant. If only the date component is entered then time is ignored and all times for the specified date will match. If the time separator is included then missing time components are defaulted as normal and time is included in the comparison.
Available only for text fields, you should select this option if you wish query to match the case of a text field as well as its content.
Cancels the expression and leaves the criteria cell unmodified.
Validates the literal and builds the criteria expression. The resulting expression will be displayed in the appropriate cell on the query.