This chapter describes how to print out selected data from a database, including the printing of individual records and of labels. Such a print-out is commonly referred to as a report.
Printed output may be displayed in a window, directed to a specified text file or sent straight to the printer. Choosing Options from the Print submenu (keystroke equivalent Ctrl Print) displays the Print Options window and you will see at the top of the window three radio buttons, Window, Text file and Printer, which let you select the output destination. Descriptions of these options follow.
This is the default setting and, as its name suggests, it displays the completed report in its own window. Clicking with MENU over this window opens the Report menu which offers five choices:
Save as text produces a Save box from which an icon may be dragged to a filer window or to any application which can accept a text file. The supplied pathname uses an appropriate leaf-name for the file and points to a directory called PrintJobs which is inside the database directory. Each database has its own PrintJobs directory which can be opened by choosing Show jobs done (Ctrl P) from the Print submenu. This menu choice also has its own submenu listing the directory contents. To save files with the least bother simply click on Save or type Return. You can, of course, delete the pathname (Ctrl U), type in your own filename and drag the file icon to any open directory. You might also be able to produce hard copy by "saving" the text to the Printers icon on the iconbar, although some recent printers contain no fonts of their own and therefore cannot print text files in this way. Look in PrintJobs regularly and get rid of files which you no longer need. (See also 3.1.2)
Sort will sort the report on whatever column the mouse pointer was over when you clicked MENU, provide the report is in Horizontal format (see 3.2.1). The default sort order is ascending, i.e. from A-Z if alphabetic, from lowest to highest if numeric, from earliest to latest if dates or times (provided the fields are designated as type Date or Time). A submenu allows the choice of a descending sort if required. After sorting the report header shows the sort field and type of sort.
Shrink list will remove as much surplus "white space" as possible from between the columns of a report. This item might be shaded: white-space removal can be set to occur automatically before the report is displayed (in fact this is the default setting). If this is the case no further space can be removed and the menu choice is therefore made unavailable.
Discard removes the report window from the screen and recovers the memory it occupied. You might find this useful if you run short of memory after creating a large report.
Restore will put a sorted report back into the order it was in when first generated.
Reports saved from the report window to the PrintJobs directory may be reloaded. Unless PrintJobs is empty, Show jobs done on the Print submenu will itself have a submenu. Choosing a saved report from this menu using SELECT will reload the report and display it in exactly the same format, colours etc. as when it was first created. If in Horizontal format the report may be sorted and records can be retrieved by double-clicking (see 3.1.3).
For the above reconstitution of the report window to be possible it is necessary to save three files for each report. One of these is a plain text file which may be viewed in an editor, printed out, incorporated into a wordprocessor document etc. The other two are data files with the same name as the text file stored in subdirectories called RecNums and Tabs. If either of these ancillary files is missing it will not be possible to display the report as it was and it will probably be simply loaded into your text editor instead. This is what will also happen with text files which get saved in PrintJobs by Powerbase functions other than normal database reports.
To avoid leaving unwanted data files in RecNums and Tabs when deleting a report it is best if deletion is carried out from the Show jobs done submenu rather than directly from a filer window. To do so, choose the reports to be deleted from the submenu using ADJUST. The menu will remain open and the chosen reports will be ticked. To untick a report click again with adjust. Delete ticked will only be unshaded when at least one report is ticked. Choosing Delete ticked will then delete all the ticked reports, removing all three files pertaining to each report. Delete all will delete the entire contents of the PrintJobs directory after requesting confirmation.
An option button in the Preferences window, Re-load last report allows the last saved report to be auto-loaded on opening a database. This is deselected by default. To activate the facility you will need to select the button and save the Preferences file either in the database (in which case it is operative for that database only) or in Powerbase, which will make it A default for all databases.
The report window has one more useful feature. If you point at a particular piece of displayed data and double-click with SELECT the record window will come to the front displaying the relevant record. If the field corresponding to the item you clicked is editable the caret will be placed in that field. If you are using a report to look for errors in the data you can quickly correct them by this method but the report won't change to reflect your corrections until you re-create it. If you double-click with ADJUST instead of SELECT the record window opens at the pointer and its size and scrolling are adjusted to show only the required field. When a record has been retrieved by this method the relevant line in the report turns grey as a useful reminder.
A Save box will appear as soon as you tell Powerbase to go ahead and generate the report. The supplied pathname is the same as would be displayed when saving from the report window. Simply click Save to save it in PrintJobs under that name. Alternatively, type a filename and drag the icon to an open directory. The report will then be created and the file closed. Nothing else appears on the screen in this case. The File destination is of greatest use when reports are being produced from a Script file (see Ch 12).
When the Printer radio button is selected the More button alongside becomes available. (It is shaded when Window or Text file is selected.) Clicking More opens the Printer Setup window which provides a wide range of options. You may specify the number of copies to print, choose the font and point-size to be used, print with the paper upright (portrait) or sideways (landscape) and set the inter-line spacing and any or all of the four margins. All measurements other than font size may be specified in mm (default), inches or points. If you are using a colour printer the colours for headers/footers, report body and rules, as specified in the Print options window, will be reproduced.
Powerbase is capable of producing reports in four different formats. Only two of these, Horizontal and Vertical, are available when you print to a window or a file and these are selected via two radio buttons on the Print Options window. When the output destination is Printer two additional formats, Table and Label, are also available and are selected from the Printer Setup window. These four formats will now be described.
This is so-called because each record appears on a single, horizontal line with the fields aligned so that they form neat columns. Non-numeric fields are left-justified. Numeric fields, and others whose content is treated as a numeric value (see 3.5.2), are right-justified. A header line identifies the columns by means of the tags or descriptors of the fields and this header can be made to appear on every page of the report or on the first page only. An optional descriptive title may also be incorporated. The Spacer is used to separate columns (see 3.10.2). All these features (and others) are chosen from the Print Options window.
Besides the ability to sort a report in a window (see 3.1.1) you may force Powerbase to sort the report before displaying it. To do so, select the Sort on option button in the Print Options window and enter in the associated writable icon either the tag of the field on which you wish to sort or the column number of the report. The feature is only of use in Horizontal and Table formats, but may be used with the Printer destination as well as with Window. When the Sort on button is selected, another button next to the writable icon becomes active. Clicking on this toggles the blue arrow to point up or down, specifying either an ascending or a descending sort.
Fields are printed underneath each other with the identifier (tag or descriptor) at the left hand side. Where the field selection includes an External text file, i.e a Text or Text block field, the Vertical format is the only one which may be used and will be selected automatically. Although each field normally occupies a line to itself you can override this by holding down Shift as you click with ADJUST to select the field. You will then not get a new line after the field: the next field to be selected will appear (with its identifier) on the same line. We will call this function field concatenation. It may be applied to any number of fields: keep Shift down while selecting all except the last one to appear on the line. This feature is very useful if your report contains a mixture of long fields which need a line each and short ones which don't and would otherwise result in wasted space and paper. Concatenated fields are separated by the Spacer and the width of the report is governed by Text width (see 3.10.2).
This, together with the Label format, is only available when outputting to the printer and the options associated with both formats are therefore selected from the Printer setup window. It resembles Horizontal format but the lines and columns are separated by horizontal and vertical rules, forming a grid. When this format is selected a number of extra features are enabled allowing you to include extra (blank) columns and lines, making this format especially useful when you want a list to which information is to be added by hand (e.g. entering marks against a printed list of students). The number and width of blank columns and the number of extra lines may be specified. For a tidy result it is recommended that you increase the line-spacing from the default 120% to about 150% when using Table format.
This is meant for printing on special label stationery. Since such stationery is expensive you are advised to try out your settings on plain paper first! Selecting this format enables the label setup choices which include the label size and the number of labels in a row. It also allows optional fixed starting and finishing lines to appear on each label. The number of lines on the label is not needed: Powerbase works this out from the label height and print size and warns you if the data won't all fit.
Printing will normally begin on the first label in the first row on the sheet but, to enable you to use up a part sheet of labels, you may specify which label to begin with, e.g. for three-in-a-row labels, entering 5 would make printing start at the second label of the second row. (Remember that sheets in a feeder-hopper are upside-down!) After the first sheet the starting-point reverts to the first label in the first row.
Each field normally appears on a separate line but fields may be concatenated (see 3.2.2) with the Spacer being used to separate the fields (see 3.10.2). This may be necessary if you are using separate fields for surname and initials or surname and forename.
You may specify one field to be substituted for another if the latter is blank. Both fields are specified by tag and the Substitute button is set. This is useful in a school or college situation where labels are being addressed to parents. Mature students, for whom the "parent" field in the record is blank, can have their own names printed instead. Another button makes the primary key of the record appear in small print on each label as a means of identification. This can be useful if the data printed on the label doesn't make it obvious which record it comes from.
Powerbase can use many different types of field. All are described in 4.2.5 to 4.2.13 in connection with setting up a new database and you should refer to those sections to clarify what follows here. You can print data from the following types of field:
(1) Any Editable field (i.e.one into which you can type directly), including Scrollable lists.
(2) Computed and Stamp fields (except Logos).
(3) External fields of Text and Text block type, and the pathnames in Remote fields.
(4) Check-boxes. What is printed for these differs from what you see in the check-box.
Thus:
(a) Tick/Cross boxes result in "Yes" or "No"
(b) Tick/Blank boxes, Option buttons and Radio buttons result in "Yes" or "-"
(c) Star/Blank boxes result in an asterisk or "-"
(d) Blank/Tick/Cross boxes result in "-", "Yes" or "No"
(e) ?/Tick/Cross boxes result in "?", "Yes" or "No"
(f) Blank/M/F boxes result in "-", "Male" or "Female"
Note that (d), (e) and (f) are three-state check-boxes; (a), (b) and (c) are two-state check-boxes. It is, of course, possible to print Draw and Sprite fields from individual records by loading the external file into Draw or Paint and printing from that application (See 2.6.2).
The field or group of fields selected for printing is called a print selection. Point at each of the required fields and click with ADJUST. The fields will be highlighted by reversing the foreground and background colours. Only those fields which are printable (see 3.3) will respond to ADJUST in this way. A second click will de-select the field. Note that the order in which you select the fields is important since that is the order in which they will appear in the report. The menu button at the bottom-centre of the Match window (see 3.5) will list the fields in the order in which they have been selected. Ctrl F has the same effect. (If no fields are selected Ctrl F gives a listing of all the fields.)
A contiguous range of fields may be selected by placing the caret in the first field then double-clicking with ADJUST in the last. To select all printable fields choose Select all ( Ctrl A) from the Print submenu. There is also a Clear selection entry on this submenu (Ctrl Z).
Although a Scrollable list is, strictly speaking, a single field, its columns are selected for printing individually. You will find, however, that the order in which the columns are highlighted is immaterial; they are always printed in the order in which they appear in the record window. For other options applicable to printing Scrollable lists see 3.10.1.
Print selections may be saved for future use. Save selection from the Print submenu leads to a Save box. Accepting the default pathname will save the file with the name Selection in a directory called PrintRes. Just as every database has its PrintJobs directory, so does it also have its own PrintRes (i.e. "Print Resources") directory whose contents can be displayed with Show resources (Ctrl R) from the Print submenu. . Selection files are of type &7f3 and are distinguished by their icon which bears a large S. Because they have a specific file-type which Powerbase recognizes they can be loaded by double-clicking on them. If PrintRes isn't empty then Show resources will have its own submenu which provides another means of loading files.
You may save as many print selections as you like and their names may include any character which is allowed in file-names, but one name is special: a file saved as !Selection is treated as a default selection. When you instruct Powerbase to create a report without having first highlighted the fields to be included, the default selection will be searched for in PrintRes and used. If there is no such file the primary key field(s) will be printed. As soon as the report is complete the selection is cleared. You won't see the highlighting of the fields at all when a selection is used automatically in this way.
To save a default selection you need only select the option button Default selection in the Save box (thus causing the "!" to be added) and accept the supplied pathname by clicking Save or typing Return.
Unless we want to print all the records in the database we need some means of telling Powerbase what are the common features of the records we wish to print. There are two ways of doing this. The more versatile way (and the one which Powerbase uses by default) makes use of a search formula or query describing the characteristics of the required records. The remainder of this section deals with the construction and use of search formulae. For the alternative method, query by example, see Section 3.6.
If you click the Print button on the tool-pane a small window with the title Find matching records appears. We will call this the Match window. The same thing happens if you choose Print from the main menu, or Create report from the Print submenu, or type the Print key on the keyboard. The most prominent feature of the Match window is a group of icons enclosed by a thin red border. This object is called the Query panel and you may have already seen it since it forms part of several windows. It appears on the Filter window for example (see 2.3.3) and is also used when making Global changes (2.5.5), performing a Batch move/delete operation (2.5.6), exporting a CSV file (8.3) and creating a Subset (Ch 13).
The writable icon in the Query panel, in whatever context the latter appears, is meant to take a search formula. The simplest thing you can do, of course, is to type nothing at all! If you then click on the Print button you will create a list of all the records in the current subfile of the database. You could achieve the same result by typing ALL* indeed if, after producing the above list with a null formula, you click on the Old button (Ctrl O, which retrieves the last-used search formula, you will find ALL displayed.
Most database queries will involve a selected group of records. A search formula describes the criteria which records must match in order to be included in the report. Getting to grips with search formulae is, perhaps, the biggest hurdle faced by the new Powerbase user and you are referred first to the simple examples described in the Tutorial file. A search formula consists of one or more search elements. A search element specifies that a field value must fit a certain condition and takes the form:
where tag1 etc. are field tags (see 4.2.4) which uniquely identify the fields to be matched. (NOTE. In. v.9.20 it is also possible to query data in validation tables linked to fields. See 5.9) A target list (if it contains more than a single target) has the form:-
where target1 etc. are the data items which are to be compared with the contents of the fields specified in the tag list. The comparator which links the tag list and target list determines the type of comparison to be made. The commonest is "=", meaning that one of the items in the target list must exactly match the content of one of the fields in the tag list.
If the Case button on the Query panel is selected then all comparisons will be case-specific, e.g. "Cat" will be regarded as different from "CAT" or "cat". If the Case button is not selected all those three will be considered identical.
The heading of a report shows which fields were used in the search formula, what targets were specified and what type of comparison was made. If a target was placed in quotes (which is the only way of searching for any string containing a comma, for example) then it appears in quotes in the heading also.
It is impossible to describe the use of search formulae adequately without quoting actual examples. As in the Tutorial file we will make considerable use of the Elements sample database. A simple example of a search formula consisting of a single search element is:
where GP is the field tag, = is the comparator and T is the target. This means "The field whose tag is GP must contain the value T", i.e. all transition elements (but no others) are to be included in the report. A slightly more complex one is:
This could also be entered as:
i.e. you may specify a range of adjacent fields by giving the first and last separated by a hyphen. Where you don't know which fields to test you can replace the tag, tag list or tag range with @, which causes all the fields in the record to be examined.
Note that in these examples only one of the fields in the tag list is required to match one of the targets in the target list (although it doesn't matter if more than one field matches more than one target). Sometimes we want an inclusive search so that all of the fields in the tag list match a given target or, less frequently, a field contains all of the values in the target list. It's a matter of connecting the search elements with AND instead of OR. You can do exactly that (although the following example is chemically nonsensical!):
You may also save typing by using the ampersand (&) instead of the word AND, but the same result can be achieved even more briefly by simply doubling the comparator, in other words using == instead of = so that the formula becomes:
{ should be read as "contains" and }{ as "does not contain". These are used where the target value must (or must not) be part of the field but isn't expected to make up the whole field. The use of all the items in a target list to be matched in a given field. e.g. Suppose we knew that someone's house number was 17 and that they lived on "<something> Avenue" but the actual name couldn't be remembered. In a database of addresses a search formula such as:
(note the doubled comparator) would find it by listing all records where ADDR contained both 17 and Avenue, whereas:
would find all those addresses where the house number was 17, regardless of street name, and all those addresses with "Avenue" in them, whatever the house number.
You may invert the logic of a search criterion by putting NOT in front of it. To print all non-transition elements you could use:
for all transition metals with atomic numbers greater than 50 and names containing IUM. Use OR when a field need meet only one of a set of criteria. e.g.
would find all lanthanide and actinide elements as the formula means "either L or A; I don't care which". AND and OR can produce ambiguous search formulae e.g.
could mean either "elements in group 1 or 2 (don't care which) with atomic numbers less than 50" or "group 1 elements (of any atomic number) or group 2 elements whose atomic numbers are less than 50". You probably want the former, but Powerbase will give you the latter. To get what you require use brackets to make the logic clear. In other words write it as:
For most types of field the comparison with the target is made by character matching but for certain types the comparison uses the numeric value of the field. The fields concerned are:
the record would be included in the report if NUM contained 5, 05, 5.0 etc. because all of these have the same numeric value. If you had an Alphanumeric field called NUM the same search formula would only match records where the content was literally 5, i.e. the character "5". This can easily catch you out. Suppose, for example, you want to print records for which NUM<8. You might be surprised to find records in which NUM contains values such as 55, 20, or 13 being printed, as well as those containing 4, 6, 2 etc! If this happens check what type of field NUM is. Unrestricted and Alphanumeric fields will give the above result; Numeric fields (and the others listed above) will give the result you probably want.
You can force a comparison by numeric value for a field which consists of (or, at least, begins with) numerals, even though the field is not defined as of Numeric type, by enclosing the field tag in square brackets, e.g. [NUM]<8 would produce the desired result in the above example even if the field is Alphanumeric or Unrestricted. This is useful where you want to make a comparison but still allow the field to accept non-numeric characters. The comparison-by-value can only work in such cases if the number part of the field comes first. e.g. it will deal correctly with 55A, 20B, 13X but not with A55, B20, X13.
The use of characters "$" and "#" as "wild-cards" was described in 2.5.5 in connection with search-and-replace operations. They may be also be used in search formulae. "$" is used to represent a group of characters and "#" to represent single characters which do not need to be matched. e.g. If (still using the Elements database) you type:
you are, in effect, saying "find all the elements whose names end in ON; I don't care what precedes ON as long as nothing follows it". Powerbase will duly find CARBON, BORON, NEON etc. If you were to use:
You would find PLUTONIUM, POLONIUM and a few others but none of CARBON, BORON, NEON etc. Something must follow ON as well as precede it. (To print both sets of elements you would use NAME{ON.)
Note also that:
finds PROTOACTINIUM, PLATINUM etc, but not TIN itself.
finds all names which begin with S and end with IUM, e.g. SAMARIUM, SCANDIUM, and SODIUM. The effect of:
is somewhat different. You are, again, asking for names which begin with S and end with IUM but this time SAMARIUM and SCANDIUM would be found, but not SODIUM since you have specified exactly 4 wild-carded letters between the S and the I. Finally, to find any 5-letter name, regardless of the actual letters:
If search elements are formulated in exactly the same way as for other fields, the entire list is examined for a possible match. e.g. If the tag of a Scrollable list is LIST entering:
will search every cell of the list and report the record as a match if any one or more cells contain the target string. The test can be restricted to a single column of the list by appending the column number as follows:
which would search cells in the second column only.
Data in Scrollable lists is always of string type, even when it consists wholly of numerals. A comparison such as:
The following formula will find records in which a cell in column 2 has numeric value>15:
A field tag (instead of a literal string) may be specified as a target, thus allowing two fields in a record to be compared to produce, for example, a list of all records in which the relevant fields have the same content. This might interfere with a "normal" query where the required literal target happens to be the same as the tag of another field. The problem can be overcome by enclosing the literal string in quotes.
Choosing Save query from the Print submenu opens a Save box from which the search formula may be saved. By default the file is saved in PrintRes under the name Query. Selecting the Save as default button on the Save box will cause the file to be saved as the default query with the name !Query. If such a file exists in PrintRes it will be automatically entered in the Query panel whenever the Match window is opened. A default query file, in other words, behaves in a similar way to a default selection file as described in 3.4.2. Query files are of type &7f4 and are recognizable by the large Q in their icon. You may save as many Query files as you like and load them into the query panel by double-clicking on them.
After that lengthy description of the search formula method of querying the database we turn to the alternative: query by example. For brevity when comparing the two we will refer to them as QSF and QBE respectively. To select query by example choose Preferences from the iconbar menu, select the option button Query by example and click on Accept. The option then becomes active for all operations which would otherwise involve typing a search formula into the Query panel.
The user is presented with a blank record and invited to type into the relevant fields the data which must be matched in order for the record to be included in the report. What you are saying in effect is: "I want a list of all records which look like this. I don't care what's in any of the fields I haven't filled in, but the ones I have filled in must correspond to what I have typed." e.g. in the Elements database if you wanted to print a list of all transition metals you would simply enter T in the Group field and then proceed with the report. The tag of the field isn't needed at all, whereas using a search formula requires you to type GP=T. Although you can't normally place the caret in Computed fields and Stamp fields you'll find that you can do so when using the blank QBE record. Check boxes will initially display an "i" (for "ignore") and any check-box left in this state will be ignored in the query. Clicking a check-box when setting up the query removes the "i" and displays the normal sprites (tick, cross etc.). Check-boxes so treated do figure in the query. Enter the data to be matched then either click with SELECT on the Print button of the Match window or else type the Print key.
If you simply enter the required target strings Powerbase assumes that you want the all relevant fields to match exactly, i.e the effect is the same as using "=" in every search element (see 3.5.1) of a search formula. There are, however, other comparators besides "=" which may be used in search formulae. (see 3.5.1 for explanation and complete list). You may use any of these in a QBE query by placing them at the start of the string, e.g. in an Address field would match all records where the field contained the word "Avenue". An address such as "15 Acacia Avenue" could be found by this method whereas just entering the word "Avenue" wouldn't work because it would require the field to read "Avenue" and nothing more.
Wildcards may be used; e.g. you could print from the Elements database all elements ending in IUM by entering $IUM in the NAME field or all those whose symbol began with H by entering H# in the SYM field.
You may specify a target list (see 3.5.1) to make the search include all records matching any item in the list. e.g. Leeds,Liverpool,Manchester in a Town field (if it will fit) would cause records with any of these places to be included. You can also specify a field list (equivalent to a tag list ; see 3.5.1) provided that the fields form a contiguous group. The target string (which may be a target list, be wild-carded or be preceded by a comparator) is entered in the first field of the group. Press Return and enter " (double quote or "ditto" mark) in the next field and for the remaining fields of the group. (Pressing Return rather than moving the caret by means of the mouse ensures that you really are dealing with a contiguous group of fields.)
So what are the advantages and disadvantages? QBE is very intuitive and avoids the need to bother with field tags or the minutiae of search formula syntax. On the other hand QSF is more comprehensive and flexible: there are some things you simply cannot do with QBE. Some of the limitations have already been mentioned but here is a complete list:
As supplied Powerbase uses QSF as the default query method and the Query by example button will be deselected when the Match window is displayed. If you want to make QBE the default you can edit the relevant line of the Config file in ! Powerbase.Resources to read Query QBE instead of Query QSF. Don't forget the space. Selecting the Print function will then produce the blank record without displaying the Match window at all. There is, of course, no Print button to click with the mouse after you have entered the target strings so you tell Powerbase proceed by typing the Print key after entering the data to be matched.
The Case and Old buttons on the Query panel have already been dealt with. Selecting Reverse causes whatever index is in use to be scanned in reverse order, e.g. alphabetical lists will be produced in Z-A order. The Indexes button is discussed briefly in Section 3.13 and in detail in Section 7.3.
Help opens the Help window which offers another way of building search formulae which might appeal to beginners. Select the target field by cycling through the tags with the bump icons or by choosing from the pop-up menu. Choose the comparator by selecting a radio button. Type the target value into the writable icon. Place the caret in the Query panel writable icon and click Add to formula. The search element will appear at the caret. You may click on AND or OR and enter other search elements in the same way. If you wish to use the NOT button you must do so before clicking Add to formula. Powerbase inserts the brackets round the search element for you.
Holding down Ctrl and clicking on a field with SELECT while the caret is in the Query panel causes the tag of the field to be entered in the search formula at the caret. This, together with the above method of constructing search formulae, largely overcomes the problem (especially when using someone else's database) of not remembering what the field tags are.
At the far left of the Match window is a group of four radio buttons labelled Print, Count, Mark and Clear. Only one of these may be selected at a time and the default action button at the bottom right of the window reflects whichever one you select. When the Match window is opened it is always Print which is selected, this being the most often used feature. If you merely want to know how many records match a specified set of criteria, without printing them, select Count. The number of matching records appears to the left of the Cancel button (which merely closes the window). Mark and Clear are explained in 3.8.2 below.
The Fields selected menu button will be shaded if there is no field selection, otherwise it lists the selected fields in the order of selection. This last is well worth remembering since there is no other indication of the order in which fields were selected for printing. The icon just to the left indicates the selected output destination (see 3.1) by displaying a representation of a window, a text-file icon, or a printer. In the latter case the icon will be shaded if no printer driver is loaded. Clicking with SELECT on the icon opens the Print Options window; in fact you might find this the most convenient way of doing so.
In many databases some keys may be repeated several times. This is especially true of subsidiary keys, but sometimes also occurs with primary keys. A report created with Ignore repetitions of key selected will contain only the first record having a given key; subsequent ones will be skipped.
Reports are usually created from records in the currently-selected subfile which is displayed in the title bar of the record window. Just under the Query panel is the legend Include subfiles: and a row of numerals, 0-5. When a database is opened 0 will be highlighted, indicating that reports will only include records from subfile 0. If you change subfile by clicking on the appropriate tool-pane buttons you will see this highlighting move from one number to another, showing the selected subfile. You can, however, click on these numbers so that any or all of them are selected. Subfiles are deselected with a second click.
When you create a report from more than one subfile the records are not merged into one alphabetically (or numerically) ordered list; the ordering starts afresh for each selected subfile. This isn't really a problem because you can always sort the completed report on any field to produce a single, ordered list (see 3.1.1).
A group of three option buttons in a frame to the left of the query panel allow you to include record numbers, the current key, and the subfile number in a report. The latter is especially useful if you have created a report from several subfiles and then sorted it as described above. If you need to keep track of which subfile a record comes from, create the report with File selected. The position in which these items appear in a report depends on the point at which the option buttons are selected. Think of selecting these buttons as an extension of selecting data fields, e.g. selecting a data field, then selecting the Key button, then finally selecting another data field would lead to a report in which the key was printed between the data fields. The extra items appear in blue on the Fields selected menu and are saved as part of a Selection file.
There are times when you want to print a number of records which have no obvious connection with one another: they may have a common feature which is obvious to you but none within the records themselves. Such a situation commonly occurs when you want to print a few mailing labels. No common feature means no basis for constructing a search formula. So how do you tell Powerbase which records you want to print?
A small panel attached to the bottom of the record window contains a check-box, Mark for printing etc., which you can tick to indicate that the displayed record is to be printed. If this panel is not present type Ctrl M, which toggles the feature on and off. With the default settings of Powerbase the mark panel is displayed but it can be turned off by an option in the Config file (see 14.8). Even so, Ctrl M will always bring it back. Using the Search button or the browse controls you can call up each record you want and tick the box. You then simply select the required fields and print in the usual way. If no search formula has been entered then only the marked records will be printed. (Printing without a search formula when no records are marked gives the whole subfile as described in 3.5) If you do enter a search formula you will get the records which match the formula plus the marked records, whether the latter match the formula or not.
A pop-up menu allows you to invert the effect of this feature so that printing without a search formula gives all records in the subfile except the marked ones and printing with a search formula gives all the matching records except for those marked. When the menu option is set like this (to exclude rather than include) the check-box shows a red cross instead of a green tick.
Next to the check-box is the Clear marks button which does exactly what it says. It is shaded when no record is marked. A further indication of whether records are marked is provided by the icon at the far right of the Query panel which displays either the green tick or the red cross when any record is marked. This applies to the whole database, by the way, not just to the current subfile.
Powerbase takes heed of marked records in any operation which involves the query panel, i.e. batch move/delete, global change, filter, export subset, export CSV file, as well as print.
It is sometimes useful to be able to mark (or clear marks from) a group of records which fit a search formula. This is made possible by the Mark and Clear radio buttons on the Match window. By repeatedly selecting Mark and executing different query operations you can build up a set of marked records by stages then, if desired, selectively clear the marks from certain ones. Finally, you can print your carefully-tailored selection of marked records without using a search formula at all.
Hold down Shift whilst clicking with SELECT on the Print button of the Match window or type Shift-Print on the keyboard. Yet another method is to mark the displayed record as described in 3.8.1 then do a "Print ALL". Only the marked record will be printed. The highlighted fields of the displayed record are printed using the currently-selected print format as determined by the setting in the Print options window. If no fields are selected the action is as described in 3.4.2 ; Powerbase will use the default selection if it exists or, failing that, print the primary key fields only.
To display this window you can choose Options from the Print submenu, type Ctrl Print, or click SELECT on the icon to the left of the Fields selected menu on the Match window. Features such as Destination (see 3.1), Format (see 3.2) and the Sort on facility (see 3.2.1) have already been dealt with extensively. The rest are covered here.
These may be printed in two different ways. The default is for all the cells in the selected columns of the list to be made into a single line. The entries in Sep and Row end are the strings used to separate data from individual cells in the same row of the list and to separate one such row from another. The defaults are ",<sp>" and ";<2sp>" respectively but you may provide your own strings (up to 5 characters each) if you prefer.
This format can result in very long lines indeed, especially if all the columns of multi-column lists are included in the print selection. An option button (Shrink line - selected by default) causes as much white space as possible to be removed, but lines could still be too long for the printer.
The alternative format puts the data from each row of the scroller on a separate line so that the data aligns in columns. This occupies less room horizontally but much much more vertically. Sep is used between the data from cells in the same row but Row end is shaded because nothing is needed to delineate rows.
It can be very inconvenient to have an entire list (or even all the data from a single selected column) included in a report when only a single cell is of interest. The option button Only if targetted in query (deselected by default) overcomes the problem by restricting what is printed to those rows in which the contents of a cell match a target in the search formula.
The remaining options require only brief explanations. Default settings appear in brackets after the name of the feature.
Headings (tags) appear at the head of reports in all formats except Label unless None is selected.
Expand codes (OFF) causes extra data from a validation table to be substituted for (or added to) the coded data in fields linked to such tables (see 5.2).
Expand headers (ON) will show the expanded versions (see 5.2) of the target values for fields linked to validation tables in the list header. Turning the option OFF causes the target values to be shown exactly as typed in the search formula.
Upper case (OFF) causes all textual output to appear in capital letters.
Print header (ON) causes the printing of header lines at the beginning of each page. The header includes the following information:
Print footer (ON). Reports in Horizontal and Table format normally end with a footer which specifies the number of records printed. If the output includes Numeric or Check-box fields and column calculations have been selected (see 6.6) the results of these too will be part of the footer.
Date stamp (ON) makes the date and time when the report was created appear as part of the header.
Shrink list (ON). In Horizontal and Table format the width of columns is determined by the maximum defined length of the fields included in the print selection. These lengths are often greater than the length of data actually present in the fields, resulting in a lot of "white space" between columns. With this option ON the surplus space will be automatically removed. Even if it is OFF you can still remove white space via the Report menu (see 3.1.1). Output to Printer always removes white space whether this button is ON or OFF
Page numbers (OFF) allows page numbers to appear at the bottom of each page of a report. This feature works quite independently of the Print footer button.
Page length (0) determines the total length of page, including header, footer and top margin, for destinations other than Printer (for which the page length is determined by the top and bottom margin settings). The default value of 0 means no division into pages at all, but you might want to alter this if you drag text-files to the printer. An A4 page is 70 lines long, but you won't be able to print on them all and page-feeds might occur in the wrong place. Look at Edit paper sizes on the iconbar menu of Printers. Subtract the displayed top and bottom text margins from 70 and enter the resulting value. In Vertical format Powerbase will try to avoid splitting a record between pages, but this can happen if the report includes Text or Text Block fields of greatly varying length. (It will also happen if the number of fields to be printed exceeds the length of the page!)
Text width (A) specifies the line length used when printing in Vertical format. A means "Auto" and lets the program calculate the value. You may enter your own value (e.g. 70) to override this.
Colours (red, black, green respectively) may be chosen for headers (also footers), the report body, and rules. Only foreground colours may be specified. This is done by clicking on the icons with SELECT to cycle through the 16 standard Wimp colours. (ADJUST cycles in the reverse direction.) The colours are used for reports sent to a Window or to the Printer.
Spacer (1) specifies how fields printed on the same line will be separated. Fields are first padded with spaces to the maximum width of the relevant data field (but see Shrink list above) and the spacer string is then printed before starting the next field. Four interpretations of the contents of this icon are possible:
All the settings in the Print Options and Printer Setup windows may be saved as a Print Options file. Clicking Save choices with the in database radio button selected brings up the familiar Save box. By default the file is saved in PrintRes under the name PrintOpts. As with Selection (see 3.4.1) and Query files (see 3.5.6) you can save a default options file called !PrintOpts by selecting the Default options button on the Save box and Powerbase will load this whenever the database is opened. Options files have a large P on their icon and a filetype of &7f5. You may save as many as you wish and load them with a double-click. If the in Powerbase radio button is selected the options are saved as the Powerbase default and no Save box is displayed. Load default reloads this file, overwriting any changed settings.
When the report destination is set to Printer clicking Lots m ore on the Print options window gives access to the Printer setup window. Some of the features of this window have already been described in connection with the Table (3.2.3) and Label (3.2.4) formats. Despite the complexity of the window most of the rest is fairly self-explanatory and only a few comments are necessary.
It is very easy to choose a combination of field-selection and point-size which makes it impossible to fit the entire line into the available width, especially if the paper is upright (portrait mode) and printing in two or more columns is selected. When Powerbase has prepared the first page of data for printing it looks to see whether the longest line will fit. If not, it calculates what the point-size would have to be reduced to in order to make it fit. If this results in an unfeasibly small size (less than 6pt) you will be advised of this and asked if you wish to proceed at the original point-size with some loss of data or cancel the job. (You will be told which field(s) will be omitted or truncated.)
If the calculated new size is 6pt or greater you have three choices: use the new size, proceed with the old size (with data loss as described above) or cancel the job. If the print job is cancelled you should then look to reducing the number of fields printed or changing the print format.
There are places in the window for setting all four margins. If, however, you set a margin which is less than the minimum specified by the printer driver (which will probably mean as near the edge of the paper as the printer is capable of printing) then the printer driver's minimum is used instead. This particularly affects the minimum bottom margin which is quite large on many ink-jet printers; possibly 15mm or more. If you specify a bottom margin of 10mm and find you get one of 15mm it probably isn't Powerbase's fault! If a printer driver is loaded the As printer button will be available and all four of the printer driver's minimum margins will be used.
Hard copy printing from Powerbase isn't wonderfully fast but you can help things along by choosing sensible options in !Printers. Some users run a high-resolution colour ink-jet printer at 1440dpi in a colour mode and never think to alter it. We're only printing text, when all's said and done - 720dpi will produce excellent text quality and will be 4x as fast. 360dpi will be 16x as fast as 1440! For rough draft work even 180dpi might be acceptable, if your driver allows such a setting. Many users are unaware that using the printer in a colour mode is much slower, even when printing black only, than in a monochrome mode. (The software has to look for colours which might be there, even though you know they aren't!) Finally, the use of a good printer-spooler application such as !FastSpool+ will cause control to be returned to the user that much more quickly and is highly recommended.
The Field submenu has an Analyse option which allows you to print a breakdown of the field contents under certain special circumstances:
(a) If the field is indexed the menu entry will read Analyse index. When chosen it will produce a list of all the values in the index with the number of times each one occurs. e.g. A database of college students might have a field for the school of origin. If there are 20 different schools and if the field is indexed then a list of those schools will be generated showing how many students came from each school.
(b) If the field contains an 8 or 10 character date (e.g. 19-10-42 or 19-10-1942) the menu entry reads Analyse months and a breakdown by month will be printed. There will be a line for each month showing the number of records for that month. For example, this could be used in an orders database to find out quickly how many orders were received or dispatched each month. This feature works on editable Date fields and also on Date stamp8 and Date stamp10 fields in the Stamp class.
It is, of course, possible for a field containing a date to be indexed. Action (a), above, takes precedence in such a case. You can, however, force action (b) instead by first selecting the field with ADJUST, then choosing from the menu. For cases not described above the menu entry simply says Analyse and is shaded. Printing is always to a window (from which the report may, of course, be saved); the Destination buttons in the Print options window have no effect.
In many cases Powerbase can make use of subsidiary indexes to make reports complete much more quickly. This feature has existed for a long time but was, prior to v.9.20, very restricted. It has now been greatly extended and requires a substantial description, which will be found in section 7.3. You might, however, first like to try the following simple exercise with the Elements database.
Create an index on the Group (GP) field, select the element name only, and create a report using the search formula:
If you do this first with the Indexes button on the Query panel selected (default), then with it deselected, you will notice a difference between the times taken. Use ADJUST, not SELECT to click the Print button on the Match window so that the window remains open and you can see the actual times. On a StrongArm RiscPC the above test takes about 0.25 sec with Indexes selected and about twice as long with it deselected.
This might not sound like a difference worth bothering about, but remember that Elements is a very small database; you are printing 31 elements out of only 103. If you were targeting a simliar number in a database containing thousands of records the difference would be very marked indeed; a factor of 10 is common and a factor of 50 or more might be achieved in some instances.