Editing List Queries |
Top Previous Next |
The Edit List Query Definition dialog is shown when adding or editing list queries from Queries Setup. Other functions where Queries are referenced, such as the Queries Tab view, may also have a button to directly Edit the Query without leaving that function and going through Queries Setup.
Here you can edit all of the components of a List Query. There are a few fields you can edit directly, a couple of buttons for editing the list of Default Sorting and Filter Conditions for the Query, and the main portion of the dialog for editing the "meat" of the Query -- the Query Columns. The order of the columns is also defined here, by their order in the list, which can be adjusted easily with the Move Up and Move Down buttons.
Query Name
The name should be descriptive enough for selecting the Query out of a drop-down selection list. Queries will usually be shown in the order they appear in Queries Setup, not alphabetical, so the name doesn't affect the order. Each Query must have a unique name (which is not case-sensitive).
Base Table
The base table determines the primary data table of the Query -- that is, which records are potentially going to be included in the Query. For instance, if the base table is Reservations, then the Query will include all Reservation records by default, subject to the Filtering Conditions.
Selecting the base table is important, but it's not always an obvious choice. For instance, lets say you want a list of outstanding balances. If you choose "Customers" as the base table, then you could easily show customer balances but it wouldn't be as easy to show reservation information (site or in/out dates, for instance), and the balance might not represent a current reservation. If you choose "Reservations" as the base table then you can show each reservation with a balance, and any related information, and it would show multiple lines for the same customer if they have more than one site reserved (e.g. linked reservations).
Also keep in mind that the base table affects which context functions are available for Query expressions. If "Customers" is the base table, then only ThisCust is useful (because a Customer record doesn't necessarily have a unique reservation). If "Reservations" is the base table, then you can use ThisResv, ThisCust, ThisSite, and ThisPark, because all of that information is known for each reservation.
Another function of the base table selection is to determine where a Query is appropriate. For instance, Query must have a base table of Reservations to be added to tab views like Arrivals, Departures, etc., and a base table of Transactions to be added to the Transactions tab view. It must have a base table of Sites to be added to the Rack sites headings.
Any table in the database may be selected, but only a few are useful for most situations -- Reservations, Customers, Transactions, and Sites.
Note that if you're creating a Query to use with the date headers of the Rack, then the base table does not matter since no record information is used. However for testing reasons, we suggest using Parks just because the Save & Test function will try to show all records of the selected base table, and the Parks table usually only has a few records.
Access Level
The access level simply determines which operator access level is required to view the Query. If the current operator does not have the selected access level, then the Query will not be shown in the selection list on the Queries Tab view, for instance.
Exclude from Lists
When this is checked, the Query will not be shown in any selection lists such as in the Queries Tab view. Note that this does not actually disable the Query -- e.g. if it was already selected as an add-on for a tab view then it will still be used in that function. However it won't be available as a general selection, which can be handy for keeping special-purpose Queries out of view. Of course this also means that you would need to uncheck this again (temporarily at least) if you ever did need to select the Query somewhere. Another alternative would be to set the Access Level for such Queries to Administrator, assuming that most operators are non-administrator.
Note that one place that Exclude from Lists does not affect is the Tab Views Setup (since this is assumed to be an administrator-only function anyway).
Default Sorting Hierarchy
The default sorting hierarchy is used as the lowest-order sorting when any column is sorted in the query, if the specific column sorting results in equal values.
For instance, if a Transactions-based query is sorted by Site, you may have a lot of records with the same Site. The normal Site column sorting would consider these equal, so the order of those records of the same site could be random. However if you've specified a default sorting hierarchy, then it would be used to further sort those records of the same site -- in the case of Transactions, we suggest using a default hierarchy that includes the Date, Time, and Record ID fields of the transactions, in that order.
Filtering Conditions
The filtering conditions determine which records of the Base Table are included in the Query. Without any filtering conditions, ALL records are included (which in some cases is what you want, e.g. to include all Sites). Refer to the Filter Conditions section below for more details.
Important: If this query is selected as add-on for another Tab View (e.g. Arrivals), then any filtering conditions defined here are also used to determine which records are shown. Only records that pass the filtering here and the tab view's normal filtering will be shown.
Includes Text Filter
When this option is checked, the user will have the option to enter search text. You should include a filter condition that checks this text against the appropriate field, e.g. a customer name, site number, confirmation number, etc. This allows you to create any type of "Find" query you can think of.
Query Columns
This grid simply shows the "columns" of the Query (although the end use may not be columns, such as when used as a date header add-on Query for the Rack). The order of the entries here determines the order of the columns when the Query is used. While only the column heading name and expression is shown here, there is actually much more information in a column definition. Each column is actually a separate Query Column record, linked to the Query.
Typical functions are available for adding, inserting, copying, moving, and deleting columns. When adding or editing a column (double-click on a column also edits it), the Edit Query Column dialog is used to enter or edit the information for that column.
Quick-Add Fields
This is a special function for quickly adding simple data fields to a query. It displays a dialog where you can select one or more fields to be shown in the Query. You can choose fields from any table that will be appropriate, e.g. any table that will be available in the context of the query.
To add a field, just double-click the field name in the left column (or select the field and click the "-->" button). To remove a field, double-click it in the right-hand column, or select it and click the "<--" button. If you don't add them in the right order, don't worry about it here -- you can fix the order once you're done and get back to the Edit List Query dialog (using the Move functions).
For each field added, a Query Column record is created and added to the Query. The various details of the column, such as the heading, expression, justification, formatting, summing and sorting details are all set up with appropriate defaults. If these aren't quite what you want, you can edit the columns added, just like any other column.
Save & Test Query Results
This function invokes the Save & Test dialog, which shows the Query in a grid. This can be used to test the query, without completely exiting the Edit function. Be aware that it does completely save any changes you've made to the Query, so it negates any possibility of cancelling changes you've made.
Notes
You can enter any notes for yourself here, or use this as a description of the Query -- they're only seen here and in the list in Queries Setup.
Additional Topics:
Advanced Customizations Overview & other topics