Recent News
|
InnPlanner 2010 Released
New faster database engine, more customization features, weekly tariffs, full multi-user work support and much more... | |
InnPlanner 2010 RC1
InnPlanner 2010 Release Candidate 1 is ready for downloading and testing. You can download it from the "Downloads" section. |
|
|
Hotel Software Documentation
Queries
Introduction
Query - a user's request for information from a database. A query consists of the following parts:
- Specification of records source for analyzing rules
conformity;
- Set of rules, according to which the records/queries will be
checked for consistency;
- Specification of how the request results should be displayed.
Let us examine what queries are, using databases, which contain information about books, as an example. Assume
that we have the following information about each book: Title, Author, Number of pages, Publication date, and year of purchase. In this case, a query would be a formal description of such expressions as follows:
Example 1. I want to get from the database a list of books written in 1995, sorted by
name. Where:
- "from the database" is a source;
- "written in 1995" is a rule;
- "a list of books, sorted by name" is a specification of how the results should be displayed.
Example 2. I want to get from the "My Favorite Books" folder a list of the books published in the year of creation, number of pages of which is more than 200, sorted by year of
publication, where:
- "from the "My Favorite Books" folder" is a source;
- "published in the year of creation, number of which is more than 200"
is a ruleset;
- "a list of the books sorted by year of publication" is a specification of how the results should be displayed.
Further we will use these examples in the queries editor description.
Query Editor
General
To open the Query Editor window, select the "Queries->Add/Modify/Delete
Query" item in the main menu:
The Query Editor window consists of two panes:
- The left pane (1) provides the list of queries available in
the database and the buttons to add and delete a query.
The queries are grouped by types of records,
the current record is marked with the blue color;
- In the right pane (2) you can edit the current query.
Creating and Deleting Queries
- To create a new query click the New Query button in the left upper
part of the window (1) and select the record type, with which the new
query will work. By default, a query returning all records of the selected
type is created. To modify a query use the right pane of
the window (2);
- To delete the current query click the Delete Query button in the
left upper part of the window
(1);
Modifying the Current Query
The right part of the window (2) used to edit the selected query. It contains
3 tabs: Query, Appearance and Post-Query Actions.
Query Tab
The contents of this tab can be divided into three parts (see the "Introduction" section):
- Part 1 - The records source (i.e. the folder, from which the
records should be taken);
- Part 2 - Set of rules, according to which the records and the queries will be checked
for consistency;
- Part 3 - In this part you can specify fields the records
should be sorted by.
Using unique field you can specify the field that has to be unique in the query
results. For example (for a database of books), to get a list of all genres,
indicate "genre" as distinct. Now the query will return only one book for each
genre, and we will get a list of all genres. Otherwise the resulting list will
contain all books and, consequently, duplicating genres.
When creating a new query, the set of rules is empty. This means that all records from the specified folder (and its subfolders, if the corresponding
checkbox is enabled) will be returned.
At the left of the window there are 3 buttons, which can be used to create,
delete or edit rules.
If a ruleset contains more than one rule, you should use
the
"And/Or" button to specify, whether a record to be returned should satisfy all the rules (and), or
one of the rules (or). For example:
- Get a list of the books published in 2000, and those page number of which is more than
100;
- Get a list of the books, which were either published in 2000, or
page number of which is more than 100;
If you want to create complex rules, like (Rule1 AND Rule2) OR (Rule3 AND Rule4)
click the dropdown menu of the button New Rules... and select the New RuleSet menu item:
Clicking either "Edit Rule" or "New Rule"
opens the window for rule editing,
where you can set up the rule.
Appearance Tab
- Configure Table Style - click on this button to define how query
results are to be output (fields, colors, grouping,
etc.) Know more...
- Show query results in -
this option defines, where query results are to be displayed:
- Special Window on the left - query results are to be displayed in a
special window to the left, under the folder list (by default).
- Replace Record list window - query results will be displayed where
the record list normally appears; at the same time, the record list in the
current folder will be hidden. Selecting this option allows creating the
Toolbar that will appear above the results. For
example, there you can place buttons for printing results or for mass editing
of selected records:
When the flag is cleared, the window with query results will not be displayed.
- Don't show the query in the "Queries" menu - select this
checkbox to hide the option for execution of the query in the queries
menu. See Running Queries to learn more about other ways
to execute a query.
- Don't show the "no records were found" message - select this
checkbox if you do not want the program to show the message in case no
records have been found.
Post-Query Actions Tab
This tab contains several checkboxes, which define operations to be performed after the request is
accomplished.
- Restart query... If none of the records meets query parameters, a user will be offered to repeat the request with different parameters. This option is performed only if any parameters in the ruleset are specified as
"variable" (see above).
- Automatically select the first record from query results. After a request is accomplished, the first record in the query results will be selected.
- Print query results. After a request is accomplished, you will be offered to
print out its results;
- Export query results using the custom export. After a request is accomplished, you will be offered to export its results
by using Custom Export Templates (generate html
web-page, export to txt, insert data to MS Word document, send result by
e-mail, etc). Learn more about the custom export...;
- Execute script. After the query has been executed, the
script set in the Script Designer will be executed.
.Running Queries
The easiest way to execute a query is to select it from the upper part of the
Queries menu:
In case the required query is not displayed in the Queries menu (i.e. for
this query in the Additional tab the checkbox Don't
show the query in the "Queries" menu is ticked), it can still be
executed by creating a corresponding toolbar
button or a form button using
actions.
Records contained in the report will be moved into the special Query
Results Window (by default):
To view a record from the query, double-click it using the left mouse button. To print
the query, select Print Query Results from the reports menu.
To perform an operation with a query result you should copy
the query results to a variable using a script and perform the "Run script for recordset" action.
For details, see User Functions - Changing Several Records.
|
|
|
|
Today"s Poll
What software do use for your inn/b&b/hotel/etc?
|
About