PFE 7.0 Queries
Note: The content of this topic is reprinted with permission from PVX Plus Technologies Ltd.
When a Query is invoked, a panel is displayed showing a list of records from which to choose. Besides the basic display, the 7.0 query also offers a number of features that are available to the end user at run time to further enhance the query experience. These include search, sort and filter capabilities, and more.
These features, described in detail below, are available either by selecting the toolbar button or by selecting from the right-click popup menu. This section also discusses how to suppress undesirable features.
- Sort By
The query is initially loaded using the key that is predefined in the package for the query. Sorting by columns in ascending/descending order is available by clicking on the column headings.
Note: Column sorting ignores case and accents.
- Match Column
Enter a match column value to position the query at the record whose currently selected column matches the value. The user has to enter formatting characters in string input values (e.g. dashes for phone numbers formatted as 000-000-0000). Columns containing images or string values that use an alternate sorting algorithm will have undefined results. Numeric column matches are not supported.
Note: To select a specific column click on the column heading.
- Find Text
The search feature allows the user to search the contents of the query for occurrences of a specific text value. To specify whether the search should be case-sensitive is also provided via the drop down button next to the Find text input field.
To find the next or previous text occurrence click on the arrow buttons next to the Find text input field.
Individual records in the query data may be designated as favorites, which may be displayed separately using Favorites display mode. Favorites mode replaces the normal display with just those records designated as favorites, allowing the user to have a personalized filter for those records required most often.
|Show Favorites/Show All
||Toggle between the normal query display and the Favorites display.
|Add to Favorites
||While in normal query display mode, add a favorite by highlighting the record and clicking the Add to Favorites button or the right-click popup menu selection.
|Remove from Favorites
||While in Favorites display mode, remove a favorite by highlighting the record and clicking the Remove from Favorites button or the right-click popup menu selection.
Technical Information: You can highlight favorite records in the normal display by setting the %nomads'Query_Fave_Color$ property to the color to be used; e.g.
%nomads'Query_Fave_Color$= "Dark Green"
%nomads'Query_Fave_Color$= "RGB:192 64 0"
The Favorites feature is enabled by default. Individual queries have a Suppress Favorites option in the Query Header Definition, Options panel with selections to Always or Never suppress favorites. A Default setting is also available that uses the
%nomads'Query_Suppress_Favorites setting (0=Off, 1=On).
Show, hide or change the order of the columns.
To use this feature, click on the Columns button or right click in the query results list and select Show/Hide/Reorder Columns….
|Hide Column Name
||Hides the column that was clicked on with the mouse. For example, if you clicked on the Vendor Name column, this option becomes Hide Vendor Name, and when selected, the Vendor Name column is hidden.
||Invokes the Show/Hide/Reorder Columns interface for selecting the columns to be shown. You can also change their order by dragging them to their new locations in the list or by moving them with the Move Up/Move Down buttons.
|Reset Default Columns
||Resets the column selection and order to that of the original query definition.
|Save Column Settings
||Select this option to save the selection of shown/hidden columns, as well as their order and width when the query is exited. The columns displayed, their order and width will be restored when the query is next invoked. If not selected, the default Show/Hide settings, as well as the original column order and sizes will be restored the next time the query is invoked.
Dynamic filters can be added to individual columns. Filters include tests for equality, inequality and range. A description of the filter can be viewed in the tip that appears over the column heading. When filters are applied to more than one column, the conditions are applied using the AND operator (e.g. CustClass$ is equal to "xyz" AND Balance is less than 0). If selection criteria is specified when a query definition is created, then the user filters are applied in addition to the selection criteria using the AND operator.
|Add filter to Column Name
||Select this to invoke the Filter interface to add/modify/remove a column filter:
Select a Column to filter from the drop-down list. If just a part of the column value is to be evaluated, then you can specify an offset and length, e.g. to evaluate only the first three characters of the column value, set the offset to 1 and length to 3.
Select a Condition to apply. Conditions include evaluations for equality, inequality, ranges, etc.
Enter Value(s) to compare. For example, if the condition is a range Between and inclusive, then two values, such as 0 and 100, may be entered. Use double quotes ("") to indicate a null text value. A PxPlus expression can be entered if prefixed by an equal sign; e.g. =%Class$
Select the Match case check box for a case-sensitive filter.
Existing column filters are displayed in the Column drop-down list.
|Remove filter from Column Name
||Remove any filter that may be associated with the current column.
|Disable User filters
||Toggle to disable/enable the current user filters.
||Select to save the current user filters when the query is exited. The filters will be restored when the query is next invoked. If not selected, the filters will not be saved or restored.
Select Formulas to invoke the Formula Wizard for defining a new formula, along with editing or deleting an existing one. The Wizard walks you through naming and defining the formula, as well as formatting the results.
Numeric formulas can be built from column values. Addition, subtraction, multiplication, division, exponentiation and modulus operations are supported.
Text formulas can be built, which concatenate text components. These components can be comprised of literal values and column values. Partial items can be defined, and segments can be padded, have characters stripped, or be converted to upper/lowercase.
Formula-defined columns, like other columns, can also be shown/hidden, resized and re-ordered.
- Edit File
Edit the data file. The current record highlighted is loaded into the specified File Maintenance interface, although any record(s) may ultimately be edited. When returning to the query, the data is updated and the last record manipulated is highlighted.
Note: The Edit file button will not be visible if the Query does not contain a file maintenance assignment.
- Print Report
Click the Print button to produce a simple report listing the title, file name and column headings, followed by a list of all the records that pass the selection criteria. Report columns are formatted in the same way as the screen display.
Note: This is not a report generator, as there are no options for control breaks, totals or staggered lines. The Query report is sent to the report viewer.
The font size is adjusted to fit all columns on the page. That is, if your query has a large number of fields or very wide columns, then the report is printed using a small font to fit the information into the space allotted. If the font is too small, users can select landscape orientation to increase the report width and thus the font size.
Technical Information: The Print feature is available by default. The No Print Button option in the Query Header Definition, Options panel allows you to suppress this feature.
Refreshes the contents of the query.