Home   Index   list of chapters    previous chapter   next chapter   main tutorial  
 

Ch 3 - Creating reports from the Database

This chapter describes how to produce reports containing selected data from a database, including the printing of individual records and of labels. To create a report the user needs to: It sounds rather complicated but there are sensible default options and the finer points can be learned as you go along. The four requirements are treated in the order listed but if you want to plunge straight into generating a report (using the defaults) you can jump to sections 3.4 and 3.5.

 
 
top of page

3.1 Output destination

To 3.7 (Other features of the Query panel and Match window)
To 3.10 (The report options window)
To 12.3 (Setting report options via script commands)

dest/png Reports may be displayed in a window, directed to a specified text file or sent straight to the printer. Choosing Options from the Report submenu (keystroke equivalent Ctrl Print but see footnote to Appendix B) displays the Report options window, the topmost portion of which is illustrated. The three different output destinations may be selected via radio buttons and descriptions of these options now follow. A complete explanation of the rest of the Report options window will be found in section 3.10top of page

3.1.1 The Window destination
To 3.2.1 (Columns format)
To 3.7.3 (Reports containing records from more than one subfile)
To 3.10.2 (The rest of the report options)
To 3.11.1 (Line length and point size)
To 5.6 (The validation table menu)
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: top of page

3.1.2 Reloading saved reports
To 3.1.1 (The Window destination)
Reports saved from the report window to the PrintJobs directory may be reloaded. If PrintJobs contains any files Show reports on the Report 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 Columns format the report may be sorted and records can be retrieved by double-clicking (see 3.1.3).

For such 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 will also happen to text files, other than normal database reports, saved in PrintJobs.

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 reports 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 (see 14.1). This is deselected by default. To activate the facility you will need to select the button and then save the Preference file either in the database (in which case it is operative for that database only) or in Powerbase, which will make it the default for all databases.top of page

3.1.3 Calling up records from the report window
To 2.7.1 (Browsing and searching reports)
To 3.1.2 (Reloading saved reports)
The report window has another 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 correct them by this method but the report itself won't change to reflect your corrections until you re-create it. (A much better method of correcting records by means of a report is to use the List edit feature. See 2.7) 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.top of page

3.1.4 The Text file destination
When this destination is selected 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 Text file destination is of greatest use when a whole batch of reports reports is being produced using a Script file (see Ch 12). It is also useful for creating a distribution list for emailing a large number of people using Pluto or Messenger Pro.

This report destination allows you to append output to an existing text file. Make sure the file-name entered is the same as that of the pre-existing file and select the Append button on the Save box. If you do this for a file which doesn't exist the effect is as though Append were not selected. top of page

3.1.5 The Printer destination
pset1/png When the Printer radio button is selected the Lots more button alongside becomes available. (It is shaded when Window or Text file is selected.) Clicking Lots more opens the Printer setup window which provides a wide range of options. The illustration (right) of part of this window shows what's available:
The rest of the window is concerned with Table (see 3.2.3) and Label (see 3.2.4) printing. If you are using a colour printer the colours for headers/footers, report body and rules, as specified in the Report options window will be reproduced (see 3.2.1).

3.2 Report formats

To 3.10 (The Report options window)

Powerbase is capable of producing reports in four different formats. Only two of these, Columns and Field-per-line, are available when format/png you display the report in a window or create it as a text-file and these are selected via two radio buttons on the Report 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. top of page

3.2.1 Columns
To 3.1.1 (The Window destination)
To 3.1.5 (The Printer destination)
To 3.2.3 (Table format)
To 3.2.4 (Label format)
To 3.10 (The Report options window)
To 3.11.1 (Line length and point size)
To 15.8 (Customisation - Outputting text to a window)
In this, the default format, each record appears on a single line with the fields aligned so that they form neat columns. Here is a simple example of such a report.
horiz/png ruled/png
Non-numeric fields, e.g. NAME are left-justified. Numeric fields are right justified if they are integers ( e.g. Z) or fixed point. (So are several other types whose content is treated as a numeric value. See 3.5.2.) Floating point numbers (e.g. M) are justified so that the decimal points align. Several aspects of the report may be set from the Report options window:

3.2.2 Field-per-line
To 3.1.1 (The Window destination)
To 3.2.4 (Label format)
To 8.3 (Exporting data as a CSV file)
Here is part of the same report in Field-per-line format. The header (and footer) information is exactly the same as before but each field now occupies a line on its own with the identifier (tag or descriptor) at the left hand side.

vert/png

Where the field selection includes an External text file, i.e a Text or Text block field, the Field-per-line 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 (so it's not strictly "Field-per-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. concat/png 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. Suppose you'd selected the six fields for the above report in the following way:

    <Shift> NAME <release Shift>SYM
    <No Shift>ORIG
    <Shift>Z <Shift>M <release Shift>GP

The report would then appear as on the right, with the first two fields concatentated on one line and the last three on another. Concatenated fields are separated by the Spacer and the width of the report is governed by Text width (see 3.10.2).top of page

3.2.3 Table
To 3.1.5 (The Printer destination)
To 3.11 (The Printer setup window)

pset3/png This format is only available when outputting to the printer. It resembles Columns 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 rows, 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 rows may be specified. You may provide headings for the blank columns by entering them in the writable field to the right of the Table button. Separate the headings with commas. Headings which are too wide for the column will be truncated to fit.

For a tidy result it is recommended that you increase the line-spacing from the default 120% to about 150% when using Table format. If you are using a colour printer the grid lines will be printed in the Rules colour (see 3.2.1). The default ruling of the table grid uses solid lines, irrespective of the settings described above in connection with Columns format. However, if you do want to use dotted lines, or even no lines, for either the horizontals or verticals you can override this default setting by means of a setting in the !Powerbase.Resources.Config file or in a separate Config file inside the database directory. top of page

3.2.4 Label
To 3.1.5 (The Printer destination)
To 3.11 (The Printer setup window)

pset4/png This format 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.

Note that the horizontal and vertical dimensions are left-edge-to-left-edge and top-edge-to-top-edge distances and may therefore be greater than the actual label dimensions. A menu button enables you to choose one of the standard Avery label sheets and will set up the options automatically. If you use other types of label you can redefine this menu by editing the file !Powerbase.Resources.LabelForm and then re-starting Powerbase. The position of printing on the label is determined by the Left and Top margin settings (see 3.1.5).

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 are upside-down in the feeder-hoppers used by some ink-jet printers!) 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. Fixed lines (i.e. the same for every label) may be printed before and/or after the selected fields and these lines will be printed in the Header font (see 3.1.5). Field data is mainly printed in the Body font (but see below). If a colour printer is in use then the Header and Body font colours will be used (see 3.2.1).

The Label format is most commonly used to print mailing labels and Powerbase can enhance these in two ways:

You may specify one field to be substituted for another if the latter is blank. Both fields are specified by tag and can be entered when the Substitute button is set. This is useful in a 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. A Sequence number is simply an incrementing value printed in small numerals on each label so that you can see from the label how many have been printed.top of page

3.3 What types of field can be included?

To 4.2.4 (More about tags and descriptors)

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 include 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, Frames and Plinths).
  3. External fields of Text and Text block type, and the pathnames in Referenced fields. Although Draw and Sprite fields cannot be included in this way it is possible to print such fields from individual records by loading the external file into Draw or Paint and printing from that application (See 2.6.2).
  4. Check-boxes. What appears on the report differs from what you see in the check-box.
    Thus:
    1. Tick/Cross boxes result in "Y" or "N"
    2. Tick/Blank boxes, Option buttons and Radio buttons result in "Y" or "-"
    3. Star/Blank boxes result in an asterisk or "-"
    4. Blank/Tick/Cross boxes result in "-", "Y" or "N"
    5. ?/Tick/Cross boxes result in "?", "Y" or "N"
    6. Blank/M/F boxes result in "-", "M" or "F" (for Male and Female)
Note that 4, 5 and 6 are three-state check-boxes; 1, 2 and 3 are two-state check-boxes.

3.4 Specifying which fields to include

To 2.5.7 (Hiding sensitive data)
To 5.8 (Including validation table data in reports)

The field or group of fields chosen for inclusion in a report is called a field 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 can appear in reports (see 3.3) will respond to ADJUST in this way. A second ADJUST 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 on the Match window, beneath the Query panel, (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 reportable fields choose Select all (Ctrl A, but see footnote to Appendix B) from the Report 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 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 Scrollable lists in reports see 3.10.1

If the selection includes a Referenced field the report will show the pathname of the object linked to the field. If the linked object is a plain text file it would be very useful to display the actual text instead: just as you can for the External Text and Text block fields. You can if fact do this by holding down ALT as you ADJUST-Click the field. As with Text and Text block fields the field-per-line format is automatically selected. top of page

3.4.1 Saving selection files
To 3.10.3 (Saving print options files)
Selections may be saved for future use. Save selection from the Report 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 Report 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 from which files may be loaded by choosing with SELECT.

If a Selection file has been loaded by double-clicking in order to modify it and re-save under the same name, you can avoid having to re-type the filename by simply choosing Save selection from the menu without opening the Save box.top of page

3.4.2 Default selection
To 3.9 (Reporting only the displayed record)
To 3.5.6 (Saving search formulae for re-use)
You may save as many Selection files 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 used as a selection. 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.top of page

3.5 Specifying which records to include

To 3.4 (Specifying which fields to include)
To 2.3.3 (Searching by filter)
To 2.5.5 (Changing many records at once)
To 2.5.6 (Moving or deleting many records at once)
To 3.7 (Other features of the Query panel and Match window)

Unless we want a report to contain all the records in the database we need some means of telling Powerbase what are the common features of the records to include. 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 3.6.

match/png

Clicking the Report button on the tool-pane opens the Match window. The same thing happens if you choose Report from the main menu, or Create report from the Report submenu, or type the Print key on the keyboard (but see footnote to Appendix B). 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. (For a list of these see Ch 2, Note 7.)

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 the Report button on the Match window 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.top of page

Clicking MENU over the Query panel's writable icon calls up a menu of the search formulae you have used in the current session and you my use them again by choosing from the menu with SELECT. Unwanted menu items can be deleted by choosing with ADJUST. This behavious is exactly the same as for searching for a record by key. (See Ch 2.3.1) On closing the database the menu contents are saved in PrintRes as a Data file called Q. You may reload and display this by dragging it onto the Query panel's writable icon or by placing the caret in the writable icon and typing Ctrl Q. (It isn't loaded automatically because you might not want any of the old queries.)

3.5.1 The construction of search formulae
To 2.3.3 (Searching by filter)
To 2.5.5 (Changing many records at once)
To 2.5.6 (Moving or deleting many records at once)
To 3.5.5 (Functions for use in search formulae)
To 3.6.1 (What is QBE?)
To 3.7 (Other features of the Query panel and Match window)
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 <TAG LIST><COMPARATOR><TARGET LIST>.

The angle brackets are there for clarity and are not used in entering the actual formula. There must be no spaces between the three parts. A tag list (if it contains more than a single tag) has the form tag1,tag2,tag3,.... where tag1 etc. are field tags (see 4.2.4) which uniquely identify the fields to be matched. 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 target1,target2,target3,.... 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 GP=T 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 GP=1,2,3 which would be interpreted as "The GP field must match one of 1,2 or 3". This may also be entered as GP=1 OR GP=2 OR GP=3 which is possibly easier to understand but also somewhat longer.

A further example is OX1,OX2,OX3=3 meaning "One of the first three oxidation state fields must have the value 3". This could also be entered as OX1=3 OR OX2=3 OR OX3=3. Yet another way is OX1-OX3=3, i.e. you may specify a range of fields by giving the first and last separated by a hyphen. This is a useful abbreviation but must be used with care. It is only useful where all the fields in the range are contiguous, e.g. if the field-numbers are 7,8,9,10. If the fields in which you are interested are numbered 7,9,10,15 any result obtained from a search of fields 7-15 is likely to be nonsense since all the intervening fields (8,11,12,13,14) will also be examined.

What if you expect the target string to be somewhere in a record don't know which fields to test? You can replace the tag, tag list or tag range with @ as in @=T which causes all the fields in the record to be examined. It's slower but it works!

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!): OX1=3 AND OX2=3 AND OX3=3

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 OX1,OX2,OX3==3.

Yet another way of forcing all the targets in a list to be matched is by using the reserved word AllOf. Applied to the example above gives the formula AllOf OX1,OX2,OX3=3, which you might find more understandable than the one with the double comparator.

= is equal or identical to
<> is not equal or identical to
~ an alternative to <>
< is less than
> is greater than
<= is less than or equal to
>= is greater than or equal to
{ contains
}{ does not contain

The full list of available comparators is shown in the table at right. { and }{ 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. It is sometimes useful to require 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 ADDR{{17,Avenue (note the doubled comparator) would find it by listing all records where ADDR contained both 17 and Avenue, whereas ADDR{17,Avenue 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. The aforementioned AllOf can be used on the target side of the formula as well as the field-tag side so you could achieve the same result with ADDR{AllOf 17,Avenue.

You may invert the logic of a search criterion by putting NOT in front of it. To include all non-transition elements you could use NOT (GP=T). Note the space after NOT, the need for brackets, and that the syntax isn't "GP NOT=T". You could equally well use either GP<>T or GP~T and may find either of these more understandable.

Be careful when using the negative comparators <> ("not equal to") and }{ ("does not contain") in conjunction with tag or target lists as opposed to a single tag or target. You might, for instance, interpret: GP<>1,2,3 to mean "field GP doesn't contain 1 or 2 or 3", i.e. the content isn't any of those targets. This would be sensible but inconsistent with the way in which GP=1,2,3 is interpreted; if the comparator isn't doubled the use of a tag or target list is always a short equivalent of multiple search elements linked by OR. In other words the example quoted would be interpreted as GP<>1 OR GP<>2 OR GP<>3 when what you probably mean is GP<>1 AND GP<>2 AND GP<>3. To secure this result with a target list you can double the comparator as previously described: GP<><>1,2,3, but it might be safer and less confusing to use another reserved word: NoneOf. The formula then becomes GP=NoneOf 1,2,3.

Note that AllOf and NoneOf contain no space (but must be followed by a space) and the use of upper and lowercase must be exactly as shown. Confusion with field-tags or target strings is then most unlikely. To complete the group there's a third reserved word AnyOf as in AnyOf OX1,OX2,OX3=3 or GP=AnyOf 1,2,3. It adds clarity to the formulae but does nothing else since the results would be exactly the same if the word was omitted.

To make multi-criterion searches either place tags and targets in comma-separated lists as described above or string search elements together with the connectives AND and OR. Use AND (or the ampersand, &) when a field must meet all of a set of criteria. e.g. GP=T & Z>50 & NAME{IUM 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. GP=L OR GP=A 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. GP=1 OR GP=2 AND Z<50 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 (GP=1 OR GP=2) AND Z<50. You could also write this as GP=1,2 & Z<50 without any need for brackets at all.top of page

3.5.2 Numeric and other special fields in search formulae
To 3.2.1 (Columns format)
To 3.5.4 (Querying Scrollable lists)
To 3.6.2 (QBE vs. QSF)
To 3.7 Other features of the Query panel and Match window
To 6.3.1 Calculations using non-numeric fields
Numeric
Calculated
Record number
Sequence number
Day in week
Day of month
Month number
Year
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 those in the table at right Only the first of these is an editable field type but all may be included in search formulae. Suppose we have a Numeric field whose tag is NUM. If you entered the formula NUM=5 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, however, 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 include records for which NUM<8. You may be surprised to find records in which NUM contains values such as 55, 20, or 13 appearing, 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, as will any others not listed above. Fields of types listed in the table 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.top of page

3.5.3 Using "wild-cards" in search formulae
To 14.1.3 (Wild-cards)
The use of characters * and # as "wild-cards" was described in 2.5.5 in connection with search-and-replace operations. They may 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 NAME=*ON 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 NAME=*ON* 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. Note that this works differently from the same syntax in search-and-replace, where *ON* would find the string ON in any position, including the start or end of the field. To achieve this in a report you would use NAME{ON.

Note also that NAME=*TIN* finds PROTOACTINIUM, PLATINUM etc, but not TIN itself. NAME=S*IUM finds all names which begin with S and end with IUM, e.g. SAMARIUM, SCANDIUM, and SODIUM.

The effect of using single wild-card characters as in NAME=S####IUM 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 you could enter NAME=#####. top of page

3.5.3.1 Querying text files
It was explained above that External Text and Text block fields can be included in reports, and so can Referenced fields where the referenced object is a plain text file which has been selected whilst holding down ALT. The text in these fields is also queriable. Unless there are only a few words of text the "=" comparator is only useful in conjunction with wild cards. Use either that method or the "{" comparator, e.g. if the field tag is TEXT and you want to find all the linked texts which contain the string "RISC OS" you could use either TEXT=*RISC OS* or TEXT{RISC OS. To make it easy for you to locate the target string in a large chunk of text lines containing it will be highlighted in red in the report.

3.5.4 Querying scrollable lists
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 LIST is the tag of a Scrollable list the formula LIST=<target> 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 LIST#2=<target> which would search cells in the second column only.

It should be noted that individual columns of a Scrollable list may be defined as Numeric type and the precautions mentioned in 3.5.2 apply.top of page

3.5.5 Functions for use in search formulae
To 6.1.6 (Using Composite fields)
Most database queries can be accomplished by the methods described in 3.5.1 but there are times when the criteria of the search need to be specified more precisely. For instance, using the Friends database, enter the formula ADD1{Road. This will produce three records (Bloggs, Croft and Falcon). In the first of these "Road" is the third word in the field (the house number counts as a word in this context) but in the other two it is the fourth. Suppose you wanted only those records in which "Road" was the fourth word? You can narrow the search in this way with a special function called word()

3.5.5.1 word()
To accomplish the search described above enter word(ADD1,4)=Road. Only two records (Croft and Falcon) now appear. If you enter word(ADD1,3)=Road the report shows Bloggs's record only. The syntax therefore is word(<tag>,<word number>) followed by any comparator and target string or target list. You can specify the last word of a field by replacing the word number with "F" (for "Final") which is useful if you don't know how many words the field may contain. Thus word(ADD1,F)=Road gets all three of the records where the last word is "Road".top of page

3.5.5.2 chars()
chars(NAME,L,3)=CAL
chars(NAME,R,3)=RON
chars(NAME,2,3)=ERB
The chars() function permits even more precise targetting; it enables you to test for specific characters in a specific location in a field. Using Elements try out each of the examples on the right. The first one tests the Leftmost 3 characters of the name (producing CALCIUM and CALIFORNIUM), the second tests the Rightmost 3 characters (producing BORON and IRON). The third tests the group of 3 characters beginning with the second from the left, i.e. characters 2, 3 and 4. Only TERBIUM appears. Note that this gives a result different from that obtained with NAME{ERB which lists 3 elements. The syntax of chars() is therefore chars(<tag>,<position>,<number of characters>) where the position is specified by L or R (for the beginning or end) or by the number of the first character to test.

The chars() and word() functions may be combined to test characters in a specified word. If you think of it in that way it is easy to remember that chars() is opened first and the word() statement is nested inside it. The following example, using Friends is of no use at all but illustrates the point chars(word(ADD3,2),L,3)=Far will list only the record for Mary Jones. (Watch those brackets!) The 3rd address field (ADD3) reads "Little Farham" so we have targeted the leftmost 3 characters of word 2. Note that the position in chars() which is normally occupied by a field tag contains a complete word() statement.

Both word() and chars() may also be used on the right-hand, i.e. the target side of a search formula. A trivial example using Elements is chars(NAME,L,1)=chars(NAME,R,1) which compares the first character of the name with the last and lists MAGNESIUM, MENDELEVIUM, MOLYBDENUM, NEON and NITROGEN. An important feature here is that a field, rather then a literal string, is being referenced as the target. In this example it's the same field as the one on the left but it doesn't have to be. You may have occasion to compare the contents of two fields in a record without the use of word() or chars() and this can be done with the field() function.top of page

3.5.5.3 field()
To 3.7 (Other features of the Query panel and Match window)
We need a little artifice to provide a simple example of this — your own databases may illustrate more practical applications. Call up any record in Elements and enter a word (any old word will do) in the first of the Notes fields (N1). Enter the same word in the second Notes field (N2). Now (with ADJUST) select NAME, N1 and N2 and apply the search formula N1=field(N2). It didn't do as expected did it? That's because most of the records have nothing in either N1 or N2 so of course they're identical. Modify the formula to leave out records where N1 is blank N1=field(N2) AND N1<>"". This will give just the record where you entered the same word into N1 and N2.

Only use field() when you are comparing whole fields; if word() or chars() is used as in the final example in 3.5.5.2 you shouldn't use field().top of page

3.5.5.4 calc()
To 12.8 (Requesting user input from script files)
The calc() function makes it possible to use a simple calculation as the target in a search formula. Using Elements again, which elements, if any, have a Relative atomic mass (M) which is twice the Atomic number (Z)? If you enter the formula as M=2*Z Powerbase replies with "Target ("2*Z") is not a number". Z is, however, a Numeric field so the calculation 2*Z yields a numeric result. We just need to tell Powerbase that we want the calculation done before making the comparison with M. M=calc(2*Z) does the trick, producing the single matching record; that of OXYGEN.

You can also use calc() on the left-hand side, just as you can with word() and chars(). The formula calc(2*Z)=field(M) will, again, list OXYGEN only. Note the use of field() here where the tag M stands alone instead of being inside a function.top of page

3.5.6 Saving search formulae for re-use
To 3.10.3 (Saving print options files)
Choosing Save query from the Report 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 with an appended number, e.g. Query_01. 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. A Query file also includes information regarding the subfiles selected.

If a Query file has been loaded by double-clicking in order to modify it and re-save under the same name, you can avoid having to re-type the filename by simply choosing Save query from the menu without opening the Save box.top of page

3.6 Query by example


To 3.5 (Specifying which records to include)
To 14.1.4 (Option buttons: query by example

After that lengthy description of querying the database using a search formula 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.top of page

3.6.1 What is QBE?
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 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 Report button of the Match window or else type the Print key (but see footnote to Appendix B).

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 of a search formula (see 3.5.1). There are, however, other comparators besides "=" which may be used in search formulae. You may use any of these in a QBE query by placing them at the start of the string, e.g. {Avenue 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 list 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 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) 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.)top of page

3.6.2 QBE vs QSF
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: If your requirements involve only fairly simple searches then QBE may be just what you've been looking for, but if complex multiple searches are often needed then QSF is the one to go for. It is, of course, a simple matter to switch between the two.

As supplied Powerbase uses QSF as the default query method and the Query by example button will be deselected when the Preferences 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 after Query. Clicking Report on the tool-pane will then produce the blank record without displaying the Match window at all. How then, without the Match window and its own Report button to click after you have entered the target strings, do you tell Powerbase to proceed? Simply type the Print key on the keyboard.top of page

3.7 Other features of the Query panel and Match window

To 2.3.3 (Searching by filter)
To 6.5.2 (Extra "fieldless" calculations)
match/png

The Case button was discussed in 3.5.1 and the Old button in 3.5. 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 3.13 and in detail in 7.3.

The default action button at bottom right reflects whichever of the four radio buttons on the left is selected. When the Match window is opened it is always Report 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 creating a report, select Count. The number of matching records appears just below the red border. Mark and Clear are explained in 3.8.2.

The Fields selected menu button (duplicated by Ctrl-F) 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. The icon directly below it 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 Report options window, in fact you may 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.top of page

3.7.1 Using the Help window to enter search formuale
help/png Help opens the Help window which offers another way of building search formulae which may appeal to beginners or to anyone who only uses a database occasionally and doesn't remember the tags or syntax very well. Select the target field by cycling through the tags with the bump icons or by choosing from the pop-up menu. If the chosen field is a Scrollable list the menu will have a submenu from which the column can be chosen. This will then appear in the icon between the menu button and the tag display.

Choose the comparator in the same way. Type the target string into the writable icon and click Add to formula. The search element will appear at the caret. The following modifications may be made to the procedure before clicking Add to formula:

Clicking AND and OR enters these elements directly into the search formula. Other search elements may be added in the same way as the first.top of page

3.7.2 An easy method of entering field tags
Even if you don't use the Help window as described above you can still save yourself the trouble of memorising field tags by using the following method to enter them into the Query panel.

Make sure the caret is in the Query panel's writable icon, hold down Ctrl and click with SELECT over the required field. The tag of the field will be entered in the search formula at the caret.

3.7.3 Reports containing records from more than one subfile

query2/png All the examples described so far report only on the current subfile. It is, however, possible to search multiple subfiles. Click Select subfiles and the query panel on the Match window changes as shown here. Any or all of the enabled subfiles may be selected or deselected (disabled subfiles are greyed out) and clicking Query returns the panel to its normal display. Note that is not possible to leave all subfiles unselected; attempting to do so will leave the the currently-in-use subfile selected. When more than one subfile is selected the label on the Select subfiles button is coloured red — a useful reminder when the subfile selector is closed.

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).

Multi-subfile reports are often more useful if records from each subfile are separately totalled and preceded by a header. To achieve this enter "S" in the Page length icon. Each page will be headed with the number and name of the relevant subfile. Subsequent sorting on a field, although permissible, would not be useful since it would order the entire report according to the sort field without regard to subfile divisions. If the report is destined for the printer each subfile will begin on a new page, even if the previous page was only partly filled.top of page

3.7.4 Including record number, key and subfile number
The option buttons Rec, Key and File allow you to include respectively the record number, 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 appears between the data fields. The extra items appear in blue on the Fields selected menu and are saved as part of a Selection file.top of page

3.8 Marking records for inclusion or exclusion

To 14.1.4 (Option buttons: save marked status)

3.8.1 Arbitrary selection of records
To 3.9 (Reporting only the displayed record)
To 2.7.1 (Highlighting lines in a report)
There are times when you want a report featuring 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 are to be included?

mark/png

Attached to the bottom of the record window is the Mark pane (Note 2). If you tick the check-box, Mark record, the displayed record will be included in the report. Using the Search button or the browse controls you can call up each record you want and tick the box. You can also mark records from a windowed report: see 2.7.1.
Check boxSearch formulaWhich records are reported?
TickNoMarked records only
TickYes ("Search within marked set" deselected)Records matching formula plus marked records, whether they match or not
TickYes ("Search within marked set" selected)Records matching formula only if they are also marked
CrossNoAll records except those marked
CrossYesRecords matching formula except those marked, even if they match the formula
You then simply select the required fields and proceed in the usual way without entering a search formula. A pop-up menu allows you to invert the effect of this feature so that reporting without a search formula gives all records in the subfile except the marked ones. 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.

But what happens if you enter a search formula as well as marking records? If the records are marked for exclusion (i.e. with a cross) records which match the search formula will be reported unless they are so marked. When records are marked for inclusion the result depends on the status of the Search within marked set button on the Match window. If this button is not selected (the default situation) the report will contain all the marked records plus all records which match the search formula. However, if the button is selected the search takes place, as indicated, within the set of marked records and only those records which match the search formula and are also marked will be included. The table on the right summarises the results obtained in all possible cases. When using Search within marked set is inapplicable the button is deselected and shaded.(See also Note 3)

You might want to check that you have marked the right records before creating a report. This can be done by selecting the Find marks check-box. A report made while this icon is selected lists only the marked records, regardless of whether the marking is for inclusion or exclusion, and any search formula is ignored.

The Clear all button 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, convert Referenced pathnames, filter, global change, export CSV file, export subset, as well as report creation.top of page

3.8.2 Groups of records
To 3.7 (Other features of the Query panel and Match window)
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 list your carefully-tailored selection of marked records without using a search formula at all.top of page

 
 

3.9 Reporting only the displayed record

Holding down Shift whilst clicking with SELECT on the Report button of the Match window creates a report containing just the displayed record; so does typing Shift-Print on the keyboard (but see footnote to Appendix B). Yet another method is to mark the displayed record as described in 3.8.1. A report generated without entering a search formula will then include only the marked record. The highlighted fields of the displayed record appear in the currently-selected report format as determined by the setting in the Report 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, use the primary key field(s) as a selection.top of page

3.10 The Report options window

To 3.1 (Output destination)

To display this window you can choose Options from the Report submenu, type Ctrl Print (but see footnote to Appendix B), or click SELECT on the icon beneath the Fields selected menu on the Match window. Features such as Destination (see 3.1), Format (see 3.2), Colours and the Sort on facility (see 3.2.1) have already been dealt with extensively. The rest are covered here.top of page

3.10.1 Scrollable lists
To 3.4 (Specifying which fields to include)
scropts/png In a report these may be formatted 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 <comma><sp> and <semicolon><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 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 list on a separate line so that the data aligns As 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 included to those rows in which the contents of a cell match a target in the search formula.top of page

3.10.2 The rest of the report options
To 3.2.1 (Columns format)
To 3.2.2 (Field-per-line format)
To 3.2.4 (Label format)
To 8.3 (Exporting data as a CSV file)
To 12.3 (Setting report options via script commands)
The remaining options require only brief explanations. Default settings appear in brackets after the name of the feature.

Field headings (tags) appear on each page of a report in Columns and Table formats, and at the beginnings of lines in Field-per-line format, 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.

Page numbers (OFF) allows the page number to appear as the first line of each page of a report

Date stamp (ON) makes the date and time when the report was created appear immediately below the line number, if present. It appears on the first page only.

Header (ON) causes the following lines to be printed below the date-stamp:

Like the date-stamp this information appears on the first page only.

Footer (ON). Reports in Columns and Table format normally end with a footer which gives the total number of records in the report. If the output includes Numeric or Check-box fields and column calculations have been selected (see 6.5) the results of these too will be part of the footer.

Cut white space (ON). In Columns and Table format the width of columns is determined by the maximum defined length of the fields included in the 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

Title (blank) allows you to specify a descriptive title for a report. It appears below the header and will be printed on every page, but you may limit it to page 1 only by selecting the button to the right. The colour of the title may be selected independently of the rest of the list header.

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 may want to alter this if you drag text-files to the printer. In Field-per-line 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 per record exceeds the length of the page!)

Text width (A) specifies the line length used in Field-per-line format. A means "Auto" and lets the program calculate the value. You may enter your own value (e.g. 70) to override this. Values are in characters.

Spacer (1) specifies how fields 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 then appears before starting the next field. Four interpretations of the contents of this icon are possible:

3.10.3 Saving print options files
All the settings in the Report 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.top of page

3.11 The Printer setup window

When the report destination is set to Printer clicking Lots more on the Report 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.top of page

3.11.1 Line length and point size of fonts
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 5pt) 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 5pt or greater and you are using RISC OS 3.50 or later you will be offered four choices:

Under RISC OS 3.1 you only have the choice of either cancelling the page or proceeding with the original point size.

The behaviour just described is modified somewhat if you have selected the pre-sorting option (see 3.2.1) or if you are printing from a report in a window (see 3.1.1). In both these cases the entire report is buffered in memory instead of processed page-by-page. A choice of smaller text to cope with over-long lines therefore applies to the entire report, not just to the current page. The same is true if you decide to proceed with the configured size.

top of page

3.11.2 Print margins

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, if selected, all four of the printer driver's minimum margins will be used.top of page

3.11.3 Printing speed
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 may 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 very quickly and is highly recommended.top of page

 
 

3.12 Field analysis reports

The Field submenu has an Analyse option which allows you to view a breakdown of the field contents under certain special circumstances:
  1. 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.
  2. If the field is a defined Date type (e.g. 19-10-42, 19-10-1942 or Mon 19 Oct 1942) the menu entry reads Analyse months and will give a breakdown by month. 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 similar fields in the Calculated and Stamp class.

Like all such windowed reports field analyses can be saved as text files or printed out (see 3.1.1).

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. These reports are always in a window (from which they may, of course, be saved); the Destination buttons in the Report options window have no effect.top of page

3.13 Subsidiary indexes and speed of reporting

To 3.7 (Other features of the Query panel and Match window)
To Ch 7 (Using extra indexes)
To 7.3 (Indexes and speed of reporting)
In many cases Powerbase can make use of subsidiary indexes to make reports complete much more quickly. This feature requires a substantial description, which will be found in section 7.3. You may, 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 GP=T

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 Report 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 may not sound like a difference worth bothering about, but remember that Elements is a very small database; you are extracting 31 elements from 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 may be achieved in some instances.top of page



Note 1
Ancillary reports which may be displayed in a window and subsequently saved to a text file or printed out are of eight types: (back)

Note 2
If the Mark pane isn't visible Ctrl M will display it. It is possible to configure Powerbase not to display the Mark pane (see 14.8) but even then it can be toggled ON and OFF with Ctrl M (back).

Note 3
If you use script commands you can make use of the "Search within marked set" feature by appending M to the commands !QUERY, !CSV and !FILTER. (back).



top of page Home   Index   list of chapters    previous chapter   next chapter