Editing Query Columns |
Top Previous Next |
The Edit Query Column Definition dialog is only shown when you Add, Insert or Edit a column from the Edit List Query dialog. Here you can change any of the details of the query column.
Field / Expression
This expression defines the content of this column for each record of the query. You can enter the expression directly in this editing window, or use one of the helper functions above it. Buttons are available to Insert Expression Element and Test/Edit Expression, which invoke the corresponding dialogs to help build the expression. There's also an Insert Field button, which invokes the Select Data Field dialog. Here you can simply select a table and data field, and the expression and other formatting information will be automatically filled in with some appropriate defaults.
Column Heading
This will be the name of the column and the text in the heading. This field can't be blank, but it's OK to have duplicate names if you really want to. Keep in mind that the columns will auto-size to fit the widest thing in them, whether it's the heading or the data. So if the data is typically short, like a number, then you'll probably want to keep the heading short too.
Custom Colors for Column Heading
If this option is checked, you can select custom text and background colors for the heading. This isn't commonly used, but you might want to use it to highlight a column for some reason. An "Example" box will show the current colors, and the "Text" and "Background" buttons are used to pick the desired colors.
Data Color Scheme
If you want to use something other than the default Windows colors for the data in the column, then you need to use a Color Scheme. Appropriate context will be available in the color scheme, such as the record being shown and the from/to dates selected for the Query. Thus you can create a color scheme that colors the data according to content or anything else about the record being shown.
If you haven't created the color scheme you need yet, or if you find that adjustments to the color scheme are needed, then you can use the Edit button next to the color scheme selection list.
Tip: If you just want to use the normal Reservation color coding like the other tab views do, then create a Color Scheme that has "Reservations" as the Default scheme. No rules need to be added to the scheme.
Show Group Totals
If this box is checked, then the "totals" will be calculated for the column and shown at the bottom. If there are blank lines inserted in the query due to sorting groups (see "Blank lines..." below), then sub-totals will also be inserted in those blank lines.
The calculations for the totals shown can be a Sum, Average, or Count. Sum is obviously just the sum total, Average will divide the total by the number of records involved, and Count will simply show the number of records.
Tip: To get a total number of records at the bottom like you see on most tab views, check this box for the first query column and select "Count" as the totalling method.
Format
This determines the format of numeric values, assuming the expression results in a number. For non-numeric data, the "General" option should be chosen. For numeric data, select an appropriate format, e.g. Currency, Integer, Percent, or Floating Point. If none of these quite fits your needs, then you can select Custom Format and enter an expression to format the data any way you need to.
Note that technically you could format the data in the Field/Expression itself, for instance by using "Currency(Tran:Tran_Amount)" instead of just "Tran:Tran_Amount". However this would not allow the values to be totalled since the expression no longer results in a numeric value.
The Format specification also determines the format of the total and sub-totals, if the Show Group Totals option is selected.
Custom Format Expression
This field only appears when the Format selected above is "Custom Format". To edit the format, click the Edit button or simply click on the text box below it. This invokes the dialog to edit the format expression.
For the most part, the format expression should simply convert a numeric value to text. The numeric value to be formatted will be available with the context function ThisValue, which will already be shown in the expression the first time you edit it. There are several functions available for formatting numeric values. For a simple example, lets say that we want to take the number an show it as a currency value rounded to the nearest dollar. The expression entered would be:
Currency( Round( ThisValue(), 0))
Technically you can do anything you want in the format expression -- the context will also have the record of interest and the from/to dates of the query. So the format could even include other fields of the record.
Remember that the format only affects the way that the number is displayed, not the way it's sorted or totalled, which is why you would do the formatting here instead of in the main Field/Expression for the column.
Align Text & Heading
Select the desired alignment, or justification, of the data (text) and the column heading. Typically the heading is always centered, but there may be cases where you want it left or right justified.
Sort this column by default
When you want a specific column in the Query sorted when it's initially displayed, check this box for the column to be sorted. If no columns have this checked, then it will be sorted by the first column. You can also select the direction of the sorting, ascending (lowest on top, highest on the bottom), or descending.
Only one column in the Query should have this option checked (if more than one column does have it checked, the first column found will be sorted).
This does not affect the method used to sort the records -- it will be the same as if the column header is clicked to sort the column.
Blank lines between sorted groups
If this box is checked, and if the query is sorted by this column, then blank (non-record) lines will be inserted between any different values. This is similar to the Transactions detail view, where blank lines are inserted when it's sorted by date, type, category, etc. Only one blank line can be inserted between each different value, no matter how different they are.
Note: When deciding whether the values are different, only the result of the Field/Expression for the column is used. If a special sorting hierarchy is defined for this column, or a default sorting hierarchy for the Query is defined, it may affect the order of records within a "group" but it will not cause extra blank lines to be inserted.
When blank lines are inserted, any columns with the Show Group Totals option will also have sub-totals inserted in the blank line.
Pop-up Tip Expression
If you want something to appear in a pop-up hint whenever the mouse cursor is placed over this column, then you can enter an expression here. To edit the tip expression, click the Edit button or simply click on the text box below it. This invokes the Expression Creator dialog to edit the tip expression.
The result of the expression should be a text string to be displayed. Typical context will be available for the record underneath the cursor, so the expression can use fields from the record being shown, or any other information.
Note that the pop-up tip can only be a single line, so be careful about its length.
Double-Click Action Expression
This can be used to make something happen when this column is double-clicked on. To edit the expression, click the Edit button or simply click on the text box below it. This invokes the Expression Creator dialog to edit the expression.
This expression could be anything from showing a message box with information about the record to executing a Script performing all kinds of functions, even changing the values of fields. For instance, you might have a "Clean" flag defined for Sites, and make double-click change the flag back and forth between "Yes" and "No". Just be careful about doing things that can't be easily undone, since a double-click might be accidental.
Special Sorting Value/Hierarchy
This allows you to change the way this particular column is sorted, for instance when the header is clicked on to sort it. This is a list of expressions used for sorting, the same way the Query's Default Sorting Hierarchy works, but is only used when this column is sorted rather than for all columns. Any current sorting expressions are shown in a list. To edit the sorting expressions, click the Edit Special Sorting Value/Hierarchy button.
By default, the column will sort based on the result of the main Field/Expression -- so that result is compared directly for each record, whether it's a numeric, text, date, or boolean value. If this doesn't result in a reasonable sorting, then enter special sorting expressions to sort the way you want. If any sorting expressions are entered here, the Field/Expression for the column will not be used at all for sorting.
The most common value needing special sorting is the Site name or abbreviation. Sorting by the text of the site name will rarely result in the order you want. In this case, you actually want to sort by their record order, that is the order they have been set up in the Sites data table. Therefore you would use this expression for sorting a Site column:
SiteOrder( Site() )
Another common issue is upper-case and lower-case values, since "Smith", "SMITH" and "smith" would all be considered different when compared directly. For this you would want to convert them to all upper-case and sort that way so they're all considered the same, without actually changing the data shown in the column. This would be done using the Upper() function -- see an example in the next section, Sorting Hierarchies.
Additional Topics:
Advanced Customizations Overview & other topics